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

[Szymon Lipiński]

On 4 May 2016 at 23:52, Kus  wrote:

>
>
> On May 4, 2016 4:56:23 PM EDT, Andrew Sullivan 
> wrote:
> >I think there's something that we're going to have to accept, however,
> >and that's that there are way more application coders than there are
> >people who really get database systems.  Fixing this problem requires
> >years of efforts.
>
> I don't really get database systems. I remember this conversation with my
> professor who said we should use ASCII and not Unicode because we won't
> need anything beyond the ASCII characters (this was on an Oracle database).
> As an application developer, I don't know how I'd keep up with the right
> way to do things(TM) as rdbms keep evolving.
>
> :(
>
>
At one of my first jobs my boss used to tell two truths: "The database will
always be inconsistent" and "Indexes are useless, they don't give anything
except for slowing down the database". So, don't believe in everything
"wise" people say, just think, and check on your own.


-- 
regards Szymon Lipiński

Re: [GENERAL] Debian and Postgres

[Zenaan Harkness]

On 5/4/16, rob stone  wrote:
> On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote:

>> I ask because this looks somewhat similar to this thread:
>>
>> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
>> taVHwSF68wM=vmy7...@mail.gmail.com
>>
>> where the solution:
>>
>> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
>> taVHwSF68wM=vmy7...@mail.gmail.com
>>
>> was:
>>
>> "That was indeed the root cause. The /etc/hosts file on the server
>> had
>> incorrect permissions which caused localhost to not resolve."
>
> /etc/hosts has a file date of Dec 19 2014.
>
> Everything worked fine yesterday. Absolutely nothing has been altered
> except some packages have been removed and none of the log files can
> give me a clue as to which ones.

For reference Debian (and I assume Ubuntu) has a little package called
etckeeper - I find it very handy in such situations.


-- 
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] Function PostgreSQL 9.2

[John R Pierce]

On 5/4/2016 9:56 PM, drum.lu...@gmail.com wrote:



If I comment the line: *ELSEIF NEW.code IS NULL THEN*, the data is 
inserted into the users.code column.


in the table definition, whats the default value of 'code' ?


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

On 5 May 2016 at 16:56, drum.lu...@gmail.com  wrote:

> CREATE OR REPLACE FUNCTION users_code_seq()
>>RETURNS "trigger" AS $$
>> BEGIN
>>
>> IF (TG_OP = 'INSERT') THEN
>> UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE
>> company_id = NEW.company_id;
>>
>> ELSEIF NEW.code IS NULL THEN
>> SELECT last_seq INTO code FROM public.company_seqs WHERE
>> company_id = NEW.company_id ORDER BY last_seq DESC;
>>
>> END IF;
>>
>> RETURN NEW;
>>
>> END;
>> $$ LANGUAGE plpgsql;
>
>
> - The command above, does not insert the last_seq into users.code column.
> And I don't know why.
>
>
> If I comment the line: *ELSEIF NEW.code IS NULL THEN*, the data is
> inserted into the users.code column.
>
> But as the customer can add data into that column, I only insert the
> last_seq if he/she didn't insert anything.
>
> So I need that *ELSEIF NEW.code IS NULL THEN*.
>
> - What am I missing?
>
> Thanks
> Lucas
>



I got it.. it's working now.
Thanks

Re: [GENERAL] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

>
> CREATE OR REPLACE FUNCTION users_code_seq()
>RETURNS "trigger" AS $$
> BEGIN
>
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE
> company_id = NEW.company_id;
>
> ELSEIF NEW.code IS NULL THEN
> SELECT last_seq INTO code FROM public.company_seqs WHERE
> company_id = NEW.company_id ORDER BY last_seq DESC;
>
> END IF;
>
> RETURN NEW;
>
> END;
> $$ LANGUAGE plpgsql;


- The command above, does not insert the last_seq into users.code column.
And I don't know why.


If I comment the line: *ELSEIF NEW.code IS NULL THEN*, the data is inserted
into the users.code column.

But as the customer can add data into that column, I only insert the
last_seq if he/she didn't insert anything.

So I need that *ELSEIF NEW.code IS NULL THEN*.

- What am I missing?

Thanks
Lucas

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

[George Neuner]

On Tue, 3 May 2016 23:11:06 -0500, Guyren Howe 
wrote:

>I've long been frustrated with how most web developers I meet 
>have no idea how to use an SQL database properly. I think I'm 
>going to write a book called Love Your Database, aimed at web
>developers, that explains how to make their apps better by 
>leveraging the power of SQL in general, and Postgres in particular.
>
>I'm thinking of a section on features of SQL most folks don't know
>about (CTEs are *way* to hell at the top of that list, but also 
>EXCEPT/INTERSECT and window functions), but much of the book
>would be about how to do things server side. Benchmarks showing
>how much faster this can be, but mostly techniques — stored
>procedures/triggers/rules, views.
>
>I asked a colleague about the advice I often hear stated but seldom
>justified, that one shouldn't put business rules in the database. He 
>offered that server-side code can be hard to debug.
>
>I'm sure many here would love to see such a book published, 
>maybe some talks on the topic given.

I think such a book would be wonderful.  Unfortunately, I doubt many
web coders would take the time to read it.

You might want a chapter or 3 on Model-View-Controller ... where it is
appropriate and where it isn't.  I've seen some truly spectacular
backflips done by code trying to shoehorn uncooperative data models
into MVC.


>What might I cover that I haven't mentioned? What are the usual 
>objections to server-side code and how can they be met? When 
>*are* they justified and what should the criteria be to put code in 
>Postgres? Any other thoughts? Any other websites or books on
>the topic I might consult?

FWIW: I have a master degree in data modeling.  I design databases,
and when necessary write web facing middleware for them.

The usual objection to stored code is highly conditional queries.  For
example, my most recent web project has a search which is ~100 lines
of SQL with 7 CTEs, 5 of which are executed conditionally depending on
user input.  This kind of dynamic code is painful to write in most SQL
dialects.

I compose such queries in middleware preferentially because I can use
languages better suited to complex string manipulation.  And yes, I am
aware of injection: SQL may be composed dynamically, but user input is
/never/ spliced - it always is passed via SQL parameters.

I am aware that Postgresql has other languages available as
extensions.  Some of them would do the job - though I think not as
nicely as my goto language: Racket (a Scheme dialect).
[Yes, I know Guile (Scheme) is one of the extension languages.]

The code wouldn't be any less complicated for being resident in the
DBMS, and I doubt it would be much faster: my middleware is always
either co-located with the DBMS, or on the same LAN if working with a
cluster.

I draw the line at giving web clients direct access to a database -
any web facing system I design always involves mediation via
middleware.   IME it is the copying/conversion of data to/from the
HTTP interface that ultimately limits performance, so where to put the
database code largely is a judgement call.

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] Debian and Postgres

[John R Pierce]

On 5/4/2016 1:55 PM, rob stone wrote:

I can connect via psql and issue queries without any problems. Trying
to connect via JDBC fails. Trying to connect by an application fails.


one potential difference, psql will connect via a unix domain socket if 
you  don't specify a -h hostname, while JDBC can only connect via a tcp 
socket.  jdbc connecting to localhost will match `host` lines in the 
pg_hba.conf file, while psql connecting without a host specification 
will match `local` line(s).


so, please show us your jdbc connection string, and your psql command 
line, and also show us your pg_hba.conf file.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Debian and Postgres

[Adrian Klaver]

On 05/04/2016 07:40 PM, rob stone wrote:

On Wed, 2016-05-04 at 16:50 -0700, Adrian Klaver wrote:

On 05/04/2016 04:38 PM, rob stone wrote:




Hmm, it is Ubuntu not Debian, but:

sudo apt-get remove whois

vi /var/log/dpkg.log

2016-05-04 16:42:39 status installed whois:amd64 5.1.1
2016-05-04 16:42:39 remove whois:amd64 5.1.1 
2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1
2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1
2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1-
1ubuntu1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status not-installed whois:amd64 


vi /var/log/apt/history.log

Start-Date: 2016-05-04  16:42:39
Commandline: apt-get remove whois
Remove: whois:amd64 (5.1.1)
End-Date: 2016-05-04  16:42:39



Hello Adrian,

I read Tom's post. There is no firewall. Stand alone box.

The apt history.log is overwritten each time.


So Debian does not rotate the logs into history.log..gz?


There is a term.log (full of Control-M's) which lists all the activity,
including the removals, as follows:-

liblept4 replaced by liblept5 and is used for image processing.
liblwres90 replaced by liblwres141 used by BIND
libpcrecpp0 replaced by libcrecpp0v5 used for Perl Regex
libsodium13 replaced by libsodium18 NaCl library
tzdata-java now gone for good, and
libdbus2.0-cil removed from Mono -- not mono -- as in mono-runtime,
etc.

So, liblwres and libsodium are the two obvious culprits. I'll have to
obtain the sources and diff the files to see if something contained in
the earlier version is missing or altered in the latest version.

I have also gone thru the major packages that have anything to do with
ports, sockets, etc. such as network-manager, isc-dhcp-client, doing an
"apt show" to list dependencies, then verifying that those dependencies
are the correct versions. So far libreadline6 and libdns-export162 were
earlier versions and libteam-utils was missing.

So, I'll let you know what else I find.


So what are the connection parameters that work for you with psql?



Thanks for the help.

Cheers,
Rob




--
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] CREATE OR REPLACE AGGREGATE -- NOT!

[David G. Johnston]

On Wednesday, May 4, 2016, dandl  wrote:

> I was surprised to discover that
>
> · CREATE OR REPLACE AGGREGATE is not allowed, but
>
> · CREATE OR REPLACE FUNCTION works just fine
>
>
>
> Is this an oversight, or is there some reason I missed?
>
>
 Its not an oversight: but whether it is easily accomplished, or worthy of
the effort, is a different matter.


> Yes, I know I can do:
>
> DROP AGGREGATE IF EXISTS
>
> CREATE AGGREGATE
>
>
>
> But this triggers a NOTICE. Is there any convenient way to replace a
> (possibly) existing aggregate function without triggering a NOTICE?
>
>
Set client_min_messages = warning;

David J.

Re: [GENERAL] Debian and Postgres

[rob stone]

On Wed, 2016-05-04 at 16:50 -0700, Adrian Klaver wrote:
> On 05/04/2016 04:38 PM, rob stone wrote:
> > 
> > 
> Hmm, it is Ubuntu not Debian, but:
> 
> sudo apt-get remove whois
> 
> vi /var/log/dpkg.log
> 
> 2016-05-04 16:42:39 status installed whois:amd64 5.1.1
> 2016-05-04 16:42:39 remove whois:amd64 5.1.1 
> 2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1
> 2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1
> 2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1-
> 1ubuntu1
> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
> 2016-05-04 16:42:39 status not-installed whois:amd64 
> 
> 
> vi /var/log/apt/history.log
> 
> Start-Date: 2016-05-04  16:42:39
> Commandline: apt-get remove whois
> Remove: whois:amd64 (5.1.1)
> End-Date: 2016-05-04  16:42:39
> 

Hello Adrian,

I read Tom's post. There is no firewall. Stand alone box.

