Re: [GENERAL] Fast way to delete big table?

2016-05-17 Thread hmzha2 
Hi Jeff and all,

Thank you for your help. Just update my progress, I finally  made it working
in production by using the second solution. The extra thing I have done was
"restart postgres" and the script started to delete 2558 records per second.
I guess it must has locked records or something blocked the delete, and
"restart" removed the obstacle.

Thanks again for the help.

Regards,
Haiming



--
View this message in context: 
http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5904081.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] Thoughts on "Love Your Database"

2016-05-17 Thread Achilleas Mantzios 

On 17/05/2016 20:38, Guyren Howe wrote:

On May 17, 2016, at 2:22 , Achilleas Mantzios  
wrote:

Sorry if I missed something but what's wrong with pgadmin3 ?

Apart from it's awful, clunky, bug-ridden and crash prone, nothing.


There is a solution for that : either find the bugs and submit patches or pay 
the developers to fix the bugs or make you a custom version
or go buy some other tool, or write your own or live with psql (like most 
people do).
In the occasions (once/twice a week) that I needed pgadmin during my last 16 
years with postgresql, it delivered just fine.
Some guys here use some better tools to access pgsql but they all cost money. I 
have paid for RazorSQL for accessing our MS SQL server,
then felt lucky I was accessing pgsql via psql (or even pgqdmin3) all those 
years.

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



--
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] Increased I/O / Writes

2016-05-17 Thread John R Pierce 

On 5/17/2016 3:56 PM, Lucas Possamai wrote:


*FATAL*: sorry, too many clients already



I believe that error means you've exceeded max_connections. query 
pg_stat_activity and see how many connections are in the various 
'states', like..



select state, count(*) from pg_stat_activity group by state;



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Increased I/O / Writes

2016-05-17 Thread Lucas Possamai 
Following
https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/

and http://pgtune.leopard.in.ua/ and
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I changed the shared_buffer from 51GB to 35GB.

Now, I'm getting spikes every 15 minutes.

*FATAL*:  sorry, too many clients already

The change have been made 3 hours ago.

- we rebooted the server as well

Might be the cache warming up again?

Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded

2016-05-17 Thread Steve Kehlet 
On Tue, May 17, 2016 at 10:40 AM Alvaro Herrera 
wrote:

> In 9.4, not really. In 9.5 there's a function mxid_age() that gives you
> the age of a multixact, so you'd grab the oldest from
> pg_database.datminmxid and compute the age of that one.  Going from the
> oldest multi to the oldest offset cannot be done without an additional
> function, however.  It's much easier to keep track of the oldest file in
> $PGDATA/pg_multixact/members/; what you really need to care about is the
> size of the "hole" between the newest and the oldest files there.  Once
> newest starts to stomp on oldest, you're screwed.


Thank you Alvaro. We're beginning to plan our rollout of 9.5.

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

2016-05-17 Thread David G. Johnston 
On Tue, May 17, 2016 at 12:04 AM, Guyren Howe  wrote:

> On May 16, 2016, at 20:48 , David G. Johnston 
> wrote:
>
>
> On Monday, May 16, 2016, Guyren Howe  wrote:
>
>> I have this SELECT clause as part of a larger query:
>>
>> FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position 
>> ASC) AS current_drs_id
>>
>> Seems reasonable to me: group and sort the fields in this table and give
>> me the first value.
>>
>> But I get "column "drs.id" must appear in the GROUP BY clause or be used
>> in an aggregate function".
>>
>> Huh?
>>
>
> The larger query would help…
>
>
> SELECT
>   o.id,
>   os.status AS status,
>   o.status_updated_at,
>   o.should_hold_at_airlines,
>   (SELECT drs2.id FROM delivery_route_segments drs2 WHERE drs2.order_id = 
> o.id AND NOT drs2.completed ORDER BY drs2.position LIMIT 1) AS current_drs_id,
>
>
> FROM
>   orders o JOIN
>   order_statuses os ON (o.status = os.id) JOIN
>   delivery_route_segments drs ON (drs.order_id = o.id) JOIN
>   pick_up_addresses pua ON (pua.order_id = o.id)
> GROUP BY
>   o.id, os.status
>
> I would prefer to do the subquery as a window function, both because that
> is cleaner to read and also because I believe it is likely to be more
> efficient.
>
>
​[reading a bit more closely now...]​

​IMO opinion you are abusing GROUP BY here - since you don't seem to care
about aggregation but rather are compensating for the presence of
additional joins and their propensity of introducing additional undesirable
rows.

Judicious use of subqueries, semi-joins (i.e., EXISTS clause), and maybe -
if all else fails - DISTINCT, would be better than throwing window
functions into the mix.

David J.

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

2016-05-17 Thread David G. Johnston 
On Tue, May 17, 2016 at 2:03 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, May 17, 2016 at 1:36 PM, Guyren Howe  wrote:
>
>> I am trying to use a window function, but it's not working. The subquery
>> is effectively aggregating.
>>
>>
> ​I would advise avoiding using select-list scalar (and particularly
> correlated) subqueries when the query level in question also contains a
> group by.  Do the group by in a subquery and then in the parent query pull
> in whatever subselects you want to execute.
>
> You need to make it easier for people to help.  The first post you sent
> was just a window function.  I asked for​
>
> ​detail and you sent a query without one.  Given that discrepancy, added
> to the fact the query is question is not self-contained, I am less than
> inclined to try and figure out what you are trying to do.
>
> Given me a query that has a window function and looks like, if said window
> function was removed, it would successfully run, and I'll made an educated
> guess.
>

