Re: [GENERAL] PostgreSQL Developer Best Practices

David, Thank you. This is exactly the type of feedback I was looking for. To answer your question, for now it is a guide for one particular client, however, if I get enough feedback and contributions, I will revise it and submit to the PostgreSQL community. On Mon, Aug 24, 2015 at 2:34 AM, Thomas

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert wrote: > > 1. Prefix ALL literals with an Escape > >EG: SELECT E'This is a \'quoted literal \''; > > SELECT E'This is an unquoted literal'; > > > >Doing so will prevent the annoying "WARNING: nonstandard use of > escape in a stri

Re: [GENERAL] PostgreSQL Developer Best Practices

9. >1) What happens if someone mis-types the account-id? > To correct that, you also need to correct the FK field in the other dozen tables. >2) What happens when your company starts a new project (or buys a competitor) >and all the new account numbers are alpha-numeric? I would reply that in

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson wrote: > 9. > >1) What happens if someone mis-types the account-id? > > To correct that, you also need to correct the FK field in the other > dozen tables. > >2) What happens when your company starts a new project (or buys a > competitor) >and

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote wrote: > On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert > wrote: > >> > 1. Prefix ALL literals with an Escape >> >EG: SELECT E'This is a \'quoted literal \''; >> > SELECT E'This is an unquoted literal'; >> > >> >Doing so will prevent

[GENERAL] Invalid memory alloc request size

Over the weekend, Amazon did some maintenance that resulted in one of our instances being restarted. Apparently this left the database in a bad state. This particular instance functions as a slony replication target and when I went to start up slony, I get the following error message. Here's some r

[GENERAL] AIX 7.1 compile and initdb startup error TRAP: FailedAssertion

Hi, I am trying to compile and run PostgreSQL 9.4.0 on AIX 7.1. I was able to compile with following settings: CC="xlc_r -q64 -qnoansialias" CFLAGS="-g -O0" AR="ar -X64" OBJECT_MODE=64 ./configure --enable-cassert --enable-debug -with-includes=/opt/freeware/include --with-libraries=/opt/freeware

[GENERAL] AIX 7.1 compile and initdb startup error TRAP: FailedAssertion

Hello, I am trying to compile and run PostgreSQL 9.4.0 on AIX 7.1. I was able to compile with following settings: CC="xlc_r -q64 -qnoansialias" CFLAGS="-g -O0" AR="ar -X64" OBJECT_MODE=64 ./configure --enable-cassert --enable-debug -with-includes=/opt/freeware/include --with-libraries=/opt/freew

Re: [GENERAL] Problem with database connections timing out for long-running queries

I am using the default TIMEOUTidle setting in stunnel so that seems plausible. Thanks very much for pointing me in what sounds like the right direction! Kind regards, Rich From: Steve Crawford [mailto:scrawf...@pinpointresearch.com] Sent: Friday, August 21, 2015 6:25 PM To: Rich Schaa

Re: [GENERAL] PostgreSQL Developer Best Practices