The apt history.log is overwritten each time.
There is a term.log (full of Control-M's) which lists all the activity,
including the removals, as follows:-

liblept4 replaced by liblept5 and is used for image processing.
liblwres90 replaced by liblwres141 used by BIND
libpcrecpp0 replaced by libcrecpp0v5 used for Perl Regex
libsodium13 replaced by libsodium18 NaCl library
tzdata-java now gone for good, and
libdbus2.0-cil removed from Mono -- not mono -- as in mono-runtime,
etc.

So, liblwres and libsodium are the two obvious culprits. I'll have to
obtain the sources and diff the files to see if something contained in
the earlier version is missing or altered in the latest version.

I have also gone thru the major packages that have anything to do with
ports, sockets, etc. such as network-manager, isc-dhcp-client, doing an
"apt show" to list dependencies, then verifying that those dependencies
are the correct versions. So far libreadline6 and libdns-export162 were
earlier versions and libteam-utils was missing.

So, I'll let you know what else I find.

Thanks for the help.

Cheers,
Rob


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

[GENERAL] CREATE OR REPLACE AGGREGATE -- NOT!

[dandl]

I was surprised to discover that

* CREATE OR REPLACE AGGREGATE is not allowed, but

* CREATE OR REPLACE FUNCTION works just fine

 

Is this an oversight, or is there some reason I missed?

 

Yes, I know I can do:

DROP AGGREGATE IF EXISTS

CREATE AGGREGATE

 

But this triggers a NOTICE. Is there any convenient way to replace a (possibly) 
existing aggregate function without triggering a NOTICE?

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

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

[dandl]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Sofen



I disagree.  I’ve worked as database architect/engineer at a number of large 
and small firms in various verticals (healthcare, financials, insurance, 
aerospace, telecom, etc), and created complete database api’s via stored 
procs/stored functions, some of which were quite complex.  I’ve found that a 
mid-level database developer, with modest coaching and good comments in the 
code, can pick up the code, support it and even enhance it.  So the notion that 
experts can only write and maintain quality code isn’t valid in my experience.

 

The original proposition was that this (a stored procedure API) was such a 
simple task any beginner could do it with ease. My view is that it is a job for 
a seasoned application developer with SQL skills that go well beyond simple 
queries. I don’t see much disagreement here.

 

There is definitely a difference in capability/velocity/solution  solving 
between junior, mid-level and senior developers, but that isn’t a deal killer, 
it’s just something that needs to be managed and accounted for.  

 

One reason for a database api is that ORMs have proved themselves incapable of 
proper scaling and ACID compliance, where stored procs/functions are capable of 
leveraging the massive set-based relational power of the underlying engine, and 
leverage efficient functionalities like windowing functions.

 

I agree absolutely. ORMs exists because of a strong desire to do business logic 
coding in the big 5 OO languages and not in whatever dialect of SQL may or may 
not be available on the RDBMS in question. The whole point of Andl is to make 
set-based relational logic including user-written accumulation functions, 
recursion (CTE) and ordered queries (windowing) available and accessible to 
ordinary developers, and totally avoid the need for an ORM.

 

So I guess you’d say I’m in the entirely opposite camp, since it’s proven to be 
such an effective solution architecture for many applications that leverage 
relational database engines.

 

It is indeed a totally effective architecture. If you haven’t already read it I 
strongly recommend 
http://thehelsinkideclaration.blogspot.com.au/2009/03/window-on-data-applications.html
 fora detailed description. It’s effective, but it sure ain’t easy, especially 
if you value portability between RDBMS. Andl aims to fill that gap: make that 
architecture accessible to all.

 

I don’t think we’re in different camps at all.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

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

[Mike Sofen]

From: dandlSent: Wednesday, May 04, 2016 5:05 PM
To: 'Pierre Chevalier Géologue' 



> From: Pierre Chevalier Géologue [  
> mailto:pierrechevalierg...@free.fr]

> ...

> > Then I think you've seriously misunderstood. Most people can indeed 

> >learn to write basic SQL queries, but those are

> >(obviously) not what I'm talking about.

> >

> > To write the business logic of a significant application entirely in 

> >SQL requires PLSQL (or in other dialects, whatever passes for SQL/PSM).

> >It means writing an entire data access layer as a set of stored 

> >procedures, with a substantial set of special functions, types, 

> >triggers and so on. No beginner and few experts have the skills 

> >required to do that in SQL, and then debug that code on the server.

> 

> All right, I understand better now.  I think I also totally missed 

> your point, sorry...

> I'll give a look at andl.

 

I hope you do. Please feel free to contact me with any comments, suggestions, 
etc.

 

I have not completed the Postgres implementation -- probably another couple of 
weeks – 

but in-memory and Sqlite are there.

 

Bonne chance!

 

Regards

David M Bennett FACS

===

 

I disagree.  I’ve worked as database architect/engineer at a number of large 
and small firms in various verticals (healthcare, financials, insurance, 
aerospace, telecom, etc), and created complete database api’s via stored 
procs/stored functions, some of which were quite complex.  I’ve found that a 
mid-level database developer, with modest coaching and good comments in the 
code, can pick up the code, support it and even enhance it.  So the notion that 
experts can only write and maintain quality code isn’t valid in my experience.

 

There is definitely a difference in capability/velocity/solution  solving 
between junior, mid-level and senior developers, but that isn’t a deal killer, 
it’s just something that needs to be managed and accounted for.  

 

One reason for a database api is that ORMs have proved themselves incapable of 
proper scaling and ACID compliance, where stored procs/functions are capable of 
leveraging the massive set-based relational power of the underlying engine, and 
leverage efficient functionalities like windowing functions.

 

So I guess you’d say I’m in the entirely opposite camp, since it’s proven to be 
such an effective solution architecture for many applications that leverage 
relational database engines.

 

Mike Sofen  (San Diego, CA  USA)

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

[Mike Sofen]

>From: Vincent Veyron   Sent: Wednesday, May 04, 2016 3:35 PM

>I write management applications for businesses, and give _a lot_ of care to
the database structure. 
>I find that the number of lines of code that need to be written is strictly
inversely correlated to the
 >appropriateness of the database design (meaning that the better the
database structure, the lesser 
>code is needed).

>Knowing about the many fine functions Postgresql offers also helps, of
course.
>   Bien à vous, Vincent Veyron 


An inverse corollary is also true, imo: encountering demands for exotic,
wild functions, cursors and code constructs (like triggers calling triggers
or frankly, triggers in general), is nearly always an indication of poor
database design.  I'm not talking about windowing functions or json or CTEs,
btw.

Postgres and mysql have piles and piles of functions that I will never use
and can't even imagine scenarios in which to use them.  So I agree 100% -
it's all about the database (design).   BTW, I'm currently
designing/building OLTP databases for use in genomics research (using
Postgres)...that's big data...where there is zero tolerance for slack db
design that could cause scalability or performance issues.  My stored
functions are...relatively simple.

Mike Sofen  (San Diego, CA USA)




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

[dandl]

> From: Pierre Chevalier Géologue [mailto:pierrechevalierg...@free.fr]
> ...
> > Then I think you've seriously misunderstood. Most people can indeed
> >learn to write basic SQL queries, but those are
> >(obviously) not what I'm talking about.
> >
> > To write the business logic of a significant application entirely in
> >SQL requires PLSQL (or in other dialects, whatever passes for SQL/PSM).
> >It means writing an entire data access layer as a set of stored
> >procedures, with a substantial set of special functions, types,
> >triggers and so on. No beginner and few experts have the skills
> >required to do that in SQL, and then debug that code on the server.
> 
> All right, I understand better now.  I think I also totally missed your
> point, sorry...
> I'll give a look at andl.

I hope you do. Please feel free to contact me with any comments, suggestions, 
etc.

I have not completed the Postgres implementation -- probably another couple of 
weeks -- but in-memory and Sqlite are there.

Bonne chance!

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] Function PostgreSQL 9.2

[Berend Tober]

David G. Johnston wrote:

On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com
...

I would expect a minimum of respect from the members of this list,
but seems you got none. If someone would need my help, I'd never
insult him/her like you guys are doing.

If my questions are too "child" for you, please, do not answer them.
Ignore the emails... Isn't that simple?

Talking to me like you guys are talking, is what I call "a ten-years
old post".


​I'm not sure is this was meant to be a play on words but the original
use of the phrase meant "a post written 10 years ago" - not that you are
acting like a 10 year old.



My apologies for causing that misunderstanding. The phrase was 
definitely not meant to imply the post of a 10-year old childs 
intelligence (although I suppose it would have been impressive) ... I 
was considerably older than that when I posted that example of compound 
sequences ... 10-years ago.





FWIW I didn't read any kind of "stealing of credit" in what you wrote -
nor likely did most people.  That particular observation came out of
left field.



As I said, I might be over-sensitive on this ... being the original 
author of the copied example implementation.







--
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] Debian and Postgres

[Adrian Klaver]

On 05/04/2016 04:38 PM, rob stone wrote:

On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote:



I can connect via psql and issue queries without any problems.
Trying
to connect via JDBC fails. Trying to connect by an application
fails.

Are you using the same connection parameters?

In particular are you using local for the psql connection and some
form
of host for the others?

I ask because this looks somewhat similar to this thread:

http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
taVHwSF68wM=vmy7...@mail.gmail.com

where the solution:

http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
taVHwSF68wM=vmy7...@mail.gmail.com

was:

"That was indeed the root cause. The /etc/hosts file on the server
had
incorrect permissions which caused localhost to not resolve."




/etc/hosts has a file date of Dec 19 2014.


Did you see Tom's post.



Everything worked fine yesterday. Absolutely nothing has been altered
except some packages have been removed and none of the log files can
give me a clue as to which ones.


Hmm, it is Ubuntu not Debian, but:

sudo apt-get remove whois

vi /var/log/dpkg.log

2016-05-04 16:42:39 status installed whois:amd64 5.1.1
2016-05-04 16:42:39 remove whois:amd64 5.1.1 
2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1
2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1
2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1-1ubuntu1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status config-files whois:amd64 5.1.1
2016-05-04 16:42:39 status not-installed whois:amd64 


vi /var/log/apt/history.log

Start-Date: 2016-05-04  16:42:39
Commandline: apt-get remove whois
Remove: whois:amd64 (5.1.1)
End-Date: 2016-05-04  16:42:39




I'll just have to continue hunting around trying to figure out what I
did and probably file a bug report against synaptic for losing the
removal info from its history logs.

Cheers,
Rob




--
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] Debian and Postgres

[rob stone]

On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote:
> 
> > I can connect via psql and issue queries without any problems.
> > Trying
> > to connect via JDBC fails. Trying to connect by an application
> > fails.
> Are you using the same connection parameters?
> 
> In particular are you using local for the psql connection and some
> form 
> of host for the others?
> 
> I ask because this looks somewhat similar to this thread:
> 
> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
> taVHwSF68wM=vmy7...@mail.gmail.com
> 
> where the solution:
> 
> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
> taVHwSF68wM=vmy7...@mail.gmail.com
> 
> was:
> 
> "That was indeed the root cause. The /etc/hosts file on the server
> had
> incorrect permissions which caused localhost to not resolve."
> 
> 

/etc/hosts has a file date of Dec 19 2014.

Everything worked fine yesterday. Absolutely nothing has been altered
except some packages have been removed and none of the log files can
give me a clue as to which ones.

I'll just have to continue hunting around trying to figure out what I
did and probably file a bug report against synaptic for losing the
removal info from its history logs.

Cheers,
Rob


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

[Vincent Veyron]

On Tue, 3 May 2016 23:11:06 -0500
Guyren Howe  wrote:

> Any other thoughts? 

I like this quote from Fred Brooks : 