​For my own part I need to remember that functional dependencies in the
group by would allow one to forgo naming a column there that is present in
the select-list...so maybe what you posted would work if run - but it still
lacks your attempt at implementing a window function and I don't have the
option to play with it since it is not self-contained.

David J.
​

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

2016-05-17 Thread David G. Johnston 
On Tue, May 17, 2016 at 1:36 PM, Guyren Howe  wrote:

> I am trying to use a window function, but it's not working. The subquery
> is effectively aggregating.
>
>
​I would advise avoiding using select-list scalar (and particularly
correlated) subqueries when the query level in question also contains a
group by.  Do the group by in a subquery and then in the parent query pull
in whatever subselects you want to execute.

You need to make it easier for people to help.  The first post you sent was
just a window function.  I asked for​

​detail and you sent a query without one.  Given that discrepancy, added to
the fact the query is question is not self-contained, I am less than
inclined to try and figure out what you are trying to do.

Given me a query that has a window function and looks like, if said window
function was removed, it would successfully run, and I'll made an educated
guess.

David J.
​

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-17 Thread Raymond O'Donnell 
On 17/05/2016 18:38, Guyren Howe wrote:
> On May 17, 2016, at 2:22 , Achilleas Mantzios  
> wrote:
>>
>> Sorry if I missed something but what's wrong with pgadmin3 ?
> 
> Apart from it's awful, clunky, bug-ridden and crash prone, nothing.

In fairness to pgAdmin 3:

- It's not a development platform, such as MS Access tries to be; it's
an admin tool, pure and simple.

- pgAdmin 4 is in heavy development, and not too far from a beta;
pgAdmin 3 has only been receiving bug fixes for quite some time now.

- A lot of the problems in pgAdmin 3 are due to upstream bugs in
wxWidgets, over which the pgAdmin team has no control (hence pgAdmin 4).

Having said all that, I've rarely had any trouble with pgAdmin 3 on
Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe
one every six months).

Ray.
-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Update or Delete causes canceling of long running slave queries

2016-05-17 Thread Viswanath 
Hi,
What if we have a chain of slaves (master->slave->sec slave)? And if queries
would run on each of them? or a config like master ->slave1
 ->slave2 

Is it ok to enable feedback on both slaves ?



--
View this message in context: 
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903914.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] preventing ERROR: multixact "members" limit exceeded

2016-05-17 Thread Alvaro Herrera 
Steve Kehlet wrote:
> On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera 
> wrote:
> 
> > Not really.  Your best bet is to reduce the
> > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to
> > get rid of multixacts sooner (and/or reduce
> > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming
> > takes place earlier).
> 
> Thank you very much. I will adjust those settings. Is there a way,
> something similar to keeping an eye on `age(relfrozenxid)`, that I can
> watch this and keep an eye on it before it becomes a problem?

In 9.4, not really. In 9.5 there's a function mxid_age() that gives you
the age of a multixact, so you'd grab the oldest from
pg_database.datminmxid and compute the age of that one.  Going from the
oldest multi to the oldest offset cannot be done without an additional
function, however.  It's much easier to keep track of the oldest file in
$PGDATA/pg_multixact/members/; what you really need to care about is the
size of the "hole" between the newest and the oldest files there.  Once
newest starts to stomp on oldest, you're screwed.

-- 
Á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] Thoughts on "Love Your Database"

2016-05-17 Thread Guyren Howe 
On May 17, 2016, at 2:22 , Achilleas Mantzios  
wrote:
> 
> Sorry if I missed something but what's wrong with pgadmin3 ?

Apart from it's awful, clunky, bug-ridden and crash prone, nothing.



-- 
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] FIRST_VALUE argument must appear in group by?

2016-05-17 Thread Guyren Howe 
I am trying to use a window function, but it's not working. The subquery is 
effectively aggregating.

> On May 17, 2016, at 6:18 , David G. Johnston  
> wrote:
> 
> On Tue, May 17, 2016 at 12:04 AM, Guyren Howe  > wrote:
> On May 16, 2016, at 20:48 , David G. Johnston  > wrote:
>> 
>> On Monday, May 16, 2016, Guyren Howe > > wrote:
>> I have this SELECT clause as part of a larger query:
>> FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position 
>> ASC) AS current_drs_id
>> Seems reasonable to me: group and sort the fields in this table and give me 
>> the first value.
>> 
>> But I get "column "drs.id " must appear in the GROUP BY 
>> clause or be used in an aggregate function".
>> 
>> Huh?
>> 
>> The larger query would help…
> 
> SELECT
>   o.id,
>   os.status AS status,
>   o.status_updated_at,
>   o.should_hold_at_airlines,
>   (SELECT drs2.id FROM delivery_route_segments drs2 WHERE drs2.order_id = 
> o.id AND NOT drs2.completed ORDER BY drs2.position LIMIT 1) AS current_drs_id,
> 
> 
> FROM
>   orders o JOIN
>   order_statuses os ON (o.status = os.id) JOIN
>   delivery_route_segments drs ON (drs.order_id = o.id) JOIN
>   pick_up_addresses pua ON (pua.order_id = o.id)
> GROUP BY
>   o.id, os.status
> I would prefer to do the subquery as a window function, both because that is 
> cleaner to read and also because I believe it is likely to be more efficient.
> 
> 
> ​This query is non-functional.  It has a GROUP BY without any aggregate 
> functions and not all of the selected columns are in the group by.
> 
> David J.
>