>What then if it is discovered that the keyed in value was mis-typed? That is why SQL has UPDATE and DELETE statements. If a primary key is incorrect, it can be fixed, be it one method of another. On Mon, Aug 24, 2015 at 10:04 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon,

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 10:32 AM, Melvin Davidson wrote: > >What then if it is discovered that the keyed in value was mis-typed? > > That is why SQL has UPDATE and DELETE statements. If a primary key is > incorrect, > it can be fixed, be it one method of another. > ​Yes, a DBA can use ON DELETE

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote wrote: 9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns are perfectly suited as a unique primary key. ... Good example: CREATE TABLE accounts ( accout_id bigint NOT NULL , I would

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 07:58 AM, John Turner wrote: On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote Point 9 is well-intentioned, but perhaps needs to be clarified/rephrased: Developers should not be creating production-grade tables devoid of well-defined business keys, period. That would be regardless o

Re: [GENERAL] PostgreSQL Developer Best Practices

>The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst every single web development environment in existen

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst e

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt it is "the default primary key amongst e

Re: [GENERAL] PostgreSQL Developer Best Practices

And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. It is far better to let DBA's and "database develeopers" design a good database, then to let those apps mold a db into a non-optimum design

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. And I don't argue that but we also live in a world based on reality. DBAs are rare, web

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. Again this is not restricted to Web apps. Anything that touches a database via an ORM i

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, 24 Aug 2015 12:40:37 -0400, Joshua D. Drake wrote: On 08/24/2015 09:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. And I don't argue that b

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/24/2015 9:34 AM, Melvin Davidson wrote: And again, I am talking about _database_ design, not Web apps. Letting Web developers design a database to work with their app, is a very, Very, VERY bad idea. It is far better to let DBA's and "database develeopers" design a good database, then to l

Re: [GENERAL] PostgreSQL Developer Best Practices

Thank you John R. Pierce. Finally someone who understands the purpose of this thread. Otherwise, next thing you know, we'll have Web apps/developers designing bra's for milk cows so they'll look better in the field. :) On Mon, Aug 24, 2015 at 1:05 PM, John R Pierce wrote: > On 8/24/2015 9:34 AM,

Re: [GENERAL] master/master replication with load balancer in front

On 2015-08-23 06:56, Martín Marqués wrote: El 21/08/15 a las 20:45, Florin Andrei escribió: The single instance scheme is not very reliable. I need to build a new DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2 instances, each instance placed in a different availability zone.

Re: [GENERAL] master/master replication with load balancer in front

On 2015-08-22 03:05, Chris Mair wrote: a few keywords in your mail hint at the fact you're using AWS? If that's the case, you might want to look into their managed PostgreSQL hosting: it's called Amazon RDS for PostgreSQL and supports failover ("Multi AZ") and master-slave replication ("Read Re

Re: [GENERAL] PostgreSQL Developer Best Practices

>ON UPDATE CASCADE ? I believe he's talking about triggers. On Mon, Aug 24, 2015 at 8:00 PM, Berend Tober wrote: > Melvin Davidson wrote: > >> 9. >> >1) What happens if someone mis-types the account-id? >> > To correct that, you also need to correct the FK field in the >> other dozen tabl

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 08:22:17PM -0400, Melvin Davidson wrote: > On Mon, Aug 24, 2015 at 8:00 PM, Berend Tober wrote: > >> 1) What happens if someone mis-types the account-id? > >> To correct that, you also need to correct the FK field in the > >> other dozen tables. > > > > ..

Re: [GENERAL] PostgreSQL Developer Best Practices

You are right, he was probably talking about FK's. I was just so frustrated about people insisting that using "ID" as the primary key in every table is a "good" idea, I didn't bother to reply previously. I stand firm on my belief that the primary key should be something meaningful and NOT "id" just

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 01:15, Ray Cote wrote: On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert mailto:karsten.hilb...@gmx.net>> wrote: [...] 9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns are perfectly suited as a unique primary key. ...

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 02:58, John Turner wrote: [...] Conversely, if synthetic keys are chosen as Primary, they must be accompanied by a legitimate Unique natural key. Agreed, but only where appropriate. Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 04:26, Joshua D. Drake wrote: On 08/24/2015 08:56 AM, Melvin Davidson wrote: >The "serial" key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely dou

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 08:53:43PM -0400, Melvin Davidson wrote: > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is > a "good" idea, > I didn't bother to reply previously. I stand firm on my bel

Re: [GENERAL] PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower < gavinflo...@archidevsys.co.nz> wrote: > Also the best practice is to make the primary key name 'id' as you do know > the table it is in, so prepending the table name is redundant - so you can > clearly identify foreign keys because the suffix '_id '

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 24, 2015, at 6:53 PM, Melvin Davidson wrote: > > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is a > "good" idea, > I didn't bother to reply previously. I stand firm on my belief

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 14:45, David G. Johnston wrote: On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower >wrote: Also the best practice is to make the primary key name 'id' as you do know the table it is in, so prepending the table name is redundant - so you