`Show me your code and conceal your data structures, and I shall continue to be 
mystified. Show me your data structures, and I won't usually need your code; 
it'll be obvious.'

It seems to be confirmed by some rather qualified people :

http://lwn.net/Articles/193245/

I write management applications for businesses, and give _a lot_ of care to the 
database structure. I find that the number of lines of code that need to be 
written is strictly inversely correlated to the appropriateness of the database 
design (meaning that the better the database structure, the lesser code is 
needed).

Knowing about the many fine functions Postgresql offers also helps, of course.

-- 
Bien à vous, Vincent Veyron 

https://libremen.com
Logiciels de gestion, libres


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

[GENERAL] Very slow update / hash join

[Kurt Roeckx]

Hi,

I have an update query that's been running for 48 hours now.
Since it started it used about 2.5% CPU, and is writing to the
disk at about 3 MB/s, and reading at about 2 MB/s.  It's mostly
waiting for the disks.

The query plan looks like this:
  QUERY PLAN
--
 Update on certificates c  (cost=1224052.45..60710389.31 rows=19950420 
width=1371)
   ->  Hash Join  (cost=1224052.45..60710389.31 rows=19950420 width=1371)
 Hash Cond: (c.id = cu.id)
 ->  Seq Scan on certificates c  (cost=0.00..8372137.31 rows=147868231 
width=1258)
 ->  Hash  (cost=623981.20..623981.20 rows=19950420 width=117)
   ->  Seq Scan on certificates_update cu  (cost=0.00..623981.20 
rows=19950420 width=117)

I've set the work_mem for this query to 6GB, which seem to be
enough to make pgsql_tmp empty, when it was only set to 1 GB it
did have files in it.  The process is using about 4GB of RAM, of
which 0.5 probably comes from the shared_buffers.

It did use 100% CPU at the start, but that was for about 80
seconds.  I'm guessing that's the time it needs to read and hash
the update table.  But from that point on, it gets really slow.

As you can see, the table is quite large and I want to update
about 20M rows of the 133M rows (not sure why the plan say 147M)

The table itself is 53GB, and the table it updates from is only
3.3 GB.  There are some index on some of the fields (like the id),
but none of them are being updated.  I tried removing those that
did get updated but that had little effect.  It does have foreign
keys to other tables, and other tables references it, but none of
the keys should get updated.

Reading or writing the whole table shouldn't take that long, and I
have no idea why it's this slow.  Does anybody have an idea why
it's this slow?

>From what I understand, the hash join should be the one I want to
use, I tried to force the others but that doesn't seem to improve
anything.


Kurt



-- 
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] Function PostgreSQL 9.2

[David G. Johnston]

On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com 
wrote:

>
>>
>> 1) I just may be over-sensitive to this, but after Adrian Klaver referred
>> you to a ten-years old post that the above looks an awful lot similar too,
>> it sure would be nice to see some attribution
>> rather than claiming it as your own with "...what *I* did..."
>>
>
>
> I would expect a minimum of respect from the members of this list, but
> seems you got none. If someone would need my help, I'd never insult him/her
> like you guys are doing.
>
> If my questions are too "child" for you, please, do not answer them.
> Ignore the emails... Isn't that simple?
>
> Talking to me like you guys are talking, is what I call "a ten-years old
> post".
>
>
​I'm not sure is this was meant to be a play on words but the original use
of the phrase meant "a post written 10 years ago" - not that you are acting
like a 10 year old.

FWIW I didn't read any kind of "stealing of credit" in what you wrote - nor
likely did most people.  That particular observation came out of left field.

David J.
​

Re: [GENERAL] Function PostgreSQL 9.2

[drum.lu...@gmail.com]

>
>
>
> 1) I just may be over-sensitive to this, but after Adrian Klaver referred
> you to a ten-years old post that the above looks an awful lot similar too,
> it sure would be nice to see some attribution
> rather than claiming it as your own with "...what *I* did..."
>


I would expect a minimum of respect from the members of this list, but
seems you got none. If someone would need my help, I'd never insult him/her
like you guys are doing.

If my questions are too "child" for you, please, do not answer them. Ignore
the emails... Isn't that simple?

Talking to me like you guys are talking, is what I call "a ten-years old
post".


3) Not sure you need a separate company_seq table. Since there is a
> one-to-one relation between company and company_seqs, put the last_seq
> column in the company table.


There is no company table, my friend.

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

[Kus]

On May 4, 2016 4:56:23 PM EDT, Andrew Sullivan  wrote:
>I think there's something that we're going to have to accept, however,
>and that's that there are way more application coders than there are
>people who really get database systems.  Fixing this problem requires
>years of efforts.

I don't really get database systems. I remember this conversation with my 
professor who said we should use ASCII and not Unicode because we won't need 
anything beyond the ASCII characters (this was on an Oracle database). As an 
application developer, I don't know how I'd keep up with the right way to do 
things(TM) as rdbms keep evolving. 

:( 


-- 
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] Debian and Postgres

[Adrian Klaver]

On 05/04/2016 01:55 PM, rob stone wrote:

Hello,

This is a Debian problem that has caused a problem starting Postgres.
So, I'm posting it here in the hope that somebody has experienced it
previously and can assist me in fixing it.

Yesterday, it started up as per normal and first command issued via
JDBC driver was run:-

2016-05-04 10:39:39 AESTLOG:  MultiXact member wraparound protections
are now enabled
2016-05-04 10:39:39 AESTLOG:  database system is ready to accept
connections
2016-05-04 10:39:39 AESTLOG:  autovacuum launcher started
2016-05-04 10:40:00 AESTLOG:  duration: 224.906 ms  parse :
SET extra_float_digits = 3


Today, the following was in the log:-

2016-05-05 03:44:53 AESTLOG:  test message did not get through on
socket for statistics collector
2016-05-05 03:44:53 AESTLOG:  disabling statistics collector for lack
of working socket
2016-05-05 03:44:53 AESTWARNING:  autovacuum not started because of
misconfiguration
2016-05-05 03:44:53 AESTHINT:  Enable the "track_counts" option.
2016-05-05 03:44:53 AESTLOG:  database system was shut down at 2016-05-
04 11:56:37 AEST
2016-05-05 03:44:54 AESTLOG:  MultiXact member wraparound protections
are now enabled
2016-05-05 03:44:54 AESTLOG:  database system is ready to accept
connections
2016-05-05 03:58:29 AESTLOG:  duration: 787.241 ms  statement: select
count(*) from boiler_plate;


Relative section of conf file unchanged for months:-

# - Query/Index Statistics Collector -

#track_activities = on
track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024   # (change requires restart)
stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


I can connect via psql and issue queries without any problems. Trying
to connect via JDBC fails. Trying to connect by an application fails.


Are you using the same connection parameters?

In particular are you using local for the psql connection and some form 
of host for the others?


I ask because this looks somewhat similar to this thread:

http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8taVHwSF68wM=vmy7...@mail.gmail.com

where the solution:

http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8taVHwSF68wM=vmy7...@mail.gmail.com

was:

"That was indeed the root cause. The /etc/hosts file on the server had
incorrect permissions which caused localhost to not resolve."




I use synaptic for package management. Yesterday I was running short on
space and removed some packages described as *obsolete* by synaptic.
My guess is that a dependency chain is incorrect and a certain package
is not in fact obsolete.
For some reason the synaptic history log only shows tzdata-java as
being removed but in fact several libraries and other packages were
permanently removed. The dpkg logs only show installation details.
I don't know why synaptic failed to record the removals in its history
logs as if it had, then I could trawl thru the logs and restore the
missing packages.
The apt logs do not list removals either.

Versions:-

Java JDK version 1.8.0_91
Linux [ 4.5.0-1-amd64 ]
JDBC 9.4-1208
Postgres psql (9.5.2)
libpq5_9.5.2-1_amd64.deb


Any assistance appreciated.

Thanks,
Rob







--
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] Debian and Postgres

[Tom Lane]

rob stone  writes:
> This is a Debian problem that has caused a problem starting Postgres.

> Today, the following was in the log:-
> 2016-05-05 03:44:53 AESTLOG:  test message did not get through on
> socket for statistics collector
> I can connect via psql and issue queries without any problems. Trying
> to connect via JDBC fails. Trying to connect by an application fails.

Sounds like you've got a firewall that is blocking local TCP connections
into and out of Postgres.  This doesn't have anything to do with package
addition or removal, it's an over-aggressive packet filter.

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

[Andrew Sullivan]

On Tue, May 03, 2016 at 11:11:06PM -0500, Guyren Howe wrote:
> I've long been frustrated with how most web developers I meet have no idea 
> how to use an SQL database properly. I think I'm going to write a book called 
> Love Your Database, aimed at web developers, that explains how to make their 
> apps better by leveraging the power of SQL in general, and Postgres in 
> particular.
> 

For whatever it's worth, more than 10 years ago I reviewed (for a
publisher) a book along these lines.  To my knowledge, it never got
published, though I thought it was quite good.

The problem is that most development on the web does not, as far as I
can tell, regard the development as what I'd think of as a real
database-backed system.  An awful lot of web systems, in particular,
treat the database as a fancy and expensive but somewhat portable
filesystem.  (This is even more true now that SQL isn't as ubiquitous
as it once was.)

I still think this is worth promoting, but it seems to me that
attempting to create some sort of buzz around these ideas at various
developer community events.  But there are three things that I think
would help make this work for more developers:

1.  This will make your application faster.  (This needs to be
proved -- I agree with the "benchmarks" point in the original
post.)

2.  This will make your time to ship/continuous integration/time
to fix bugs shorter.  This is harder to prove, but it's an
important value for most developers (and developer managers).

3.  This is way cooler than $x.

(3) is a distant 3d, but my experience of managing less-experienced
developers who go to conferences suggest that it's a good way to get
people interested.

I think there's something that we're going to have to accept, however,
and that's that there are way more application coders than there are
people who really get database systems.  Fixing this problem requires
years of efforts.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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

[GENERAL] Debian and Postgres

[rob stone]

Hello,

This is a Debian problem that has caused a problem starting Postgres.
So, I'm posting it here in the hope that somebody has experienced it
previously and can assist me in fixing it.

Yesterday, it started up as per normal and first command issued via
JDBC driver was run:-

2016-05-04 10:39:39 AESTLOG:  MultiXact member wraparound protections
are now enabled
2016-05-04 10:39:39 AESTLOG:  database system is ready to accept
connections
2016-05-04 10:39:39 AESTLOG:  autovacuum launcher started
2016-05-04 10:40:00 AESTLOG:  duration: 224.906 ms  parse :
SET extra_float_digits = 3


Today, the following was in the log:-

2016-05-05 03:44:53 AESTLOG:  test message did not get through on
socket for statistics collector
2016-05-05 03:44:53 AESTLOG:  disabling statistics collector for lack
of working socket
2016-05-05 03:44:53 AESTWARNING:  autovacuum not started because of
misconfiguration
2016-05-05 03:44:53 AESTHINT:  Enable the "track_counts" option.
2016-05-05 03:44:53 AESTLOG:  database system was shut down at 2016-05-
04 11:56:37 AEST
2016-05-05 03:44:54 AESTLOG:  MultiXact member wraparound protections
are now enabled
2016-05-05 03:44:54 AESTLOG:  database system is ready to accept
connections
2016-05-05 03:58:29 AESTLOG:  duration: 787.241 ms  statement: select
count(*) from boiler_plate;


Relative section of conf file unchanged for months:-

# - Query/Index Statistics Collector -

#track_activities = on
track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024   # (change requires restart)
stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


I can connect via psql and issue queries without any problems. Trying
to connect via JDBC fails. Trying to connect by an application fails.

I use synaptic for package management. Yesterday I was running short on
space and removed some packages described as *obsolete* by synaptic.
My guess is that a dependency chain is incorrect and a certain package
is not in fact obsolete.
For some reason the synaptic history log only shows tzdata-java as
being removed but in fact several libraries and other packages were
permanently removed. The dpkg logs only show installation details.
I don't know why synaptic failed to record the removals in its history
logs as if it had, then I could trawl thru the logs and restore the
missing packages.
The apt logs do not list removals either.

Versions:-

Java JDK version 1.8.0_91
Linux [ 4.5.0-1-amd64 ]
JDBC 9.4-1208
Postgres psql (9.5.2)
libpq5_9.5.2-1_amd64.deb


Any assistance appreciated.

Thanks,
Rob




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

[Will McCormick]

Yeah but your already paying for a developer ...

On Wed, May 4, 2016 at 2:36 PM, Szymon Lipiński  wrote:

>
>
> On 4 May 2016 at 20:20, Will McCormick  wrote:
>
>> I 100% agree with you. It's always been a problem but it is up to us to
>> take ownership and provide value. Some would be surprising shocked how
>> simple it is to manage the Data access layer once the framework is in place
>> regardless of what it is written in. For the same reasons you wouldn't
>> typically have Application Developers configuring your production disks for
>> high performance... why would you ever have them access the database
>> inefficiently? There is an assumption designers are good at SQL or at least
>> know it ... I challenge you to flip that around and learn the Data Access
>> Layer.  Companies do not knowingly spend money on hardware to have it
>> consumed by inefficient data access? No executive signs up to increase the
>> TCO and reduce profit margins when they could be making more money? But
>> this is far to often the case and the root cause is they did not have the
>> right tool (pun not intended) for the job.
>>
>> On Wed, May 4, 2016 at 1:33 PM, Szymon Lipiński 
>> wrote:
>>
>>>
>>>
>>> On 4 May 2016 at 19:09, Will McCormick  wrote:
>>>
 I agree that it's not like turning on the light switch. And I'm not
 implying there isn't a logic layer between the database and the
 application. Based off my past experiences I would likely not put business
 logic in the database unless it was a critical for performance. This does
 not make it portable and does the performance of my product require it? It
 really comes down to the application there is not one big paint brush. We
 have all be around and get this. I would not likely design a solution that
 had the database and the application layers both containing the business
 logic. I have seen this and the unexpected behavior as assumptions are made
 on both ends of that spectrum. I like to keep it simple where I can. This
 all being said I think database minded folks should own DAO's. I think if
 your a database guy and you don't own the DAO's you are missing an
 opportunity to really make a difference and get more aligned with your
 development staff. Doesn't matter what code base DAO's are in it's a
 repetitive pattern that any database person can pick up.

 On Wed, May 4, 2016 at 12:29 PM, Szymon Lipiński 
 wrote:

>
>
> On 4 May 2016 at 18:14, Will McCormick  wrote:
>
>> I agree it's typically political but so are most things business.
>> Examples:  Companies buy other companies - You are using a competitors 
>> data
>> store and want to replace it.  Company needs to compete with competitors
>> and wants to reduce cost ... these are not technical requirements and 
>> it's
>> certainly not vapor ideology. I have only worked for startups and have 
>> seen
>> this happen at every company i have worked for, yes it is political but 
>> yes
>> it happens. Smaller companies are more susceptible to it.
>>
>> The reality is somewhere in the middle as it often is. My point is
>> you don't have to replace a million lines of code if you plan upfront. If
>> you don't .. you do.
>>
>>
>> On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:
>>
>>> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>>>
>>> A reason to consider may be portability. What happens if I want to
>>> let my customer chose their data store or I just don't want to put all 
>>> my
>>> eggs in one basket.Technically there are truths but you cannot ignore 
>>> the
>>> business side either. If a we can exceed our performance requirements 
>>> and
>>> keep things generic/portable this is the best of both worlds.I think 
>>> this
>>> is the main reason people separate the business logic from the database.
>>> How many of you have ported databases between platforms? Or had multiple
>>> types of data stores in the same company?
>>>
>>> I have been waiting for the portability argument for the last 20+
>>> posts :-) Everyone who did any type of consulting/working in this
>>> field knows that latest the second argument from management is
>>> “portability”. I have yet to see anyone who really needed to move to a
>>> different database. If they did it usually is a political issue and not 
>>> a
>>> technical one (uhh, we hired this new operations manager and MS SQL is 
>>> so
>>> much better than postgresql …) Unless you develop a generic software to 
>>> be
>>> sold to many clients, the choice of data storage is rarely a real 
>>> concern
>>> unless someone who feels the urge to be important starts 

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

[Guyren Howe]

On May 4, 2016, at 11:59 , Paul Jungwirth  wrote:
> 
> I think teaching this is a different thing than just teaching SQL syntax. I 
> haven't seen it written about a lot. It must be somewhere, but any book 
> encouraging "outsiders" to use more SQL could benefit from giving them 
> direction like that.

I've never seen relational databases explained properly for beginners.

I did a pretty successful boot camp two-week database course where it was 
heavily built around set operations. I started with a simple query on a table, 
then we went into all the cool things you could do to combine queries using 
UNION, INTERSECT and EXCEPT. To this day, I hardly ever see set operations used 
in SQL, and oftentimes when you have a hard query to write, set operations are 
the natural expression. Anyhow.

-- 
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] Debugging code on server?

[Szymon Lipiński]

On 4 May 2016 at 20:30, Thomas Kellerer  wrote:

> Guyren Howe schrieb am 04.05.2016 um 18:43:
>
>> This is a fork from my "Love your database" question. It's a
>> separable concern, so I moved it here.
>>
>> Let's say I want to use Postgres' *amazing* support for lots of
>> languages. I want to use Javascript or PERL or Ruby or something. How
>> do I debug the code?
>>
>> I can imagine things you might set up: using LISTEN/NOTIFY to a
>> simple logger that would let you see what's going on. There are
>> breakpoints and such in PGAdmin, but I'll be that doesn't work for
>> other languages.
>>
>>
> There is a unit test framework for Postgres
>
> http://pgtap.org/
>
>
>
Right, and it is quite painful to use compared to writing tests as some
external program.

-- 
regards Szymon Lipiński

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

[Szymon Lipiński]

On 4 May 2016 at 20:20, Will McCormick  wrote:

> I 100% agree with you. It's always been a problem but it is up to us to
> take ownership and provide value. Some would be surprising shocked how
> simple it is to manage the Data access layer once the framework is in place
> regardless of what it is written in. For the same reasons you wouldn't
> typically have Application Developers configuring your production disks for
> high performance... why would you ever have them access the database
> inefficiently? There is an assumption designers are good at SQL or at least
> know it ... I challenge you to flip that around and learn the Data Access
> Layer.  Companies do not knowingly spend money on hardware to have it
> consumed by inefficient data access? No executive signs up to increase the
> TCO and reduce profit margins when they could be making more money? But
> this is far to often the case and the root cause is they did not have the
> right tool (pun not intended) for the job.
>
> On Wed, May 4, 2016 at 1:33 PM, Szymon Lipiński 
> wrote:
>
>>
>>
>> On 4 May 2016 at 19:09, Will McCormick  wrote:
>>
>>> I agree that it's not like turning on the light switch. And I'm not
>>> implying there isn't a logic layer between the database and the
>>> application. Based off my past experiences I would likely not put business
>>> logic in the database unless it was a critical for performance. This does
>>> not make it portable and does the performance of my product require it? It
>>> really comes down to the application there is not one big paint brush. We
>>> have all be around and get this. I would not likely design a solution that
>>> had the database and the application layers both containing the business
>>> logic. I have seen this and the unexpected behavior as assumptions are made
>>> on both ends of that spectrum. I like to keep it simple where I can. This
>>> all being said I think database minded folks should own DAO's. I think if
>>> your a database guy and you don't own the DAO's you are missing an
>>> opportunity to really make a difference and get more aligned with your
>>> development staff. Doesn't matter what code base DAO's are in it's a
>>> repetitive pattern that any database person can pick up.
>>>
>>> On Wed, May 4, 2016 at 12:29 PM, Szymon Lipiński 
>>> wrote:
>>>


 On 4 May 2016 at 18:14, Will McCormick  wrote:

> I agree it's typically political but so are most things business.
> Examples:  Companies buy other companies - You are using a competitors 
> data
> store and want to replace it.  Company needs to compete with competitors
> and wants to reduce cost ... these are not technical requirements and it's
> certainly not vapor ideology. I have only worked for startups and have 
> seen
> this happen at every company i have worked for, yes it is political but 
> yes
> it happens. Smaller companies are more susceptible to it.
>
> The reality is somewhere in the middle as it often is. My point is you
> don't have to replace a million lines of code if you plan upfront. If you
> don't .. you do.
>
>
> On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:
>
>> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>>
>> A reason to consider may be portability. What happens if I want to
>> let my customer chose their data store or I just don't want to put all my
>> eggs in one basket.Technically there are truths but you cannot ignore the
>> business side either. If a we can exceed our performance requirements and
>> keep things generic/portable this is the best of both worlds.I think this
>> is the main reason people separate the business logic from the database.
>> How many of you have ported databases between platforms? Or had multiple
>> types of data stores in the same company?
>>
>> I have been waiting for the portability argument for the last 20+
>> posts :-) Everyone who did any type of consulting/working in this
>> field knows that latest the second argument from management is
>> “portability”. I have yet to see anyone who really needed to move to a
>> different database. If they did it usually is a political issue and not a
>> technical one (uhh, we hired this new operations manager and MS SQL is so
>> much better than postgresql …) Unless you develop a generic software to 
>> be
>> sold to many clients, the choice of data storage is rarely a real concern
>> unless someone who feels the urge to be important starts throwing words 
>> he
>> read in a magazine at management. None of my clients ever questioned the
>> database. They either had one which they wanted to use (i.e. they had a 
>> big
>> iron IBM with DB2 on it plus a DBA who knew what he was doing) or they
>> didn't 

Re: [GENERAL] Debugging code on server?

[Thomas Kellerer]

Guyren Howe schrieb am 04.05.2016 um 18:43:

This is a fork from my "Love your database" question. It's a
separable concern, so I moved it here.

Let's say I want to use Postgres' *amazing* support for lots of
languages. I want to use Javascript or PERL or Ruby or something. How
do I debug the code?

I can imagine things you might set up: using LISTEN/NOTIFY to a
simple logger that would let you see what's going on. There are
breakpoints and such in PGAdmin, but I'll be that doesn't work for
other languages.



There is a unit test framework for Postgres

http://pgtap.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] Thoughts on "Love Your Database"

[Will McCormick]

I 100% agree with you. It's always been a problem but it is up to us to
take ownership and provide value. Some would be surprising shocked how
simple it is to manage the Data access layer once the framework is in place
regardless of what it is written in. For the same reasons you wouldn't
typically have Application Developers configuring your production disks for
high performance... why would you ever have them access the database
inefficiently? There is an assumption designers are good at SQL or at least
know it ... I challenge you to flip that around and learn the Data Access
Layer.  Companies do not knowingly spend money on hardware to have it
consumed by inefficient data access? No executive signs up to increase the
TCO and reduce profit margins when they could be making more money? But
this is far to often the case and the root cause is they did not have the
right tool (pun not intended) for the job.

On Wed, May 4, 2016 at 1:33 PM, Szymon Lipiński  wrote:

>
>
> On 4 May 2016 at 19:09, Will McCormick  wrote:
>
>> I agree that it's not like turning on the light switch. And I'm not
>> implying there isn't a logic layer between the database and the
>> application. Based off my past experiences I would likely not put business
>> logic in the database unless it was a critical for performance. This does
>> not make it portable and does the performance of my product require it? It
>> really comes down to the application there is not one big paint brush. We
>> have all be around and get this. I would not likely design a solution that
>> had the database and the application layers both containing the business
>> logic. I have seen this and the unexpected behavior as assumptions are made
>> on both ends of that spectrum. I like to keep it simple where I can. This
>> all being said I think database minded folks should own DAO's. I think if
>> your a database guy and you don't own the DAO's you are missing an
>> opportunity to really make a difference and get more aligned with your
>> development staff. Doesn't matter what code base DAO's are in it's a
>> repetitive pattern that any database person can pick up.
>>
>> On Wed, May 4, 2016 at 12:29 PM, Szymon Lipiński 
>> wrote:
>>
>>>
>>>
>>> On 4 May 2016 at 18:14, Will McCormick  wrote:
>>>
 I agree it's typically political but so are most things business.
 Examples:  Companies buy other companies - You are using a competitors data
 store and want to replace it.  Company needs to compete with competitors
 and wants to reduce cost ... these are not technical requirements and it's
 certainly not vapor ideology. I have only worked for startups and have seen
 this happen at every company i have worked for, yes it is political but yes
 it happens. Smaller companies are more susceptible to it.

 The reality is somewhere in the middle as it often is. My point is you
 don't have to replace a million lines of code if you plan upfront. If you
 don't .. you do.


 On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:

> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>
> A reason to consider may be portability. What happens if I want to let
> my customer chose their data store or I just don't want to put all my eggs
> in one basket.Technically there are truths but you cannot ignore the
> business side either. If a we can exceed our performance requirements and
> keep things generic/portable this is the best of both worlds.I think this
> is the main reason people separate the business logic from the database.
> How many of you have ported databases between platforms? Or had multiple
> types of data stores in the same company?
>
> I have been waiting for the portability argument for the last 20+
> posts :-) Everyone who did any type of consulting/working in this
> field knows that latest the second argument from management is
> “portability”. I have yet to see anyone who really needed to move to a
> different database. If they did it usually is a political issue and not a
> technical one (uhh, we hired this new operations manager and MS SQL is so
> much better than postgresql …) Unless you develop a generic software to be
> sold to many clients, the choice of data storage is rarely a real concern
> unless someone who feels the urge to be important starts throwing words he
> read in a magazine at management. None of my clients ever questioned the
> database. They either had one which they wanted to use (i.e. they had a 
> big
> iron IBM with DB2 on it plus a DBA who knew what he was doing) or they
> didn't care as long as it worked as expected and came with a maintenance
> contract of sorts.
>
> If you're developing a web application, the least concern is
> portability (you're not going to 

Re: [GENERAL] Debugging code on server?

[Vick Khera]

Good old fashioned "print" :)

I tend to use RAISE DEBUG, and look in the logs. The most complicated
procedures we have in our system are enforcing state diagram changes for a
given field that tracks an object status, so maybe this doesn't scale well.

On Wed, May 4, 2016 at 12:43 PM, Guyren Howe  wrote:

> This is a fork from my "Love your database" question. It's a separable
> concern, so I moved it here.
>
> Let's say I want to use Postgres' *amazing* support for lots of languages.
> I want to use Javascript or PERL or Ruby or something. How do I debug the
> code?
>
> I can imagine things you might set up: using LISTEN/NOTIFY to a simple
> logger that would let you see what's going on. There are breakpoints and
> such in PGAdmin, but I'll be that doesn't work for other languages.
>
> Any thoughts?
>
> --
> 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"

[Szymon Lipiński]

On 4 May 2016 at 19:09, Will McCormick  wrote:

> I agree that it's not like turning on the light switch. And I'm not
> implying there isn't a logic layer between the database and the
> application. Based off my past experiences I would likely not put business
> logic in the database unless it was a critical for performance. This does
> not make it portable and does the performance of my product require it? It
> really comes down to the application there is not one big paint brush. We
> have all be around and get this. I would not likely design a solution that
> had the database and the application layers both containing the business
> logic. I have seen this and the unexpected behavior as assumptions are made
> on both ends of that spectrum. I like to keep it simple where I can. This
> all being said I think database minded folks should own DAO's. I think if
> your a database guy and you don't own the DAO's you are missing an
> opportunity to really make a difference and get more aligned with your
> development staff. Doesn't matter what code base DAO's are in it's a
> repetitive pattern that any database person can pick up.
>
> On Wed, May 4, 2016 at 12:29 PM, Szymon Lipiński 
> wrote:
>
>>
>>
>> On 4 May 2016 at 18:14, Will McCormick  wrote:
>>
>>> I agree it's typically political but so are most things business.
>>> Examples:  Companies buy other companies - You are using a competitors data
>>> store and want to replace it.  Company needs to compete with competitors
>>> and wants to reduce cost ... these are not technical requirements and it's
>>> certainly not vapor ideology. I have only worked for startups and have seen
>>> this happen at every company i have worked for, yes it is political but yes
>>> it happens. Smaller companies are more susceptible to it.
>>>
>>> The reality is somewhere in the middle as it often is. My point is you
>>> don't have to replace a million lines of code if you plan upfront. If you
>>> don't .. you do.
>>>
>>>
>>> On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:
>>>
 On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:

 A reason to consider may be portability. What happens if I want to let
 my customer chose their data store or I just don't want to put all my eggs
 in one basket.Technically there are truths but you cannot ignore the
 business side either. If a we can exceed our performance requirements and
 keep things generic/portable this is the best of both worlds.I think this
 is the main reason people separate the business logic from the database.
 How many of you have ported databases between platforms? Or had multiple
 types of data stores in the same company?

 I have been waiting for the portability argument for the last 20+ posts
 :-) Everyone who did any type of consulting/working in this field
 knows that latest the second argument from management is “portability”. I
 have yet to see anyone who really needed to move to a different database.
 If they did it usually is a political issue and not a technical one (uhh,
 we hired this new operations manager and MS SQL is so much better than
 postgresql …) Unless you develop a generic software to be sold to many
 clients, the choice of data storage is rarely a real concern unless someone
 who feels the urge to be important starts throwing words he read in a
 magazine at management. None of my clients ever questioned the database.
 They either had one which they wanted to use (i.e. they had a big iron IBM
 with DB2 on it plus a DBA who knew what he was doing) or they didn't care
 as long as it worked as expected and came with a maintenance contract of
 sorts.

 If you're developing a web application, the least concern is
 portability (you're not going to replace the million lines of PHP either).
 The biggest concern should lie with data integrity and API's that protect
 your data and business logic so you can hire that cheap app development
 company from overseas your boss recommended (based on a spam email) without
 running the risk of compromising everything.

 Uwe

>>>
>>>
>> I'm not sure that when a company buys another company they can just
>> migrate the other database without any logic layer. The data is usually
>> useless without the business layer which tells how to merge all the parts
>> together to have a simple answer to a question like "do we have this in
>> stock". And for such a migration that's not too important if we have the
>> logic in database, or in some other layer. Of course it is always simpler
>> to migrate a database treated like a CSV file, where all the logic
>> (including constraints) is in an external application. But do we really
>> want that?
>>
>> On the other hand, when I was trying to store all my logic in a database,
>> there was just one thing that made 

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

[Will McCormick]

I agree that it's not like turning on the light switch. And I'm not
implying there isn't a logic layer between the database and the
application. Based off my past experiences I would likely not put business
logic in the database unless it was a critical for performance. This does
not make it portable and does the performance of my product require it? It
really comes down to the application there is not one big paint brush. We
have all be around and get this. I would not likely design a solution that
had the database and the application layers both containing the business
logic. I have seen this and the unexpected behavior as assumptions are made
on both ends of that spectrum. I like to keep it simple where I can. This
all being said I think database minded folks should own DAO's. I think if
your a database guy and you don't own the DAO's you are missing an
opportunity to really make a difference and get more aligned with your
development staff. Doesn't matter what code base DAO's are in it's a
repetitive pattern that any database person can pick up.

On Wed, May 4, 2016 at 12:29 PM, Szymon Lipiński  wrote:

>
>
> On 4 May 2016 at 18:14, Will McCormick  wrote:
>
>> I agree it's typically political but so are most things business.
>> Examples:  Companies buy other companies - You are using a competitors data
>> store and want to replace it.  Company needs to compete with competitors
>> and wants to reduce cost ... these are not technical requirements and it's
>> certainly not vapor ideology. I have only worked for startups and have seen
>> this happen at every company i have worked for, yes it is political but yes
>> it happens. Smaller companies are more susceptible to it.
>>
>> The reality is somewhere in the middle as it often is. My point is you
>> don't have to replace a million lines of code if you plan upfront. If you
>> don't .. you do.
>>
>>
>> On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:
>>
>>> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>>>
>>> A reason to consider may be portability. What happens if I want to let
>>> my customer chose their data store or I just don't want to put all my eggs
>>> in one basket.Technically there are truths but you cannot ignore the
>>> business side either. If a we can exceed our performance requirements and
>>> keep things generic/portable this is the best of both worlds.I think this
>>> is the main reason people separate the business logic from the database.
>>> How many of you have ported databases between platforms? Or had multiple
>>> types of data stores in the same company?
>>>
>>> I have been waiting for the portability argument for the last 20+ posts
>>> :-) Everyone who did any type of consulting/working in this field knows
>>> that latest the second argument from management is “portability”. I have
>>> yet to see anyone who really needed to move to a different database. If
>>> they did it usually is a political issue and not a technical one (uhh, we
>>> hired this new operations manager and MS SQL is so much better than
>>> postgresql …) Unless you develop a generic software to be sold to many
>>> clients, the choice of data storage is rarely a real concern unless someone
>>> who feels the urge to be important starts throwing words he read in a
>>> magazine at management. None of my clients ever questioned the database.
>>> They either had one which they wanted to use (i.e. they had a big iron IBM
>>> with DB2 on it plus a DBA who knew what he was doing) or they didn't care
>>> as long as it worked as expected and came with a maintenance contract of
>>> sorts.
>>>
>>> If you're developing a web application, the least concern is portability
>>> (you're not going to replace the million lines of PHP either). The biggest
>>> concern should lie with data integrity and API's that protect your data and
>>> business logic so you can hire that cheap app development company from
>>> overseas your boss recommended (based on a spam email) without running the
>>> risk of compromising everything.
>>>
>>> Uwe
>>>
>>
>>
> I'm not sure that when a company buys another company they can just
> migrate the other database without any logic layer. The data is usually
> useless without the business layer which tells how to merge all the parts
> together to have a simple answer to a question like "do we have this in
> stock". And for such a migration that's not too important if we have the
> logic in database, or in some other layer. Of course it is always simpler
> to migrate a database treated like a CSV file, where all the logic
> (including constraints) is in an external application. But do we really
> want that?
>
> On the other hand, when I was trying to store all my logic in a database,
> there was just one thing that made me hate it. Testing. Testing the
> procedures inside the database was not easy, not funny, and too much time
> consuming.
>
> --
> regards Szymon Lipiński
>

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

[Paul Jungwirth]

On 05/04/2016 08:39 AM, Paul Jungwirth wrote:

On 05/03/2016 09:11 PM, Guyren Howe wrote:

I think I'm going to write a book called Love Your Database, aimed at
web developers


I gave a talk here about doing "interesting" Postgres things in Rails:


Oh also: one part of my talk I did like what giving my mental process 
for building up a query. Because of SQL's declarative nature, a lot of 
people just don't know where to start. My own thinking goes like this:


1. Each output row is a _.
2. Use that for the `FROM`.

From there, it is easy to JOIN to whatever else I need, add filters, 
and fill in the SELECT.


That's worked really well for me. I'd love to hear what other people do, 
if you've done any reflection on your own thought process.


I think teaching this is a different thing than just teaching SQL 
syntax. I haven't seen it written about a lot. It must be somewhere, but 
any book encouraging "outsiders" to use more SQL could benefit from 
giving them direction like that.


Paul




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

[GENERAL] Debugging code on server?

[Guyren Howe]

This is a fork from my "Love your database" question. It's a separable concern, 
so I moved it here.

Let's say I want to use Postgres' *amazing* support for lots of languages. I 
want to use Javascript or PERL or Ruby or something. How do I debug the code?

I can imagine things you might set up: using LISTEN/NOTIFY to a simple logger 
that would let you see what's going on. There are breakpoints and such in 
PGAdmin, but I'll be that doesn't work for other languages.

Any thoughts?

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

[Szymon Lipiński]

On 4 May 2016 at 18:14, Will McCormick  wrote:

> I agree it's typically political but so are most things business.
> Examples:  Companies buy other companies - You are using a competitors data
> store and want to replace it.  Company needs to compete with competitors
> and wants to reduce cost ... these are not technical requirements and it's
> certainly not vapor ideology. I have only worked for startups and have seen
> this happen at every company i have worked for, yes it is political but yes
> it happens. Smaller companies are more susceptible to it.
>
> The reality is somewhere in the middle as it often is. My point is you
> don't have to replace a million lines of code if you plan upfront. If you
> don't .. you do.
>
>
> On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:
>
>> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>>
>> A reason to consider may be portability. What happens if I want to let my
>> customer chose their data store or I just don't want to put all my eggs in
>> one basket.Technically there are truths but you cannot ignore the business
>> side either. If a we can exceed our performance requirements and keep
>> things generic/portable this is the best of both worlds.I think this is the
>> main reason people separate the business logic from the database. How many
>> of you have ported databases between platforms? Or had multiple types of
>> data stores in the same company?
>>
>> I have been waiting for the portability argument for the last 20+ posts
>> :-) Everyone who did any type of consulting/working in this field knows
>> that latest the second argument from management is “portability”. I have
>> yet to see anyone who really needed to move to a different database. If
>> they did it usually is a political issue and not a technical one (uhh, we
>> hired this new operations manager and MS SQL is so much better than
>> postgresql …) Unless you develop a generic software to be sold to many
>> clients, the choice of data storage is rarely a real concern unless someone
>> who feels the urge to be important starts throwing words he read in a
>> magazine at management. None of my clients ever questioned the database.
>> They either had one which they wanted to use (i.e. they had a big iron IBM
>> with DB2 on it plus a DBA who knew what he was doing) or they didn't care
>> as long as it worked as expected and came with a maintenance contract of
>> sorts.
>>
>> If you're developing a web application, the least concern is portability
>> (you're not going to replace the million lines of PHP either). The biggest
>> concern should lie with data integrity and API's that protect your data and
>> business logic so you can hire that cheap app development company from
>> overseas your boss recommended (based on a spam email) without running the
>> risk of compromising everything.
>>
>> Uwe
>>
>
>
I'm not sure that when a company buys another company they can just migrate
the other database without any logic layer. The data is usually useless
without the business layer which tells how to merge all the parts together
to have a simple answer to a question like "do we have this in stock". And
for such a migration that's not too important if we have the logic in
database, or in some other layer. Of course it is always simpler to migrate
a database treated like a CSV file, where all the logic (including
constraints) is in an external application. But do we really want that?

On the other hand, when I was trying to store all my logic in a database,
there was just one thing that made me hate it. Testing. Testing the
procedures inside the database was not easy, not funny, and too much time
consuming.

-- 
regards Szymon Lipiński

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

[Geoff Winkless]

On 4 May 2016 at 17:14, Will McCormick  wrote:
> Examples:  Companies buy other companies - You are using a competitors data 
> store and want to replace it.  Company needs to compete with competitors and 
> wants to reduce cost ...

Or, let's say, massive multibillion-dollar DBMS competitor buys
smaller but very popular DBMS and steadily increases license costs to
try to price out that database and force its customers to its own,
extortionately expensive, database.

Completely hypothetically, of course.

> The reality is somewhere in the middle as it often is. My point is you don't 
> have to replace a million lines of code if you plan upfront. If you don't .. 
> you do.

Chances are you do anyway. Since no engine is perfect, at some point
you will (assuming it's a non-trivial store) have had to have made
modifications to the data structures or the queries you run to
optimise to a particular DBMS.

But you're right, if you've started out well, it will at least
minimise the amount of change.

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"

[Will McCormick]

I agree it's typically political but so are most things business. Examples:
 Companies buy other companies - You are using a competitors data store and
want to replace it.  Company needs to compete with competitors and wants to
reduce cost ... these are not technical requirements and it's certainly not
vapor ideology. I have only worked for startups and have seen this happen
at every company i have worked for, yes it is political but yes it happens.
Smaller companies are more susceptible to it.

The reality is somewhere in the middle as it often is. My point is you
don't have to replace a million lines of code if you plan upfront. If you
don't .. you do.


On Wed, May 4, 2016 at 11:29 AM, Uwe Schroeder  wrote:

> On Wed, May 04, 2016 11:05:25 AM Will McCormick wrote:
>
> A reason to consider may be portability. What happens if I want to let my
> customer chose their data store or I just don't want to put all my eggs in
> one basket.Technically there are truths but you cannot ignore the business
> side either. If a we can exceed our performance requirements and keep
> things generic/portable this is the best of both worlds.I think this is the
> main reason people separate the business logic from the database. How many
> of you have ported databases between platforms? Or had multiple types of
> data stores in the same company?
>
> I have been waiting for the portability argument for the last 20+ posts
> :-) Everyone who did any type of consulting/working in this field knows
> that latest the second argument from management is “portability”. I have
> yet to see anyone who really needed to move to a different database. If
> they did it usually is a political issue and not a technical one (uhh, we
> hired this new operations manager and MS SQL is so much better than
> postgresql …) Unless you develop a generic software to be sold to many
> clients, the choice of data storage is rarely a real concern unless someone
> who feels the urge to be important starts throwing words he read in a
> magazine at management. None of my clients ever questioned the database.
> They either had one which they wanted to use (i.e. they had a big iron IBM
> with DB2 on it plus a DBA who knew what he was doing) or they didn't care
> as long as it worked as expected and came with a maintenance contract of
> sorts.
>
> If you're developing a web application, the least concern is portability
> (you're not going to replace the million lines of PHP either). The biggest
> concern should lie with data integrity and API's that protect your data and
> business logic so you can hire that cheap app development company from
> overseas your boss recommended (based on a spam email) without running the
> risk of compromising everything.
>
> Uwe
>

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

[Alban Hertroys]

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.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


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

[Paul Jungwirth]

On 05/03/2016 09:11 PM, Guyren Howe wrote:

I think I'm going to write a book called Love Your Database, aimed at web 
developers

What might I cover that I haven't mentioned? What are the usual objections to 
server-side code and how can they be met? When *are* they justified and what 
should the criteria be to put code in Postgres? Any other thoughts? Any other 
websites or books on the topic I might consult?


I gave a talk here about doing "interesting" Postgres things in Rails:

https://github.com/pjungwir/rails-and-sql-talk

I don't think that will satisfy many people on this list advocating for 
stored procedures, but it is an example of trying to teach what SQL can 
do, and how you can do those things without losing your convenient and 
familiar ORM tools, e.g. running a query and getting back a bunch of 
Ruby objects.


I was not really happy with the talk to be honest. I felt it lacked 
unity, it didn't fit in the time I had, and it was too split between 
"for beginners" and advanced stuff. It was just snippets---in other 
words CTEs and window functions :-). I would like to see something that 
offers more strategic advice. Give me a plan.


I would be very interested in that book. I think the biggest challenge 
will be identifying with your audience: knowing their priorities and 
concerns and workflows. My take on the last 25 years of software 
architecture theory is "how to put a layer in front of my database." I 
think most people who want business logic in the database are dismissive 
of this work and too derogatory toward typical web developers, so I 
would be pleased to see a book that takes that history more seriously. 
You aren't going to convince the world to trade Rails for PLSQL. But are 
there ways I can use SQL (and PLSQL) without giving up Rails? How will I 
maintain that stuff? Does it play nice with Rails database migrations? 
How will I write tests for it? How do I debug it? What principles will 
help me draw the line between (PL)SQL and Ruby?


Good luck! I think a book like that would be great.

Paul







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

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

[John McKown]

On Wed, May 4, 2016 at 9:55 AM, Pierre Chevalier Géologue <
pierrechevalierg...@free.fr> wrote:

> Le 04/05/2016 15:25, John McKown a écrit :
>
>> On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless > >wrote:
>>
>> ​
>>
>> The sensible way is to do it as John wrote - to restrict access rights
>> to everyone except admin to calling functions only. That way the
>> functions are written by the people who are paid to understand the
>> business rules and the data behind it, and the application developers
>> can ask those experts to do the heavy lifting for them. Having to
>> persuade management that they should no longer be able to connect the
>> database to MS Access and make changes that way will usually put an
>> end to that pure model, though. :)
>>
>>
>> ​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).



>
>
> They become like some users who "know Excel". They are now just as
>> knowledgeable as someone who's been doing this for years. I've actually
>> heard one say something akin to: "Damn it, I can write Excel formulas. I
>> know very well that an new function on the web site could be written in
>> less than a day, if you'd just get off you a$$ and do it."
>>
>
> 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. 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.



>
> À+
> Pierre



-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

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

[Steve Crawford]

On Wed, May 4, 2016 at 8:04 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> First, you hit them over the head with a copy of "SQL Antipatterns:
> Avoiding the Pitfalls of Database Programming". It is a tad out of date and
> tends to use PHP and MySQL for the main examples but does also address
> different solutions available in PostgreSQL, Oracle. MS SQL server, etc.
> while pointing out the risks of various common foot-guns and providing
> alternatives.
>
> Or point them to this recent Linux Journal article by Reuven Lerner (who
> is occasionally seen on these lists):
> http://www.linuxjournal.com/content/use-your-database
>
> Developers often have a pre-Gallileo world view that they and whatever app
> they are coding is the center of the universe and databases, networks,
> storage and the rest all revolve around them existing only to support their
> app.
>
> But ultimately the church of the developer gets forced into the modern era
> and finds that the data is at the center and the apps that allow input,
> maintenance, extraction and analysis all revolve around those core crown
> jewels. Then, *gasp*, there are other people and apps touching "your" data.
> Are they all validating the data the way you do? Protecting it? Retrieving
> it efficiently? Only then does the real value of the database come into
> focus.
>
> Cheers,
> Steve
>
>
>
>
>
> On Tue, May 3, 2016 at 9:11 PM, Guyren Howe  wrote:
>
>> I've long been frustrated with how most web developers I meet have no
>> idea how to use an SQL database properly. I think I'm going to write a book
>> called Love Your Database, aimed at web developers, that explains how to
>> make their apps better by leveraging the power of SQL in general, and
>> Postgres in particular.
>>
>> I'm thinking of a section on features of SQL most folks don't know about
>> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
>> and window functions), but much of the book would be about how to do things
>> server side. Benchmarks showing how much faster this can be, but mostly
>> techniques — stored procedures/triggers/rules, views.
>>
>> I asked a colleague about the advice I often hear stated but seldom
>> justified, that one shouldn't put business rules in the database. He
>> offered that server-side code can be hard to debug.
>>
>> I'm sure many here would love to see such a book published, maybe some
>> talks on the topic given.
>>
>>
>> What might I cover that I haven't mentioned? What are the usual
>> objections to server-side code and how can they be met? When *are* they
>> justified and what should the criteria be to put code in Postgres? Any
>> other thoughts? Any other websites or books on the topic I might consult?
>>
>> TIA
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
(Apologies for the top-posts - forgot to override the GMail defaults...)

-Steve

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

[Jeff Janes]

On Wed, May 4, 2016 at 1:39 AM, Maxim Boguk  wrote:

>> This sounds like a known issue, fixed in 9.6, where the pages used for
>> the pending list do not eligible for recycling until the table is
>> vacuumed.  Autovacuum does not run on insert only tables, so they just
>> accumulate as empty pages in the index.
>>
>> Cheers,
>>
>> Jeff
>
>
>
> Hi Jeff,
>
> Yes it's look like a cause.
> Is there any workaround possible for 9.5.2?
> At this moment I see palliative (because it doesn't reduce already bloated
> index) cure via performing manual vacuum on the table after each batch
> insert
> or very slow (for the large table) full scale fix via create new index/drop
> old index.

A full workaround would be turn off "fastupdate" on the index.

In my workloads, doing that doesn't even slow inserts down by a
meaningful amount, so now I just habitually turn it off.

If you can't do that, you could run manual vacuum repeatedly during
the bulk load so the pages can be recycled within the same batch,
although that might slow things down more than just turning fastupdate
off does.

Or, you could just live with the bloat.  It is 15x when starting from
an empty table.  But if you are doing repeated batch inserts which
don't each start with an empty table, it won't be nearly as bad (as
long as you vacuum in between) on a ratio basis.  (And if you do
always start out with an empty table, you should build the index at
the end, not the beginning, of the inserts)

Cheers,

Jeff


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

[Steve Crawford]

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

Cheers,
Steve

On Wed, May 4, 2016 at 7:55 AM, Pierre Chevalier Géologue <
pierrechevalierg...@free.fr> wrote:

> Le 04/05/2016 15:25, John McKown a écrit :
>
>> On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless > >wrote:
>>
>> ​
>>
>> The sensible way is to do it as John wrote - to restrict access rights
>> to everyone except admin to calling functions only. That way the
>> functions are written by the people who are paid to understand the
>> business rules and the data behind it, and the application developers
>> can ask those experts to do the heavy lifting for them. Having to
>> persuade management that they should no longer be able to connect the
>> database to MS Access and make changes that way will usually put an
>> end to that pure model, though. :)
>>
>>
>> ​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.
>
>
> They become like some users who "know Excel". They are now just as
>> knowledgeable as someone who's been doing this for years. I've actually
>> heard one say something akin to: "Damn it, I can write Excel formulas. I
>> know very well that an new function on the web site could be written in
>> less than a day, if you'd just get off you a$$ and do it."
>>
>
> 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.
>
> À+
> 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"

[Will McCormick]

A reason to consider may be portability. What happens if I want to let my
customer chose their data store or I just don't want to put all my eggs in
one basket.Technically there are truths but you cannot ignore the business
side either. If a we can exceed our performance requirements and keep
things generic/portable this is the best of both worlds.I think this is the
main reason people separate the business logic from the database. How many
of you have ported databases between platforms? Or had multiple types of
data stores in the same company?

On Wed, May 4, 2016 at 12:11 AM, Guyren Howe  wrote:

> I've long been frustrated with how most web developers I meet have no idea
> how to use an SQL database properly. I think I'm going to write a book
> called Love Your Database, aimed at web developers, that explains how to
> make their apps better by leveraging the power of SQL in general, and
> Postgres in particular.
>
> I'm thinking of a section on features of SQL most folks don't know about
> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
> and window functions), but much of the book would be about how to do things
> server side. Benchmarks showing how much faster this can be, but mostly
> techniques — stored procedures/triggers/rules, views.
>
> I asked a colleague about the advice I often hear stated but seldom
> justified, that one shouldn't put business rules in the database. He
> offered that server-side code can be hard to debug.
>
> I'm sure many here would love to see such a book published, maybe some
> talks on the topic given.
>
>
> What might I cover that I haven't mentioned? What are the usual objections
> to server-side code and how can they be met? When *are* they justified and
> what should the criteria be to put code in Postgres? Any other thoughts?
> Any other websites or books on the topic I might consult?
>
> TIA
>
> --
> 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"

[Steve Crawford]

First, you hit them over the head with a copy of "SQL Antipatterns:
Avoiding the Pitfalls of Database Programming". It is a tad out of date and
tends to use PHP and MySQL for the main examples but does also address
different solutions available in PostgreSQL, Oracle. MS SQL server, etc.
while pointing out the risks of various common foot-guns and providing
alternatives.

Or point them to this recent Linux Journal article by Reuven Lerner (who is
occasionally seen on these lists):
http://www.linuxjournal.com/content/use-your-database

Developers often have a pre-Gallileo world view that they and whatever app
they are coding is the center of the universe and databases, networks,
storage and the rest all revolve around them existing only to support their
app.

But ultimately the church of the developer gets forced into the modern era
and finds that the data is at the center and the apps that allow input,
maintenance, extraction and analysis all revolve around those core crown
jewels. Then, *gasp*, there are other people and apps touching "your" data.
Are they all validating the data the way you do? Protecting it? Retrieving
it efficiently? Only then does the real value of the database come into
focus.

Cheers,
Steve





On Tue, May 3, 2016 at 9:11 PM, Guyren Howe  wrote:

> I've long been frustrated with how most web developers I meet have no idea
> how to use an SQL database properly. I think I'm going to write a book
> called Love Your Database, aimed at web developers, that explains how to
> make their apps better by leveraging the power of SQL in general, and
> Postgres in particular.
>
> I'm thinking of a section on features of SQL most folks don't know about
> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
> and window functions), but much of the book would be about how to do things
> server side. Benchmarks showing how much faster this can be, but mostly
> techniques — stored procedures/triggers/rules, views.
>
> I asked a colleague about the advice I often hear stated but seldom
> justified, that one shouldn't put business rules in the database. He
> offered that server-side code can be hard to debug.
>
> I'm sure many here would love to see such a book published, maybe some
> talks on the topic given.
>
>
> What might I cover that I haven't mentioned? What are the usual objections
> to server-side code and how can they be met? When *are* they justified and
> what should the criteria be to put code in Postgres? Any other thoughts?
> Any other websites or books on the topic I might consult?
>
> TIA
>
> --
> 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"

[Pierre Chevalier Géologue]

Le 04/05/2016 15:25, John McKown a écrit :

On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless >wrote:

​

The sensible way is to do it as John wrote - to restrict access rights
to everyone except admin to calling functions only. That way the
functions are written by the people who are paid to understand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that pure model, though. :)


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



They become like some users who "know Excel". They are now just as
knowledgeable as someone who's been doing this for years. I've actually
heard one say something akin to: "Damn it, I can write Excel formulas. I
know very well that an new function on the web site could be written in
less than a day, if you'd just get off you a$$ and do it."


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.


À+
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"

[Pierre Chevalier Géologue]

Le 04/05/2016 13:36, dandl a écrit :
...

Then I think you've seriously misunderstood. Most people can
indeed learn to write basic SQL queries, but those are
(obviously) not what I'm talking about.

To write the business logic of a significant application
entirely in SQL requires PLSQL (or in other dialects, whatever
passes for SQL/PSM). It means writing an entire data access
layer as a set of stored procedures, with a substantial set of
special functions, types, triggers and so on. No beginner and
few experts have the skills required to do that in SQL, and then
debug that code on the server.


All right, I understand better now.  I think I also totally missed your 
point, sorry...

I'll give a look at andl.

À+
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"

[Pierre Chevalier Géologue]

Hi,

Le 04/05/2016 13:36, Szymon Lipiński a écrit :

On 4 May 2016 at 13:13, Chris Travers > wrote:
A few observations

On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless > wrote:

On 4 May 2016 at 06:46, dandl > wrote:
> I'm a strong believer in putting the business code next to the data, 
not the wrong
> side of the object-relational divide. However, for many the challenge 
of writing and
> debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug.


Yes, I agree. SQL is just crystal-clear to write, read and understand. I 
found out that debugging is usually not a common exercise in SQL, 
because the language is so trivial.



...

 From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually
the management said "no, that's not doable, as we will have trouble with
finding good sql programmers later",


Shocking! Apart from very few languages I know, SQL is by far more 
productive and efficient, for many-many tasks.




and we were still writing all the logic outside the database.


I used to implement the logic outside the database, like you mention, 
*but* I was writing plain SQL.  Only when I had specific needs, then I 
would switch to another language which would just get the results from a 
well-polished plain SQL query, process, and feed back things into the 
database (with another well-polished SQL, of course) or just throw the 
results out somewhere else (file, screen, picture, whatever).  No ORM or 
any complication.


And I find SQL fairly easy to debug and maintain, no need for fancy 
tools: an editor and a console (psql or equivalent) and you're up and going!



Nowadays, things got quite different, and I tend to stuff more and more 
logic inside the database. Which is often merely converting SQL queries 
into views...


But it comes with a counterpart: the more you put logic inside your 
DBMS, the more dependent you become. As far as I'm concerned, I recently 
decided to just stick to PostgreSQL forever! (or almost)


À+
Pierre

PS: sorry for the double-reply, Szymon: I forgot *again* to hit 
Shift-Ctrl-R instead of Ctrl-R, shame on me...

--

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] ruby pg connection fails on centos - okay on debian and dev machine

[Adrian Klaver]

On 05/04/2016 05:26 AM, john.tiger wrote:

On 05/03/2016 06:18 PM, Adrian Klaver wrote:

On 05/03/2016 05:07 PM, john.tiger wrote:

our model.rb runs fine on a dev machine and a debian server  but is
failing on a new centos server - checked the postgres db name and user
name and password - all seem fine


The error message is?



could it be:

host => "localhost"

or maybe CORS ?


CORS as in:

https://en.wikipedia.org/wiki/Cross-origin_resource_sharing

or something else?

Have you tried connecting using psql with same parameters?

Have you looked at the pg_hba.conf files on the different platforms
and see if they differ?


yeah, but we couldn't find any log file - not sure on centos where this
should be  - nothing in /var/log or /var/lib/pgsql/9.5/data


I do not use Centos so I am not sure where the logs go. You might get a 
hint by logging in using psql and doing:


show log_directory ;



running psql dbname="ourdb" works
running psql dbname="ourdb" username="ouruser"  ==> FATAL peer


That is failing because peer authentication is set up for connecting via 
local(per pg_hba.conf below), where local is a socket connection. For 
more information see:


http://www.postgresql.org/docs/9.5/interactive/auth-methods.html#AUTH-PEER

Retry the above with -h localhost or -h 127.0.0.1


authentication failed for user"ouruser"

running \l  => shows db
   \du =>   ouruser {}  ? doesn't show login although we specified that
as when created role - could that be the problem ?? then how to add
login to user   alter role ouruser with login  => still shows {}


That is normal. If you could not login you would see something like;

test=# \du test_role
List of roles
 Role name |  Attributes  | Member of
---+--+---
 test_role | Cannot login | {}

FYI, {} is for role membership, so something like:

test=# \du aklaver
  List of roles
 Role name | Attributes |Member of
---++-
 aklaver   || {app_admin,enhanced,production}




both pg_hba show:
localallall  peer
host allall  127.0.0.1
host all   all ident


Is that all?

In particular is that the full line for the second line above?

Are there any IPv6 address?




re cors - all on same linode server with postgres running on std port so
don't think should be an issue and is not issue on debian server - just
searching for possible answer

here is our model
@conn = PG.connect(
  :host => "localhost",
  :dbname => "ourdb",
  :user  => "ouruser",
  :password => "ourpassword"
)
again, this runs fine on debian and debian dev machine


Is there a chance there is more then one instance of Postgres running on 
this machine?






















--
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] Do parallel queries work with only dblink not with fdw?

[Marc Mamin]

>From: pgsql-general-ow...@postgresql.org 
>[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Klaus P.
>Sent: Mittwoch, 4. Mai 2016 10:28
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Do parallel queries work with only dblink not with fdw?
>
>In order to improve cpu and disk utilization, I am testing parallel queries. 
>
>The approach with dblink_send_query() and dblink_get_result() works in my 
>proof-of-concept. Runtime of my reference query was reduced from 55 seconds to 
>~20seconds using 4 parallel connections. Not what I had hoped but certainly a 
>significant improvement.
>My approach is fairly easy: 
>db_link_send_query('conn1', 'statement based on partitioning field');
>db_link_send_query('conn2', 'statement based on partitioning field');
>
>...
>SELECT 
>dblink_get_result('conn1')
>UNION ALL 
>dblink_get_result('conn2')
>
>...


Hello,

I'm using the db_link approach too, but rather than using dblink_get_result, I 
store the result in an additional table.
This was faster, at least for my use case and at the time when I implemented 
that solution...

something like 

db_link_send_query('conn0', 'CREATE UNLOGGED TABLE  my_result...
db_link_send_query('conn1', 'INSERT INTO my_result  statement based on 
partitioning field');
db_link_send_query('conn2', 'INSERT INTO my_result  statement based on 
partitioning field');
...
select * from my_result

regards,

Marc Mamin

-- 
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] Do parallel queries work with only dblink not with fdw?

[kpi6288]

> -Ursprüngliche Nachricht-
> Von: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> 
> I am not sure of the status of parallel query in FDW, but for those that are 
> some
> more information would be helpful:
> 
> 1) You are using postgres_fdw, correct?

Yes.

> 
> 2) What version of the FDW are you using?

Not sure if FDW could be different from the cluster version (see below). 
Postgres_fdw.dll shows file version 9.5.2.16088 - this is the one installed 
with the Windows PostgreSQL package.

> 
> 3) What version(s) of Postgres are you connecting from/to?

For testing I have installed a fresh download 9.5.2 Windows x64 (on Windows 
Server 2008 R2, if this is of interest)

There is only one test database running in this cluster. I am connecting in a 
"loopback" mode to that database. 

I could repeat my tests with two different databases or run it on a Linux / 
Debian machine if that makes a difference. 

Thanks Klaus






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

[Melvin Davidson]

On Wed, May 4, 2016 at 9:25 AM, John McKown 
wrote:

> On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless 
> wrote:
>
>> ​
>>
>> The sensible way is to do it as John wrote - to restrict access rights
>> to everyone except admin to calling functions only. That way the
>> functions are written by the people who are paid to understand the
>> business rules and the data behind it, and the application developers
>> can ask those experts to do the heavy lifting for them. Having to
>> persuade management that they should no longer be able to connect the
>> database to MS Access and make changes that way will usually put an
>> end to that pure model, though. :)
>>
>
> ​Allowing PHBs direct access t​o company data is a nasty thing. They
> become like some users who "know Excel". They are now just as knowledgeable
> as someone who's been doing this for years. I've actually heard one say
> something akin to: "Damn it, I can write Excel formulas. I know very well
> that an new function on the web site could be written in less than a day,
> if you'd just get off you a$$ and do it."
>
>
>
>>
>> Geoff
>>
>>
>>
>
>
> --
> The unfacts, did we have them, are too imprecisely few to warrant our
> certitude.
>
> Maranatha! <><
> John McKown
>

>What might I cover that I haven't mentioned?

Well, I'm pretty sure that one of the reasons Web Developers do not use SQL
is because they do not know what is in the database. Perhaps a sections
that teaches them how to list the tables and columns
from the INFORMATION_SCHEMA would be a good start.


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

Re: [GENERAL] Do parallel queries work with only dblink not with fdw?

[Adrian Klaver]

On 05/04/2016 01:28 AM, Klaus P. wrote:

In order to improve cpu and disk utilization, I am testing parallel
queries.

The approach with dblink_send_query() and dblink_get_result() works in
my proof-of-concept. Runtime of my reference query was reduced from 55
seconds to ~20seconds using 4 parallel connections. Not what I had hoped
but certainly a significant improvement.

My approach is fairly easy:

db_link_send_query('conn1', 'statement based on partitioning field');
db_link_send_query('conn2', 'statement based on partitioning field');

...

SELECT
dblink_get_result('conn1')
UNION ALL
dblink_get_result('conn2')

...


However, using fdw foreign data wrappers, I was not able to run any
query in parallel. I came across this presentation
http://de.slideshare.net/EnterpriseDB/foreign-data-wrappers-and-you-with-postgres
where it says on page 12  "parallel FDW access". Is there any example
available on how to run FDW queries in parallel?

My approach with FDW is similar to the dblink example above:

SELECT * FROM myFdwTable WHERE (clause based on partitioning field)
UNION ALL
SELECT * FROM myFdwTable WHERE (clause based on partitioning field)
...

My experience is however that the statements are carried out sequentially.


I am not sure of the status of parallel query in FDW, but for those that 
are some more information would be helpful:


1) You are using postgres_fdw, correct?

2) What version of the FDW are you using?

3) What version(s) of Postgres are you connecting from/to?




Thanks

Klaus Pieper



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

[John McKown]

On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless  wrote:

> ​
>
> The sensible way is to do it as John wrote - to restrict access rights
> to everyone except admin to calling functions only. That way the
> functions are written by the people who are paid to understand the
> business rules and the data behind it, and the application developers
> can ask those experts to do the heavy lifting for them. Having to
> persuade management that they should no longer be able to connect the
> database to MS Access and make changes that way will usually put an
> end to that pure model, though. :)
>

​Allowing PHBs direct access t​o company data is a nasty thing. They become
like some users who "know Excel". They are now just as knowledgeable as
someone who's been doing this for years. I've actually heard one say
something akin to: "Damn it, I can write Excel formulas. I know very well
that an new function on the web site could be written in less than a day,
if you'd just get off you a$$ and do it."



>
> Geoff
>
>
>


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

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

[Sándor Daku]

On 4 May 2016 at 13:36, Szymon Lipiński  wrote:

>
>
> On 4 May 2016 at 13:13, Chris Travers  wrote:
>
>> A few observations
>>
>> On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless 
>> wrote:
>>
>>> On 4 May 2016 at 06:46, dandl  wrote:
>>> > I'm a strong believer in putting the business code next to the data,
>>> not the wrong
>>> > side of the object-relational divide. However, for many the challenge
>>> of writing and
>>> > debugging SQL code is just too high!
>>>
>>> Your source for this statement please? "For many" sounds rather like
>>> weasel-words to me. In my experience, a wide range of people, from
>>> beginners to experts, find SQL easy to write and debug. I'm afraid
>>> that the problem seems to me to be that your peg is rather too square.
>>>
>>>
>> I actually agree with dandl on this.  Folks can write SQL but often
>> aren't really comfortable using it as core application logic.
>>
>> I.e. one often sees code that retrieves a bunch of records from the db,
>> loops through them, and transforms the data as part of the OLTP workflow.
>> It is obviously much better of one can think about SQL as business logic
>> but this is not that often.
>>
>> I.e. people think the peg is square but indeed it is round.
>>
>>
>>
> From my perspective there is one more thing: when I tried, in couple of
> companies, to move some part of the logic to a database, then usually the
> management said "no, that's not doable, as we will have trouble with
> finding good sql programmers later", and we were still writing all the
> logic outside the database.
>
>
Yeah. The classic "We have a bunch of scissors, so use them instead of
screwdrivers. Oh, and by the way, please redesign our screws, to make them
more scissor compatible." approach. :)
The real shame, when they buy an expensive, feature-rich DBMS, run it on a
kick ass hardware and then using it as a glorified file cabinet.

Regards,
Sándor

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

[John McKown]

On Wed, May 4, 2016 at 7:55 AM, Szymon Lipiński  wrote:
​​


> Hi,
> at my previous jobs I was working with many programmers, and almost none
> of them understood SQL. The problem was even deeper. They didn't want to
> learn it. When I was working among java programmers, I noticed that they
> hated SQL, but there was no problem with learning HQL, which in fact is
> quite similar. I really have no idea why it is like that.
>
> I was thinking about such a book too, I have even started writing that,
> but the whole set of ideas was always either too large or was a copy of
> what can be found in other, general programming books. And if programmers
> don't want to read the other books, and learn basics of SQL from there,
> then the whole idea seemed useless to me. Of course the other reason of not
> writing the book was lack of time, as writing a good book requires enormous
> amount of work.
>
> On the other hand I think that the huge problem with programmers and SQL
> is changing the mindset. A standard programmer usually has a problem with
> thinking in sets. Instead she usually thinks in terms of loops, and objects.
>

​Very true. What _really_ was of help to me was the fact that I had learned
APL in college. APL is an vector / array oriented language with operators
which work on entire data structures, rather than individual array
elements. E.g. to sum all the number in a vector is simply "+/vector".


>
> When I was giving talks about what not to do in databases, people were
> either not interested or everything was a surprise for them, even for quite
> experienced programmers. However after such a talk, or a training, people
> were still not interested in knowing more, but they were happily learning
> about programming.
>
> I think it would be great to have a book like that, and I think it should
> not be only about Postgres. But here is the problem I had with this
> concept: to describe all the things to a normal programmer, assuming she
> will be interested, it will need to be quite a huge and complicated book.
>
> Or maybe this book should be about something else, start from an ORM, and
> show how to translate it to much better SQL, as ORMs are the things
> programmers usually understand, and they really don't bother that using
> them can be a bad idea.
>
>
> --
> regards Szymon Lipiński
>

​What I have heard of are shops in which the application programmers simply
DO NOT CODE any SQL. They only use stored procedures which they view as
subroutine calls. The stored procedures are written by the DBAs and other
SQL experts. That group has hard control over the enterprise data. If the
programmers need something, they become like end-user in that they simply
describe what data they need and why. The programmers basically know enough
to be able to ask for something. Completely unlike back in the days of
indexed (DBM basically) files​.


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

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

[Szymon Lipiński]

On 4 May 2016 at 06:11, Guyren Howe  wrote:

> I've long been frustrated with how most web developers I meet have no idea
> how to use an SQL database properly. I think I'm going to write a book
> called Love Your Database, aimed at web developers, that explains how to
> make their apps better by leveraging the power of SQL in general, and
> Postgres in particular.
>
> I'm thinking of a section on features of SQL most folks don't know about
> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
> and window functions), but much of the book would be about how to do things
> server side. Benchmarks showing how much faster this can be, but mostly
> techniques — stored procedures/triggers/rules, views.
>
> I asked a colleague about the advice I often hear stated but seldom
> justified, that one shouldn't put business rules in the database. He
> offered that server-side code can be hard to debug.
>
> I'm sure many here would love to see such a book published, maybe some
> talks on the topic given.
>
>
> What might I cover that I haven't mentioned? What are the usual objections
> to server-side code and how can they be met? When *are* they justified and
> what should the criteria be to put code in Postgres? Any other thoughts?
> Any other websites or books on the topic I might consult?
>
> TIA
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi,
at my previous jobs I was working with many programmers, and almost none of
them understood SQL. The problem was even deeper. They didn't want to learn
it. When I was working among java programmers, I noticed that they hated
SQL, but there was no problem with learning HQL, which in fact is quite
similar. I really have no idea why it is like that.

I was thinking about such a book too, I have even started writing that, but
the whole set of ideas was always either too large or was a copy of what
can be found in other, general programming books. And if programmers don't
want to read the other books, and learn basics of SQL from there, then the
whole idea seemed useless to me. Of course the other reason of not writing
the book was lack of time, as writing a good book requires enormous amount
of work.

On the other hand I think that the huge problem with programmers and SQL is
changing the mindset. A standard programmer usually has a problem with
thinking in sets. Instead she usually thinks in terms of loops, and objects.

When I was giving talks about what not to do in databases, people were
either not interested or everything was a surprise for them, even for quite
experienced programmers. However after such a talk, or a training, people
were still not interested in knowing more, but they were happily learning
about programming.

I think it would be great to have a book like that, and I think it should
not be only about Postgres. But here is the problem I had with this
concept: to describe all the things to a normal programmer, assuming she
will be interested, it will need to be quite a huge and complicated book.

Or maybe this book should be about something else, start from an ORM, and
show how to translate it to much better SQL, as ORMs are the things
programmers usually understand, and they really don't bother that using
them can be a bad idea.


-- 
regards Szymon Lipiński

Re: [GENERAL] Function PostgreSQL 9.2

[Berend Tober]

On Tuesday, May 3, 2016, drum.lu...@gmail.com  
> wrote:

  * This is what I did...

|-- Creating the table
CREATE  TABLE  public.company_seqs
(company_id BIGINTNOT  NULL,
last_seq BIGINTNOT  NULL  DEFAULT  1000,
CONSTRAINT  company_seqs_pkPRIMARY  KEY  (company_id)
);


-- Creating the function

CREATE  OR  REPLACEFUNCTION  users_code_seq()
RETURNS"trigger"  AS
'
BEGIN
 UPDATE public.company_seqs
 SET last_seq = (last_seq + 1)
 WHERE company_id = NEW.company_id;
 SELECT INTO NEW.code last_seq
 FROM public.company_seqs WHERE company_id = 
NEW.company_id;
 END IF;
 RETURN new;
END
'

 LANGUAGE'plpgsql'  VOLATILE;

-- Creating the trigger
CREATE  TRIGGER  tf_users_code_seq
BEFOREINSERT
ON  public.users
FOR  EACHROW
EXECUTE  PROCEDURE  users_code_seq();|




1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old 
post that the above looks an awful lot similar too, it sure would be nice to see some attribution

rather than claiming it as your own with "...what *I* did..."




When inserting data:

|INSERT  INTO  
public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
  VALUES  (672,'te...@test.com  
','bucefalo','0','2016-05-03 
00:01:01','2016-05-03 00:01:01',default,'1');

INSERT  INTO  
public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
  VALUES  (672,'te...@test.com  
','bucefalo','0','2016-05-03 
00:01:01','2016-05-03 00:01:01','inserting my own data code column','1');|

  *

On the first query, nothing happens on the users.code column. The 
column is null.

  *

On the second query, I can see the "inserting my own data code column" 
inserted into the
code column. |This means my Trigger function is not working.. I don't 
know why.|




2) Does the public.company_seqs have any rows in it?


3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between 
company and company_seqs, put the last_seq column in the company table.



-- Berend






--
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] ruby pg connection fails on centos - okay on debian and dev machine

[john.tiger]

On 05/03/2016 06:18 PM, Adrian Klaver wrote:

On 05/03/2016 05:07 PM, john.tiger wrote:

our model.rb runs fine on a dev machine and a debian server  but is
failing on a new centos server - checked the postgres db name and user
name and password - all seem fine


The error message is?



could it be:

host => "localhost"

or maybe CORS ?


CORS as in:

https://en.wikipedia.org/wiki/Cross-origin_resource_sharing

or something else?

Have you tried connecting using psql with same parameters?

Have you looked at the pg_hba.conf files on the different platforms 
and see if they differ?


yeah, but we couldn't find any log file - not sure on centos where this 
should be  - nothing in /var/log or /var/lib/pgsql/9.5/data


running psql dbname="ourdb" works
running psql dbname="ourdb" username="ouruser"  ==> FATAL peer 
authentication failed for user"ouruser"


running \l  => shows db
   \du =>   ouruser {}  ? doesn't show login although we specified that 
as when created role - could that be the problem ?? then how to add 
login to user   alter role ouruser with login  => still shows {}


both pg_hba show:
localallall  peer
host allall  127.0.0.1
host all   all ident


re cors - all on same linode server with postgres running on std port so 
don't think should be an issue and is not issue on debian server - just 
searching for possible answer


here is our model
@conn = PG.connect(
  :host => "localhost",
  :dbname => "ourdb",
  :user  => "ouruser",
  :password => "ourpassword"
)
again, this runs fine on debian and debian dev machine

















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

[Szymon Lipiński]

On 4 May 2016 at 13:13, Chris Travers  wrote:

> A few observations
>
> On Wed, May 4, 2016 at 12:31 PM, Geoff Winkless 
> wrote:
>
>> On 4 May 2016 at 06:46, dandl  wrote:
>> > I'm a strong believer in putting the business code next to the data,
>> not the wrong
>> > side of the object-relational divide. However, for many the challenge
>> of writing and
>> > debugging SQL code is just too high!
>>
>> Your source for this statement please? "For many" sounds rather like
>> weasel-words to me. In my experience, a wide range of people, from
>> beginners to experts, find SQL easy to write and debug. I'm afraid
>> that the problem seems to me to be that your peg is rather too square.
>>
>>
> I actually agree with dandl on this.  Folks can write SQL but often aren't
> really comfortable using it as core application logic.
>
> I.e. one often sees code that retrieves a bunch of records from the db,
> loops through them, and transforms the data as part of the OLTP workflow.
> It is obviously much better of one can think about SQL as business logic
> but this is not that often.
>
> I.e. people think the peg is square but indeed it is round.
>
>
>
>From my perspective there is one more thing: when I tried, in couple of
companies, to move some part of the logic to a database, then usually the
management said "no, that's not doable, as we will have trouble with
finding good sql programmers later", and we were still writing all the
logic outside the database.



-- 
regards Szymon Lipiński

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

[dandl]

> ow...@postgresql.org] On Behalf Of Geoff Winkless

> > I'm a strong believer in putting the business code next to the data,
> > not the wrong side of the object-relational divide. However, for many
> > the challenge of writing and debugging SQL code is just too high!
> 
> Your source for this statement please? "For many" sounds rather like weasel-
> words to me. In my experience, a wide range of people, from beginners to
> experts, find SQL easy to write and debug. I'm afraid that the problem seems
> to me to be that your peg is rather too square.

Then I think you've seriously misunderstood. Most people can indeed learn to 
write basic SQL queries, but those are (obviously) not what I'm talking about.

To write the business logic of a significant application entirely in SQL 
requires PLSQL (or in other dialects, whatever passes for SQL/PSM). It means 
writing an entire data access layer as a set of stored procedures, with a 
substantial set of special functions, types, triggers and so on. No beginner 
and few experts have the skills required to do that in SQL, and then debug that 
code on the server. The plain aim of Andl is make this task far, far easier so 
that indeed a beginner can do it.

> > Meanwhile I'm busy replacing SQL with Andl, which can do everything
> > SQL gets right and avoids most of the things it gets wrong. Look out
> > for an implementation on Postgres real soon now. See
> http://www.andl.org/2016/04/postgres-meet-andl/.
> 
> Please, can you stop spamming every marginally-related topic in the list with
> this? I'm sure that anyone who's interested in this will have seen it in the
> thread you created that was actually marked with it in the subject.

The man asked a question and I gave him two links that provide specific parts 
of my answer. I think you would learn something from reading them, but perhaps 
the simplest solution is that you just ignore my posts in future.

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

[Geoff Winkless]

On 4 May 2016 at 06:46, dandl  wrote:
> I'm a strong believer in putting the business code next to the data, not the 
> wrong
> side of the object-relational divide. However, for many the challenge of 
> writing and
> debugging SQL code is just too high!

Your source for this statement please? "For many" sounds rather like
weasel-words to me. In my experience, a wide range of people, from
beginners to experts, find SQL easy to write and debug. I'm afraid
that the problem seems to me to be that your peg is rather too square.

> Meanwhile I'm busy replacing SQL with Andl, which can do everything SQL gets
> right and avoids most of the things it gets wrong. Look out for an 
> implementation
> on Postgres real soon now. See 
> http://www.andl.org/2016/04/postgres-meet-andl/.

Please, can you stop spamming every marginally-related topic in the
list with this? I'm sure that anyone who's interested in this will
have seen it in the thread you created that was actually marked with
it in the subject.

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"

[Bill Moran]

On Tue, 3 May 2016 23:11:06 -0500
Guyren Howe  wrote:

> I've long been frustrated with how most web developers I meet have no idea 
> how to use an SQL database properly. I think I'm going to write a book called 
> Love Your Database, aimed at web developers, that explains how to make their 
> apps better by leveraging the power of SQL in general, and Postgres in 
> particular.
> 
> I'm thinking of a section on features of SQL most folks don't know about 
> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT 
> and window functions), but much of the book would be about how to do things 
> server side. Benchmarks showing how much faster this can be, but mostly 
> techniques ? stored procedures/triggers/rules, views.
> 
> I asked a colleague about the advice I often hear stated but seldom 
> justified, that one shouldn't put business rules in the database. He offered 
> that server-side code can be hard to debug.
> 
> I'm sure many here would love to see such a book published, maybe some talks 
> on the topic given.
> 
> 
> What might I cover that I haven't mentioned? What are the usual objections to 
> server-side code and how can they be met? When *are* they justified and what 
> should the criteria be to put code in Postgres? Any other thoughts? Any other 
> websites or books on the topic I might consult?

Not a specific topic, but as a general theme, a lot of developers don't
seem to think it's useful for them to know SQL, and therefore don't
bother trying -- or even actively resist learning.

So if the overall theme is "knowing this makes things better", I would
buy multiple copies of the book an mysteriously leave it on various
developer's desks.

-- 
Bill Moran


-- 
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] Insert only table and size of GIN index JSONB field.

[Maxim Boguk]

On Wed, May 4, 2016 at 3:45 AM, Jeff Janes  wrote:

> On Tue, May 3, 2016 at 3:09 AM, Maxim Boguk  wrote:
> > Hi,
> >
> > I started with empty table with index over
> >  custom_fields | jsonb
> > field
> > defined as:
> > "idx_learners_custom_fields" gin (custom_fields)
> > Globally gin_pending_list_limit set to 2MB.
> > Database version is 9.5.2.
> >
> > Now question:
> > If table populated with 1M records in single transaction then the final
> size
> > of the GIN index is:
> > 4265 MB
> > but after I performed reindex index idx_learners_custom_fields;
> > the index size had been reduced 15x to 295 MB.
> >
> > Is this behavior expected?
>
> This sounds like a known issue, fixed in 9.6, where the pages used for
> the pending list do not eligible for recycling until the table is
> vacuumed.  Autovacuum does not run on insert only tables, so they just
> accumulate as empty pages in the index.
>
> Cheers,
>
> Jeff
>


​Hi Jeff,

Yes it's look like a cause.
Is there any workaround possible for 9.5.2?
At this moment I see palliative (because it doesn't reduce already bloated
index) cure via performing manual vacuum on the table after each batch
insert
or very slow (for the large table) full scale fix via create new index/drop
old index.
​

-- 
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.com/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.bo...@gmail.com

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."

Re: [GENERAL] Vacuum full of parent without partitions possible?

[Jan Keirse]

On Tue, May 3, 2016 at 3:22 PM, Tom Lane  wrote:

> Jan Keirse  writes:
> > I have a table that used to contain all data.
> > because it grew too big I added a partition trigger a long time ago and
> > since than all new data was added to small partitions. By now all data in
> > the original parent table has become obsolete and was deleted, however
> the
> > disk space cannot be reclaimed without a vacuum full. The problem is, a
> > vacuum full of only the parent table should be instantaneous since it
> > contains no rows, but because the vacuum full triggers a vacuum of all
> > partitions too,
>
> No, a VACUUM on a single table processes only that table.
>
> I'm inclined to think your actual problem is that VACUUM FULL wants
> an exclusive lock and can't get one because of other traffic on the
> table.  Plain VACUUM doesn't need an exclusive lock ... unless it's
> trying to truncate the relation, which in this case it presumably would
> be.  Maybe your conclusion that you needed a VACUUM FULL was based
> on observing that VACUUM didn't reduce disk consumption; but if the
> table is empty, that would only be because it couldn't get exclusive
> lock.
>
> I'd suggest waiting for a low-traffic time of day and then doing a
> plain VACUUM.  Or alternatively, if you're sure the table is empty
> and will stay that way, you could just cut to the chase and TRUNCATE
> it.  But none of these alternatives are going to reclaim any disk
> space without taking an exclusive lock on the table, because they
> simply cannot truncate the file while other queries are scanning it.
>

​OK, thanks for clearing that up. It is indeed impossible to ever take an
exclusive lock on the table during normal operations (there are continuous
selects and ​

inserts into the table which are redirected to the partitions by a before
insert trigger, the data is all machine output and the load is constant
24x7.​)
I'll leave the table as is for the time being and do a vacuum the next time
there is a need for scheduled down time.

-- 


 DISCLAIMER 

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."

[GENERAL] Do parallel queries work with only dblink not with fdw?

[Klaus P.]

In order to improve cpu and disk utilization, I am testing parallel
queries.

The approach with dblink_send_query() and dblink_get_result() works in my
proof-of-concept. Runtime of my reference query was reduced from 55 seconds
to ~20seconds using 4 parallel connections. Not what I had hoped but
certainly a significant improvement.

My approach is fairly easy:

db_link_send_query('conn1', 'statement based on partitioning field');
db_link_send_query('conn2', 'statement based on partitioning field');

...

SELECT
dblink_get_result('conn1')
UNION ALL
dblink_get_result('conn2')

...


However, using fdw foreign data wrappers, I was not able to run any query
in parallel. I came across this presentation
http://de.slideshare.net/EnterpriseDB/foreign-data-wrappers-and-you-with-postgres
where it says on page 12  "parallel FDW access". Is there any example
available on how to run FDW queries in parallel?

My approach with FDW is similar to the dblink example above:

SELECT * FROM myFdwTable WHERE (clause based on partitioning field)
UNION ALL
SELECT * FROM myFdwTable WHERE (clause based on partitioning field)
...

My experience is however that the statements are carried out sequentially.

Thanks

Klaus Pieper

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

[Chris Travers]

I think one of the key questions is when to put logic in the database (and
this is controversial so it may be worth covering from a few angles).  In
general my view is:

1.  Data logic belongs in the database
2.  Logic you can't roll back belongs somewhere else
3.  A lot of stuff could go either place.

Another thing I would recommend is looking at SQL in a way that is similar
to map(), reduce(), and filter() (using python terms but you can find
similar in other languages).

Additionally some functional programming theory can go a long way in both
understanding database normalization and putting logic in the database.

On Wed, May 4, 2016 at 6:11 AM, Guyren Howe  wrote:

> I've long been frustrated with how most web developers I meet have no idea
> how to use an SQL database properly. I think I'm going to write a book
> called Love Your Database, aimed at web developers, that explains how to
> make their apps better by leveraging the power of SQL in general, and
> Postgres in particular.
>
> I'm thinking of a section on features of SQL most folks don't know about
> (CTEs are *way* to hell at the top of that list, but also EXCEPT/INTERSECT
> and window functions), but much of the book would be about how to do things
> server side. Benchmarks showing how much faster this can be, but mostly
> techniques — stored procedures/triggers/rules, views.
>
> I asked a colleague about the advice I often hear stated but seldom
> justified, that one shouldn't put business rules in the database. He
> offered that server-side code can be hard to debug.
>
> I'm sure many here would love to see such a book published, maybe some
> talks on the topic given.
>
>
> What might I cover that I haven't mentioned? What are the usual objections
> to server-side code and how can they be met? When *are* they justified and
> what should the criteria be to put code in Postgres? Any other thoughts?
> Any other websites or books on the topic I might consult?
>
> TIA
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] MVIEW refresh consistently faster then insert ... select

[Thomas Kellerer]

Kevin Grittner schrieb am 04.05.2016 um 09:06:
> On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer  wrote:
> 
>> I have a table that is an aggregation of another table.
>> This aggregation reduces an input of ~14 million rows to ~4
>> million rows.
> 
>> The refresh takes approx 2 minutes (fastest was 1:40) on our
>> development server (CentOS, Postgres 9.5.0)
> 
>> However, when I create a materialized view:
> 
>> Subsequent refreshs using "REFRESH MATERIALIZED VIEW mv_stock"
>> are consistently much faster: between 40 seconds and 1 minute
>>
>> I have run both refreshs about 10 times now, so caching effects
>> should not be there.
>>
>> My question is: what is refresh mview doing differently then a
>> plain insert ... select that it makes that so much faster?
> 
> If *without*, I would guess the difference is probably in creating
> the index "from scratch" with sort and load versus retail insertion
> of index entries.  You could approximate this by dropping the index
> before the TRUNCATE and INSERT and creating it again after it is
> loaded.

It's without. 

But your suggestion got me looking in the right direction ;)

I completely forgot that the table has two foreign keys that I did not create 
on the mview.

When I remove those, then both solutions are equally fast.

Sorry for the noise. 

Thomas





-- 
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] MVIEW refresh consistently faster then insert ... select

[Kevin Grittner]

On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer  wrote:

> I have a table that is an aggregation of another table.
> This aggregation reduces an input of ~14 million rows to ~4
> million rows.

> The refresh takes approx 2 minutes (fastest was 1:40) on our
> development server (CentOS, Postgres 9.5.0)

> However, when I create a materialized view:

> Subsequent refreshs using "REFRESH MATERIALIZED VIEW mv_stock"
> are consistently much faster: between 40 seconds and 1 minute
>
> I have run both refreshs about 10 times now, so caching effects
> should not be there.
>
> My question is: what is refresh mview doing differently then a
> plain insert ... select that it makes that so much faster?

Just to confirm, is this with or without the CONCURRENTLY keyword
on the REFRESH command?

If *without*, I would guess the difference is probably in creating
the index "from scratch" with sort and load versus retail insertion
of index entries.  You could approximate this by dropping the index
before the TRUNCATE and INSERT and creating it again after it is
loaded.

If *with*, I would guess that it is because most of the work is
done in temporary files and workspace, with just the delta applied
to the table and index in permanent storage.

It's hard to guess which way will be faster for the use case you
describe -- it will probably depend on what percentage of rows
remain unchanged on each REFRESH.

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

[GENERAL] MVIEW refresh consistently faster then insert ... select

[Thomas Kellerer]

Hello,

I have a table that is an aggregation of another table. 
This aggregation reduces an input of ~14 million rows to ~4 million rows. 

So far I have used a truncate/insert approach for this: 

truncate table stock;
insert into stock (product_id, warehouse_id, reserved_provisional, 
reserved, available, non_deliverable)
select product_id, warehouse_id, sum(reserved_provisional), sum(reserved), 
sum(available), sum(non_deliverable)
from staging.stock_data
group by product_id, warehouse_id;

The table has a primary key on (product_id, warehouse_id), all columns are 
integer columns.
The refresh takes approx 2 minutes (fastest was 1:40) on our development server 
(CentOS, Postgres 9.5.0) 

However, when I create a materialized view: 

create materialized view mv_stock 
as
select product_id, 
   warehouse_id, 
   sum(reserved_provisional) as reserved_provisional, 
   sum(reserved) as reserved, 
   sum(available) as available, 
   sum(non_deliverable) as non_deliverable
from staging.stock_data
group by product_id, warehouse_id;

create unique index pk_mv_stock on mv_stock (product_id, warehouse_id);

Subsequent refreshs using "REFRESH MATERIALIZED VIEW mv_stock" are consistently 
much faster: between 40 seconds and 1 minute

I have run both refreshs about 10 times now, so caching effects should not be 
there. 

My question is: what is refresh mview doing differently then a plain insert ... 
select that it makes that so much faster? 

The select itself takes about 25 seconds. It is using an external merge on 
disk, which can be removed by increasing work_mem (the select then goes down to 
12 seconds, but that doesn't change much in the runtime of the refreshs). 

The 2 minutes are perfectly acceptable, I'm just curious why refreshing the 
mview would be so much faster as the work they are doing should be exactly the 
same. 

Thomas








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

[Charles Clavadetscher]

Good morning

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe
> Sent: Mittwoch, 4. Mai 2016 06:11
> To: pgsql-general 
> Subject: [GENERAL] Thoughts on "Love Your Database"
> 
> I've long been frustrated with how most web developers I meet have no idea 
> how to use an SQL database properly. I
> think I'm going to write a book called Love Your Database, aimed at web 
> developers, that explains how to make their
> apps better by leveraging the power of SQL in general, and Postgres in 
> particular.
> 
> I'm thinking of a section on features of SQL most folks don't know about 
> (CTEs are *way* to hell at the top of that
> list, but also EXCEPT/INTERSECT and window functions), but much of the book 
> would be about how to do things server
> side. Benchmarks showing how much faster this can be, but mostly techniques — 
> stored procedures/triggers/rules,
> views.
> 
> I asked a colleague about the advice I often hear stated but seldom 
> justified, that one shouldn't put business rules
> in the database. He offered that server-side code can be hard to debug.
> 
> I'm sure many here would love to see such a book published, maybe some talks 
> on the topic given.
> 
> 
> What might I cover that I haven't mentioned? What are the usual objections to 
> server-side code and how can they be
> met? When *are* they justified and what should the criteria be to put code in 
> Postgres? Any other thoughts? Any
> other websites or books on the topic I might consult?

If you have a complex design or if the processes require the modification of 
various tables within a transaction you may probably prefer to expose functions 
as the application interface. Advantages of this approach:

- Hide complexity: You don't need to explain all the details, dependencies and 
implications to all web developers. Just make sure that your documentation is 
up-to-date for those who want to learn about it.
- Transactions are controlled by the database: You may have doubts if 
application developers do handle this correctly.
- Minimize the impact on application development: If changes to requirements 
force changes in the database, these would be transparent to the application. 
Even if the interface changes, that may mean only an additional argument to a 
function.
- Security: You can grant execute on (security definer) functions instead of 
granting privileges for each object. The latter can become quite complex.
- Separation of concerns: Application developers don't need to (but can if they 
want) learn SQL. They should focus instead on the presentation layer, which at 
the end is what customers see and sells.

Bye
Charles

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