Re: [GENERAL] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Karsten Hilbert 
> On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov  wrote:
> > I had a bit of fun with this SQL version and came up with this query:
> >
> > WITH src(s) AS (
> >   VALUES
> > ('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
> > ), str AS (
> >   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+
> > o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line
> > FROM src, generate_series(1, 182, 2) p
> > )
> > SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;

I would nominate this to be called

 select pg_logo_obfuscated();

Karsten


-- 
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] PG wire protocol question

2016-05-17 Thread George Neuner 
On Sat, 14 May 2016 21:58:48 +0200, Boszormenyi Zoltan 
wrote:

>Hi,
>
>it was a long time I have read this list or written to it.
>
>Now, I have a question. This blog post was written about 3 years ago:
>https://aphyr.com/posts/282-jepsen-postgres
>
>Basically, it talks about the client AND the server as a system
>and if the network is cut between sending COMMIT and
>receiving the answer for it, the client has no way to know
>whether the transaction was actually committed.
>
>The client connection may just timeout and a reconnect would
>give it a new connection but it cannot pick up its old connection
>where it left. So it cannot really know whether the old transaction
>was committed or not, possibly without doing expensive queries first.
>
>Has anything changed on that front?
>
>There is a 10.0 debate on -hackers. If this problem posed by
>the above article is not fixed yet and needs a new wire protocol
>to get it fixed, 10.0 would be justified.

It isn't going to be fixed ... it is a basic *unsolvable* problem in
communication theory that affects coordination in any distributed
system.  For a simple explanation, see

https://en.wikipedia.org/wiki/Two_Generals'_Problem


>Thanks in advance,
>Zoltán Böszörményi

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] edit wiki

2016-05-17 Thread Willy-Bas Loos 
OK, thx

On Tue, May 17, 2016 at 6:19 PM, Daniel Gustafsson  wrote:

>
> The wiki editor permission requests should go to the pgsql-www@
> mailinglist,
> remember to include your community username in the email.
>


-- 
Willy-Bas Loos

Re: [GENERAL] edit wiki

2016-05-17 Thread Daniel Gustafsson 
> On 17 May 2016, at 18:10, Willy-Bas Loos  wrote:
> 
> Hi,
> 
> I added some code to the script on 
> https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
> and i would like to share.
> The code provides in backing up the globals by means of pg_dumpall -g
> 
> The wiki says i need to ask for editing privileges.
> 
> BTW nice script. And it could yet be expanded with support for multiple 
> clusters. There is some possible example code in Debian's postgresql-common 
> pg_lsclusters, which is written in perl.

The wiki editor permission requests should go to the pgsql-www@ mailinglist,
remember to include your community username in the email.

cheers ./daniel

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

[GENERAL] edit wiki

2016-05-17 Thread Willy-Bas Loos 
Hi,

I added some code to the script on
https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
and i would like to share.
The code provides in backing up the globals by means of pg_dumpall -g

The wiki says i need to ask for editing privileges.

BTW nice script. And it could yet be expanded with support for multiple
clusters. There is some possible example code in Debian's postgresql-common
pg_lsclusters, which is written in perl.

Cheers,
-- 
Willy-Bas Loos

Re: [GENERAL] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Michael Paquier 
On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov  wrote:
> I had a bit of fun with this SQL version and came up with this query:
>
> WITH src(s) AS (
>   VALUES
> ('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
> ), str AS (
>   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+
> o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line
> FROM src, generate_series(1, 182, 2) p
> )
> SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;

You just made my day.
-- 
Michael


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

2016-05-17 Thread Edson Richter 

Em 17/05/2016 11:07, Merlin Moncure escreveu:

On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback
 wrote:

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.

FYI tuple header is 23 bytes, not 28 bytes
(http://www.postgresql.org/docs/9.5/static/storage-page-layout.html).
Personally I think column stores are a bit overrated.  They are faster
at certain things (in some cases much faster) but tend to put pretty
onerous requirements on application design so that they are very much
a special case vehicle.

merlin



+1 (to not change current defaults).

I would tend to avoid columnar store "as default" because this would 
badly affect hundred of thousands of applications around the world. 
Columnar store should have its own niche, but certainly doesn't fit my 
needs.


Would you give a "option to change the store" is another history.

As I work with objects at programming side, and ORM works just so well, 
it is a really waste of time (and other resources) to change systems 
that are working well in the past 10 or more years.


Just my 2c,

Edson Richter





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

2016-05-17 Thread Merlin Moncure 
On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback
 wrote:
>>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.

FYI tuple header is 23 bytes, not 28 bytes
(http://www.postgresql.org/docs/9.5/static/storage-page-layout.html).
Personally I think column stores are a bit overrated.  They are faster
at certain things (in some cases much faster) but tend to put pretty
onerous requirements on application design so that they are very much
a special case vehicle.

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] PG wire protocol question

2016-05-17 Thread Manuel Gómez 
On Tue, May 17, 2016 at 9:29 AM, Albe Laurenz  wrote:
> That blog post seems ill-informed - that has nothing to do with
> two-phase commit.
>
> The problem - that the server may commit a transaction, but the client
> never receives the server's response - is independent of whether
> two-phase commit is used or not.

The author addresses this in a comment within the linked page:

«The database may be consistent, but the system isn’t. A concurrent
request to the db will get the answer “yes, the transaction has
committed”, but the same request of the remote client gets “no, the
transaction has not yet committed.” The system may eventuallybecome
consistent, if the partition is healed and the acknowledgement reaches
the client. But it isn’t consistent until that point.

And the client can’t just wait indefinitely for acknowledgement–the
commit request may not have reached the server, in which case the
client would deadlock forever. Not to mention practical concerns (a
customer and clerk aren’t going to wait very long for a credit card
transaction to complete). Introducing timeouts then causes the
temporary inconsistency to become permanent.»


-- 
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] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Jan de Visser 
On Tuesday, May 17, 2016 9:41:51 AM EDT Bryan Nuse wrote:
> On 05/17/2016 08:25 AM, Victor Yegorov wrote:
> > I had a bit of fun with this SQL version and came up with this query:
> > 
> > WITH src(s) AS (
> > 
> >   VALUES
> > 
> > ('729472967293732174412176b12173b17111752171927491b1744171b174112171814172
> > 11718141734172b191721191724173b1714171912175b17221b1912174b1412178b1217151
> > 22a172a1b2317d91a172a17f71b1a1912177') ), str AS (
> > 
> >   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab',
> > 
> > '(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int),
> > '') line
> > 
> > FROM src, generate_series(1, 182, 2) p
> > 
> > )
> > SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;
> 
> I wonder if --under some fonts especially-- that elephant's eyes look a
> little... aggressive?
> Perhaps the following is too cute, however:
> 
>   __  ___
>/)/  \/   \
>   ( / ___\)
>\(/ p)  ( g)   )
> \_  (_  )   \ ) _/
>   \  /\_/\)/
>\/ 
> _|  |
> \|_/

Some people have WAY too much time on their hands.




-- 
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] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Bryan Nuse 


On 05/17/2016 08:25 AM, Victor Yegorov wrote:


I had a bit of fun with this SQL version and came up with this query:

WITH src(s) AS (
  VALUES 
('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')

), str AS (
  SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', 
'(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), 
'') line

FROM src, generate_series(1, 182, 2) p
)
SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;




I wonder if --under some fonts especially-- that elephant's eyes look a 
little... aggressive?

Perhaps the following is too cute, however:

     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ p)  ( g)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/

--
Postdoctoral Researcher
Georgia Cooperative Fish & Wildlife Research Unit
University of Georgia
Athens, GA 30606-2152

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

2016-05-17 Thread Charles Clavadetscher 
Hello again

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Dienstag, 17. Mai 2016 14:50
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final 
> function proposal
> 
> Hello all
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten
> > Hilbert
> > Sent: Dienstag, 17. Mai 2016 09:23
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> >
> > On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> >
> > > A question to the naming. I find pg_logo() also a good name, but is
> > > the prefix pg_* not reserved for system functions? Of course I could
> > > use the name I want, but was wondering if there is a policy or a
> > > best practice in this area.
> >
> > pg_logo would only be suitable if it got blessing from "higher up".
> 
> Well. This question will be addressed when the body of the function is 
> complete. Now I have a proposal which is in
> the attachment.

In my function there was a problem with casting of values to real instead of 
numeric to compute the number of spaces required on the
right side of a string to be centered. In some specific string lengths the 
rounding was not correct.
Sorry. This should now be ok.

In the attachment the corrected version.

Bye
Charles

--
-- Display the PostgreSQL logo as ASCII art with
-- an optional frame and optional text.
--
CREATE OR REPLACE FUNCTION pg_logo(p_frame BOOLEAN DEFAULT false,
   p_text TEXT[] DEFAULT NULL,
   p_position TEXT DEFAULT 'bottom',
   p_align TEXT DEFAULT 'center',
   p_valign TEXT DEFAULT 'center')
RETURNS SETOF TEXT
AS $$
DECLARE
  v_pic TEXT[] := ARRAY[
  '    __  ___  ',
  ' /)/  \/   \ ',
  '( / ___\)',
  ' \(/ o)  ( o)   )',
  '  \_  (_  )   \ ) _/ ',
  '\  /\_/\)/   ',
  ' \/',
  '  _|  |  ',
  '  \|_/   '];
  v_pic_width INTEGER := coalesce((SELECT max(length(x))
   FROM unnest(v_pic) x),0);
  v_pic_height INTEGER := array_length(v_pic,1);
  -- Get the longest text available or zero if none.
  v_max_text_width INTEGER := coalesce((SELECT max(length(x))
FROM unnest(p_text) x),0);
  v_text_height INTEGER := coalesce(array_length(p_text,1),0);
  -- Compute total width including a space if text is on the right.
  -- This value does not include the frame (if requested).
  v_tot_width INTEGER := CASE WHEN p_position = 'bottom' THEN
greatest(v_max_text_width,v_pic_width)
  ELSE v_pic_width+v_max_text_width+1
 END;
  v_pic_line TEXT;
  v_line_count INTEGER; -- Used for vertical alignment of text
BEGIN
  IF v_text_height > 8 THEN
  END IF;
  -- Check positioning and alignments. Fall back to default if
  -- values are not allowed.
  IF lower(coalesce(p_position,'')) NOT IN ('bottom','right') THEN
p_position := 'bottom';
p_position := lower(p_position);
  END IF;
  IF lower(coalesce(p_align,'')) NOT IN ('left','center','right') THEN
p_align := 'center';
p_align := lower(p_align);
  END IF;
  IF lower(coalesce(p_valign,'')) NOT IN ('top','center','bottom') THEN
p_valign := 'center';
p_valign := lower(p_position);
  END IF;
  -- Add top frame line.
  IF p_frame THEN
RETURN QUERY SELECT '+-'||repeat('-',v_tot_width)||'-+';
  END IF;
  -- Reset counter for vertical alignment of right positioned text.
  CASE WHEN p_valign = 'top' THEN v_line_count := -1; -- It looks better like 
this.
   WHEN p_valign = 'bottom' THEN v_line_count := v_text_height-v_pic_height;
   ELSE v_line_count := (v_text_height-v_pic_height)/2;
   IF v_line_count = 0 THEN v_line_count := -1; -- Correct for case when 
number
   END IF;  -- of text lines is 8
  END CASE;
  FOREACH v_pic_line IN ARRAY v_pic
  LOOP
CASE WHEN p_position = 'bottom' THEN
  CASE WHEN p_align = 'left' THEN
 RETURN QUERY SELECT CASE
   WHEN p_frame THEN '| '||
 v_pic_line||
 repeat(' 
',v_tot_width-length(v_pic_line))||
 ' |'
   ELSE v_pic_line||
repeat(' 
',v_tot_width-length(v_pic_line))

Re: [GENERAL] PG wire protocol question

2016-05-17 Thread Albe Laurenz 
Boszormenyi Zoltan wrote:
> it was a long time I have read this list or written to it.
> 
> Now, I have a question. This blog post was written about 3 years ago:
> https://aphyr.com/posts/282-jepsen-postgres
> 
> Basically, it talks about the client AND the server as a system
> and if the network is cut between sending COMMIT and
> receiving the answer for it, the client has no way to know
> whether the transaction was actually committed.
> 
> The client connection may just timeout and a reconnect would
> give it a new connection but it cannot pick up its old connection
> where it left. So it cannot really know whether the old transaction
> was committed or not, possibly without doing expensive queries first.
> 
> Has anything changed on that front?

That blog post seems ill-informed - that has nothing to do with
two-phase commit.

The problem - that the server may commit a transaction, but the client
never receives the server's response - is independent of whether
two-phase commit is used or not.

This is not a problem of PostgreSQL, it is a generic problem of communication.

What would be the alternative?
That the server has to wait for the client to receive the commit response?
But what if the client received the message and the server or the network
go down before the server learns of the fact?
You see that this would lead to an infinite regress.

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

Re: [GENERAL] FIRST_VALUE argument must appear in group by?

2016-05-17 Thread David G. Johnston 
On Tue, May 17, 2016 at 12:04 AM, Guyren Howe  wrote:

> On May 16, 2016, at 20:48 , David G. Johnston 
> wrote:
>
>
> On Monday, May 16, 2016, Guyren Howe  wrote:
>
>> I have this SELECT clause as part of a larger query:
>>
>> FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position 
>> ASC) AS current_drs_id
>>
>> Seems reasonable to me: group and sort the fields in this table and give
>> me the first value.
>>
>> But I get "column "drs.id" must appear in the GROUP BY clause or be used
>> in an aggregate function".
>>
>> Huh?
>>
>
> The larger query would help…
>
>
> SELECT
>   o.id,
>   os.status AS status,
>   o.status_updated_at,
>   o.should_hold_at_airlines,
>   (SELECT drs2.id FROM delivery_route_segments drs2 WHERE drs2.order_id = 
> o.id AND NOT drs2.completed ORDER BY drs2.position LIMIT 1) AS current_drs_id,
>
>
> FROM
>   orders o JOIN
>   order_statuses os ON (o.status = os.id) JOIN
>   delivery_route_segments drs ON (drs.order_id = o.id) JOIN
>   pick_up_addresses pua ON (pua.order_id = o.id)
> GROUP BY
>   o.id, os.status
>
> I would prefer to do the subquery as a window function, both because that
> is cleaner to read and also because I believe it is likely to be more
> efficient.
>
>
​This query is non-functional.  It has a GROUP BY without any aggregate
functions and not all of the selected columns are in the group by.

David J.

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

2016-05-17 Thread Charles Clavadetscher 
Hello all

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten Hilbert
> Sent: Dienstag, 17. Mai 2016 09:23
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> 
> On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> 
> > A question to the naming. I find pg_logo() also a good name, but is
> > the prefix pg_* not reserved for system functions? Of course I could
> > use the name I want, but was wondering if there is a policy or a best
> > practice in this area.
> 
> pg_logo would only be suitable if it got blessing from "higher up".

Well. This question will be addressed when the body of the function is 
complete. Now I have a proposal which is in the attachment.

The function accepts a set of arguments, all having default values:

p_frame BOOLEAN DEFAULT false: values true or false.
p_text TEXT[] DEFAULT NULL: Free text as array of text. Each element of the 
array is a line of text. Maximum of 8 lines. What is
above is ignored.
p_position TEXT DEFAULT 'bottom': position of the text in relation to the pic. 
bottom or right.
p_align TEXT DEFAULT 'center': Alignment of pic AND text, when p_position is 
bottom, only of the text otherwise. Values: left,
center, right.
p_valign TEXT DEFAULT 'center': Vertical alignement of the text. Only applies 
if p_position is right. Values: top, center, bottom.

Below I add a set of sample outputs. Feel free to test it and all feedbacks are 
as usual very much appreciated.

Regards,
Charles

Sample outputs:

No arguments (all defaults):
select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

Add frame:
select pg_logo(true);
  pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) |
 |  \(/ o)  ( o)   ) |
 |   \_  (_  )   \ ) _/  |
 | \  /\_/\)/|
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Add text using default positioning and alignments:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch']);
 pg_logo
-
 +-+
 |    __  ___  |
 | /)/  \/   \ |
 |( / ___\)|
 | \(/ o)  ( o)   )|
 |  \_  (_  )   \ ) _/ |
 |\  /\_/\)/   |
 | \/|
 |  _|  |  |
 |  \|_/   |
 | |
 |  Swiss PGDay 2016   |
 | 24.06.2016 - HSR Rapperswil |
 | Switzerland |
 | http://www.pgday.ch |
 | |
 +-+
(17 rows)

Position text on the right hand side of the pic:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\)  Swiss PGDay 2016   |
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change text alignment to left:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right','left');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) Swiss PGDay 2016|
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change vertical alignment of text to bottom:
select pg_logo(true,ARRAY['Swiss PGDa

Re: [GENERAL] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Victor Yegorov 
2016-05-16 14:47 GMT+03:00 Charles Clavadetscher :

> SQL version by Melvin Davidson:
>
> CREATE TABLE elephant
> (row_num integer NOT NULL,
>  row_dat varchar(30) NOT NULL,
>  CONSTRAINT elephant_pk PRIMARY KEY (row_num)
> );
>
> INSERT INTO elephant
> (row_num, row_dat)
> VALUES
> ( 1,'++'),
> ( 2,'|     __  ___|'),
> ( 3,'|  /)/  \/   \   |'),
> ( 4,'| ( / ___\)  |'),
> ( 5,'|  \(/ o)  ( o)   )  |'),
> ( 6,'|   \_  (_  )   \ ) _/   |'),
> ( 7,'| \  /\_/\)/ |'),
> ( 8,'|  \/  |'),
> ( 9,'|   _|  ||'),
> (10,'|   \|_/ |'),
> (11,'||'),
> (12,'|  PostgreSQL 1996-2016  |'),
> (13,'|  20 Years of success   |'),
> (14,'++');
>
> SELECT row_dat FROM elephant ORDER BY row_num;
>


I had a bit of fun with this SQL version and came up with this query:

WITH src(s) AS (
  VALUES
('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
), str AS (
  SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab',
'(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '')
line
FROM src, generate_series(1, 182, 2) p
)
SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;


-- 
Victor Y. Yegorov

Re: [GENERAL] Increased I/O / Writes

2016-05-17 Thread Kevin Grittner 
On Tue, May 17, 2016 at 12:11 AM, Lucas Possamai  wrote:
> This is my postgresql.conf at the moment:
>
> shared_buffer(51605MB) +
> effective_cache_size(96760MB) +
> work_mem(32MB) +
> max_connections(200)
>
> = 148397.08 MB

You are comparing some very dissimilar settings.  effective_cache_size
does not allocate memory, it tells the planner how much cache you have
allocated (i.e., the sum of shared_buffers and the OS cache).
work_mem can be allocated zero to a large number of times per active
query.  Every open connection will use some RAM, but the amount is
hard to predict exactly.

You might want to go over this page:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

... and then read the documentation of any setting you are thinking of
adjusting.

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Connections - Postgres 9.2

2016-05-17 Thread Sridhar N Bamandlapally 
Hi

I control this way

if "state_change" is from longtime and "state" is idle then I use

function:
  *pg_terminate_backend ( integer ) * ==> return TRUE if killed-successful
else FALSE

example:
# select pg_terminate_backend ( pid ) from pg_stat_activity where
state='idle' and state_change < (current_timestamp - interval '1 hour');

may be helpful

NOTE: we come to decision to kill sessions in idle state more then "1 hour"
after lot of discussion with application / implementation / stake-holders
team

*removed history as thrown error due to mail length


Thanks
Sridhar
OpenText

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

2016-05-17 Thread Bráulio Bhavamitra 
Alvaro, is this related or dependent on
https://www.pgcon.org/2016/schedule/events/920.en.html ?

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

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-17 Thread Geoff Winkless 
On 17 May 2016 at 10:22, Achilleas Mantzios
 wrote:
> On 17/05/2016 12:16, Geoff Winkless wrote:
>>
>> On 17 May 2016 at 09:34, Pierre Chevalier Géologue
>>  wrote:
>>>
>>> On this matter, I hear *very* often from such guys that the only reproach
>>> they have to PostgreSQL is that it does not come with a slick GUI like
>>> Access.  PGAdmin does not suit their needs at all: they want to design
>>> their
>>> forms, directly write into the tables by using quick'n easy/dirty
>>> copy/paste
>>> from/to their Excel (yes, it is still lying around).  I understand them,
>>> somehow.
>>> There are a few tools around, many proprietary ones, some Free/Libre
>>> ones,
>>> but they are still looking for a sort of Holy Grail that would definitely
>>> convince them.  A standard client tool that would come with any
>>> PostgreSQL
>>> installation would please them.  Some sort of psqlGUI, I guess.
>>
>> Why reinvent the wheel? I would say that putting the development
>> effort into the OpenOffice Base app would be time better spent.
>
> Sorry if I missed something but what's wrong with pgadmin3 ?

There's nothing wrong with pgadmin as such.

From the very email you just quoted:

>>> they want to design their forms

ie (as I understand it) they're after some sort of RAD tool.

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] Thoughts on "Love Your Database"

2016-05-17 Thread Achilleas Mantzios 

On 17/05/2016 12:16, Geoff Winkless wrote:

On 17 May 2016 at 09:34, Pierre Chevalier Géologue
 wrote:

On this matter, I hear *very* often from such guys that the only reproach
they have to PostgreSQL is that it does not come with a slick GUI like
Access.  PGAdmin does not suit their needs at all: they want to design their
forms, directly write into the tables by using quick'n easy/dirty copy/paste
from/to their Excel (yes, it is still lying around).  I understand them,
somehow.
There are a few tools around, many proprietary ones, some Free/Libre ones,
but they are still looking for a sort of Holy Grail that would definitely
convince them.  A standard client tool that would come with any PostgreSQL
installation would please them.  Some sort of psqlGUI, I guess.

Why reinvent the wheel? I would say that putting the development
effort into the OpenOffice Base app would be time better spent.

Sorry if I missed something but what's wrong with pgadmin3 ?

Geoff





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



--
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] Thoughts on "Love Your Database"

2016-05-17 Thread Geoff Winkless 
On 17 May 2016 at 09:34, Pierre Chevalier Géologue
 wrote:
> On this matter, I hear *very* often from such guys that the only reproach
> they have to PostgreSQL is that it does not come with a slick GUI like
> Access.  PGAdmin does not suit their needs at all: they want to design their
> forms, directly write into the tables by using quick'n easy/dirty copy/paste
> from/to their Excel (yes, it is still lying around).  I understand them,
> somehow.
> There are a few tools around, many proprietary ones, some Free/Libre ones,
> but they are still looking for a sort of Holy Grail that would definitely
> convince them.  A standard client tool that would come with any PostgreSQL
> installation would please them.  Some sort of psqlGUI, I guess.

Why reinvent the wheel? I would say that putting the development
effort into the OpenOffice Base app would be time better spent.

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] Thoughts on "Love Your Database"

2016-05-17 Thread John R Pierce 

On 5/17/2016 1:34 AM, Pierre Chevalier Géologue wrote:


On this matter, I hear *very* often from such guys that the only 
reproach they have to PostgreSQL is that it does not come with a slick 
GUI like Access.


Access is a lot more than a slick GUI, its a rapid application 
development system based on VisualBasic.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Thoughts on "Love Your Database"

2016-05-17 Thread Pierre Chevalier Géologue 

Hi,
Le 04/05/2016 17:55, Alban Hertroys a écrit :

On 4 May 2016 at 17:08, John McKown  wrote:

I had a manager, long ago, who used a Lotus 1-2-3 spreadsheet to
contain all his memos. I was glassy eyed in disbelief. He also would use his
hand calculator to add up the numbers in the spreadsheet to be sure that the
summation function in the spreadsheet didn't make a mistake. 


That still happens - we have a few live examples around in this
company. Unfortunately they're not caged and there's no sign "Do not
feed the managers". Admittedly, they're using Excel instead of
Lotus-1-2-3, but that's the only difference.


;-D


Another step (forward obviously) for these fellows is to use MSAccess. 
Much better, yes.  But, still...


On this matter, I hear *very* often from such guys that the only 
reproach they have to PostgreSQL is that it does not come with a slick 
GUI like Access.  PGAdmin does not suit their needs at all: they want to 
design their forms, directly write into the tables by using quick'n 
easy/dirty copy/paste from/to their Excel (yes, it is still lying 
around).  I understand them, somehow.
There are a few tools around, many proprietary ones, some Free/Libre 
ones, but they are still looking for a sort of Holy Grail that would 
definitely convince them.  A standard client tool that would come with 
any PostgreSQL installation would please them.  Some sort of psqlGUI, I 
guess.


Something to think about...  Maybe it would more suitable into 
"advocacy" than here in "general", though.


À+
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] Thoughts on "Love Your Database"

2016-05-17 Thread Pierre Chevalier Géologue 

Hello,

(sorry for delay, I've been traveling)

Le 04/05/2016 17:08, John McKown a écrit :

...
​Allowing PHBs direct access t​o company data is a nasty thing.


Sorry, what is a PHB?  Our friend google didn't help me much on this
matter.



​Ah. Sorry. PHB is a "Pointy Haired Boss" and is a reference to the
comic "Dilbert". Dilbert is a engineer who works for a boss who is a
complete idiot & has his hair moussed up at the sides (I guess it is
moussed).


Le 04/05/2016 17:05, Steve Crawford a écrit :

Perhaps a bit US centric but PHB = Pointy Haired Boss. See the boss in
"Dilbert".


Alright, got it, thanks.
I admire Dilbert; I've had bosses like PHB...



...
Hm.  Sounds familiar...
I usually call "excelitis" a sort of mental disease related to a use
and abuse of Excel, up to the point where one cannot imagine data
which is *not* in a table-like array.  And they think that they do
Relational Database Management...  In the 1990's, I met many-many
deeply sick persons.  I had been infected for a while, I must confess.


​Yes, I keep reading in another forum about how to interface the R
language so that the users can continue to input data into Excel, but
then have it run a R language script to produce some output. So many
there are trying to use Excel as their "user interface" because it is
just about all the user knows.


Well, that could be a good way to pull them out of excel: once they will 
realize that there is no way that they can do what R does with macros 
and formulas (yes, they will try hard to get rid of R, I'm sure...), it 
may open up their mind and consider other ways of looking at data than 
mere two-dimensional tables...





I had a manager, long ago, who used a Lotus 1-2-3​ spreadsheet to contain
all his memos. I was glassy eyed in disbelief.


Well...  I did use 1-2-3 as a sort-of database (I was young... bad 
excuse).  And also, I had used Excel to produce final reports, with 
plenty of graphics, I used the vector editing tools a lot to make fancy 
figures...

Hm.  Long time ago.



He also would use his hand calculator to add up the numbers
in the spreadsheet to be sure that the summation function in the
spreadsheet didn't make a mistake. 


Uh-oh.  This is worrying.  But it makes me think of a famous excel bug, 
where you had something like 1 - 1 = 2...  Maybe your boss was actually 
very wise (and paranoid)?...


À+
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] Increased I/O / Writes

2016-05-17 Thread Johnny Morano 
Hi

Why not use 
https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ to 
determine your optimal shared_buffers  settings? ;-)

Cheers
Johnny

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Lucas Possamai
Sent: Dienstag, 17. Mai 2016 06:12
To: Sergey Konoplev
Cc: Postgres General
Subject: Re: [GENERAL] Increased I/O / Writes

This is my postgresql.conf at the moment:

shared_buffer(51605MB) +
effective_cache_size(96760MB) +
work_mem(32MB) +
max_connections(200)

= 148397.08 MB

My server has 128GB of RAM

So, I'm using more RAM that I have. (not good at all)
I'm glad that it wasn't me who put those confs in there :)


Anyway...
I was thinking about decrease the shared_buffer to something like 1/8 of total 
RAM = 16GB

What do u guys think about it?

Cheers
Lucas

Re: [GENERAL] How to convert firebird stored procedures into postgresql functions

2016-05-17 Thread Martijn Tonies (Upscene Productions) 
Hello Elusai,

I very much doubt there’s a tool for this, given that the procedural language 
isn’t the same, so you’re looking at converting syntax.

As far as I’ve seen PLPgSQL, it’s similar and it should be quite easy to 
convert Firebird PSQL to this language.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

From: Elusai Soares 
Sent: Tuesday, May 17, 2016 2:56 AM
To: pgsql-general@postgresql.org 
Subject: [GENERAL] How to convert firebird stored procedures into postgresql 
functions

Hi there :) 

I have an internship project that consists in migrate a Firebird structure and 
data to a PostgreSQL one. I have already gotten the migration of tables, 
primary and foreign keys (and other constraints), indexes and views to a 
PostgreSQL database. 
But now I have 140 Firebird stored procedures. I need to convert them into 
PostgreSQL functions. My question is: is there any tool that could do this 
conversion? I have tried some tools such as DBTools Manager Professional, Full 
Convert and PostgreSQL Database Converter, but none of them is able to convert 
stored procedures.



I would like to find a tool because I have a little time to finish this 
activity manually, and I not even reached triggers...


Thank you all and best regards from Brazil :)

Re: [GENERAL] Ascii Elephant for text based protocols - Final

2016-05-17 Thread Karsten Hilbert 
On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:

> A question to the naming. I find pg_logo() also a good name, but is the
> prefix pg_* not reserved for system functions? Of course I could use the
> name I want, but was wondering if there is a policy or a best practice in
> this area.

pg_logo would only be suitable if it got blessing from "higher up".

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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