Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote: > On 25/08/15 01:15, Ray Cote wrote: > >On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert >> wrote: > > > [...] > > > >9. Do NOT arbitrarily assign an "id" column to a table as a > >primary key w

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

Igor Sosa Mayor writes: > Igor Sosa Mayor writes: > >> My question is therefore: >> 1. is there a way to permit the pl/python to connect to the internet all >>the time and with a better configuration? >> 2. or should I forget the procedure and write a python script outside >>the database

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

Adrian Klaver writes: >> But I see that the log options in PG are really rich. Could maybe >> someone tell me which could be the best options to find the problem? I >> will be offline now during 24h, but I will try to make some experiments >> in the meantime. > > From here: > http://www.postgresq

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

Igor Sosa Mayor writes: > Maybe it has something to do with systemd? I'm trying to get all debug A little more information: the unit of postgresql in my systemd looks like this[1]. That means, it is started of course after the network (but maybe there is not any connection avalaible?) [1] [Unit

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

Dave Potts writes: > In cases like this I normally restart the progresql under strace/truss > etc and then wade through the output, it will normally tell me which > process was invoked. Thanks for the hint. I answered you indirectly in other email. -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 19:04, Karsten Hilbert wrote: On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote: 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 tab

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

On 08/25/2015 01:30 AM, Igor Sosa Mayor wrote: Igor Sosa Mayor writes: Igor Sosa Mayor writes: My question is therefore: 1. is there a way to permit the pl/python to connect to the internet all the time and with a better configuration? 2. or should I forget the procedure and write a pyt

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 22, 2015, at 10:15 AM, Melvin Davidson wrote: > 6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best > to specify as a CONSTRAINT, >that way YOU get to choose the name, otherwise postgres assigns a default > name which may not be to your liking. >EG: ,

[GENERAL] Why this lock?

I have a long-running query (running now for more than 6 days already (process 17434). It involves three tables of which one contains XML-data. On another, unrelated table with 30718567 records, I ran a query to create an index on a field. This morning I cancelled this process because it did no

Re: [GENERAL] Why this lock?

On Tue, Aug 25, 2015 at 8:33 AM, Johann Spies wrote: > I have a long-running query (running now for more than 6 days already > (process 17434). It involves three tables of which one contains XML-data. > > On another, unrelated table with 30718567 records, I ran a query to create > an index on a

Re: [GENERAL] Why this lock?

On 25 August 2015 at 15:52, Merlin Moncure wrote: > h... > > creating and index requires exclusive access. did you try the > concurrent variant? > Yes. The one which I stopped after 5 days, was running concurrently. There was a similar lock involved. Regards Johann -- Because experiencing y

Re: [GENERAL] Why this lock?

Johann Spies writes: > On 25 August 2015 at 15:52, Merlin Moncure wrote: >> creating and index requires exclusive access. did you try the >> concurrent variant? > Yes. The one which I stopped after 5 days, was running concurrently. There > was a similar lock involved. That lock type is used b

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/24/2015 08:44 PM, Rob Sargent wrote: 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 previ

Re: [GENERAL] PostgreSQL Developer Best Practices

Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that there is no need to duplicate uniqueness with a separate number. IOW: If we have an account ta

Re: [GENERAL] PostgreSQL Developer Best Practices

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 I would not consider the general use of natural primary

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 09:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that there is no need to duplicate uniqueness w

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 11:40 AM, Melvin Davidson wrote: > Consider: > SELECT c.registration_no, >c.car_make, >p.part_no >FROM car c >JOIN parts p ON ( p.registration_no = c.registration_no) > WHERE registration_no = ; > > versus: > SELECT c.registration_no, >c.

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 12:09 PM, Rob Sargent wrote: > On 08/25/2015 09:40 AM, Melvin Davidson wrote: > >> Adrian, >> >> Stop being so technical. When we/I speak of natural keys, we are talking >> about the column >> that would NATURALly lend itself as the primary key. >> No one ever said a numbe

Re: [GENERAL] [pgsql-general] Daily digest v1.13732 (15 messages)

On Tue, 2015-08-25 at 15:41 +, Neil Tiffin wrote: > I really like the standardization that PostgreSQL uses in auto > generating default names. The rule I use is to always use the auto > generated names unless the object is referenced routinely in code. In > most cases developers don’t care

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 09:09 AM, Rob Sargent wrote: On 08/25/2015 09:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. No one ever said a number is not natural. just that th

Re: [GENERAL] PostgreSQL Developer Best Practices

> No one ever said a number is not natural. just that there is no need to > duplicate uniqueness > with a separate number. The whole point is that people are telling you that surrogate keys do not _duplicate_ uniqueness but rather _generate_ it, artificially, and therefore reliably. Today's ext

Re: [GENERAL] PostgreSQL Developer Best Practices

Consistency in naming convention. Good suggestion! On Tue, Aug 25, 2015 at 12:33 PM, Marc Munro wrote: > On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: > > > I've been searching for a "PostgreSQL Developer Best Practices" with not > > much luck, > > so I've started my own. At the ris

Re: [GENERAL] PostgreSQL Developer Best Practices

Personally I always set the natural key with a not null and unique constraint, but create an artificial key for it as well. As an example, if we had a product table, the product_sku is defined as not null with a unique constraint on it, while product_id is the primary key which all other tables re

[GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

Howdy All, For a very long time I've held the belief that splitting PGDATA and xlog on linux systems fairly universally gives a decent performance benefit for many common workloads. (i've seen up to 20% personally). I was under the impression that this had to do with regular fsync()'s from the

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

However, I know from experience that's not entirely true, (although it's not always easy to measure all aspects of your I/O bandwith). Am I missing something? Two things I can think of: Transaction writes are entirely sequential. If you have disks assigned for just this purpose, then the he

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

On Tue, 25 Aug 2015 10:08:48 -0700 David Kerr wrote: > Howdy All, > > For a very long time I've held the belief that splitting PGDATA and xlog on > linux systems fairly universally gives a decent performance benefit for many > common workloads. > (i've seen up to 20% personally). > > I was un

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote: > >However, I know from experience that's not entirely true, (although it's not > >always easy to measure all aspects of your I/O bandwith). > > > >Am I missing something? > > > Two things I can think of: > > Transaction writes are entirely s

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

> On Aug 25, 2015, at 10:45 AM, Bill Moran wrote: > > On Tue, 25 Aug 2015 10:08:48 -0700 > David Kerr wrote: > >> Howdy All, >> >> For a very long time I've held the belief that splitting PGDATA and xlog on >> linux systems fairly universally gives a decent performance benefit for many >> c

Re: [GENERAL] PostgreSQL Developer Best Practices

> In most cases developers don’t care about index, unique, foreign key, or > primary key names (from a coding standpoint) Until the day they'd like to write a reliable database change script. (PG's internal conventions for object names _have_ changed over the years) Karsten -- Sent via pgsql

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

On 26/08/15 05:54, David Kerr wrote: On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote: However, I know from experience that's not entirely true, (although it's not always easy to measure all aspects of your I/O bandwith). Am I missing something? Two things I can think of: Transaction w

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

On Tue, Aug 25, 2015 at 4:31 PM, Gavin Flower wrote: > On 26/08/15 05:54, David Kerr wrote: > >> On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote: >> >>> However, I know from experience that's not entirely true, (although it's not always easy to measure all aspects of your I/O bandwith)

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 03:40, Melvin Davidson wrote: [...] IOW: If we have an account table, then the account_id or account_no would be the primary key. There is no need to have a separate serial id as the primary key. [...] Account numbers are externally generated, and may potentially change. Man

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sure this is a technical list:) No one ever said a number is not natural. just that the

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the year the account started as the first 4 digits, or that the branch code should be reflected in it, or something els

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 01:56 PM, John R Pierce wrote: On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the year the account started as the first 4 digits, or that the branch code

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 08:56, Adrian Klaver wrote: On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sure this is a technical list:) Don't let inco

[GENERAL] PostgreSQL Developer Best Practices

On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: > I've been searching for a "PostgreSQL Developer Best Practices" with not > much luck, > so I've started my own. At the risk of stirring up a storm of controversy, > I would appreciate additional suggestions and feedback. > You might add:

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 02:17, Adrian Klaver wrote: [...] 2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologists have discovered DNA testing, it can be

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 02:23 PM, Gavin Flower wrote: On 26/08/15 08:56, Adrian Klaver wrote: On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sur

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 02:44 PM, Gavin Flower wrote: On 26/08/15 02:17, Adrian Klaver wrote: [...] 2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologi

Re: [GENERAL] PostgreSQL Developer Best Practices

NB the attribution colours seems to be mixed up a bit here, but this all dialogue between me & Adrian. On 26/08/15 09:48, Adrian Klaver wrote: On 08/25/2015 02:23 PM, Gavin Flower wrote: On 26/08/15 08:56, Adrian Klaver wrote: [...] have all gone to the same seminar on how to be Walmart and

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 04:33, Marc Munro wrote: On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: I've been searching for a "PostgreSQL Developer Best Practices" with not much luck, so I've started my own. At the risk of stirring up a storm of controversy, I would appreciate additional suggestions

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 25, 2015, at 1:38 PM, Karsten Hilbert wrote: > >> In most cases developers don’t care about index, unique, foreign key, or >> primary key names (from a coding standpoint) > > Until the day they’d like to write a reliable database change script. Not sure I understand. Once the object

Re: [GENERAL] PostgreSQL Developer Best Practices

On 8/25/2015 1:38 PM, Joshua D. Drake wrote: But a VIN is in fact, UNIQUE so it is useful as a PK. JD But a VIN is *not* guaranteed to exist, nor is it guaranteed never to change, and I regard those as pretty important characteristics in a PK. VINs were not required in the U.S. until 1954, a

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 04:27 PM, Gavin Flower wrote: On 26/08/15 04:33, Marc Munro wrote: On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: I've been searching for a "PostgreSQL Developer Best Practices" with not much luck, so I've started my own. At the risk of stirring up a storm of controv

[GENERAL] BDR: cannot remove node from group

Testing BDR for the first time, using the binary packages for Ubuntu 10.04 provided at http://packages.2ndquadrant.com/bdr/apt/ Postgres 9.4.4 and BDR 0.9.2 (I think) I'm loosely following this document: http://bdr-project.org/docs/stable/quickstart-enabling.html Except I've created two separ

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 6:27 PM, Gavin Flower wrote: > On 26/08/15 04:33, Marc Munro wrote: > >> On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: >> >> I've been searching for a "PostgreSQL Developer Best Practices" with not >>> much luck, >>> so I've started my own. At the risk of stirr

Re: [GENERAL] PostgreSQL Developer Best Practices

Adrian Klaver writes: > On 08/25/2015 01:56 PM, John R Pierce wrote: > >> On 8/25/2015 1:42 PM, Gavin Flower wrote: >>> Account numbers are externally generated, and may potentially change. >>> Management might suddenly decide that they want to start using the >>> year the account started as the

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 04:23 PM, Jerry Sievers wrote: Adrian Klaver writes: On 08/25/2015 01:56 PM, John R Pierce wrote: On 8/25/2015 1:42 PM, Gavin Flower wrote: Account numbers are externally generated, and may potentially change. Management might suddenly decide that they want to start using the

[GENERAL] Grouping sets, cube and rollup

Any chance to get those amazing wonderful features backported to 9.4? Thanks, Edson Enviado do meu smartphone Sony Xperia™

Re: [GENERAL] PostgreSQL Developer Best Practices

I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for the sake of argument, a natural key is something that in itself is uniq

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 11:34, Adrian Klaver wrote: [...] Agreed, but it happens. When Lowes took over a local hardware chain(Eagles) here in Washington state they moved very quickly on changing the account numbers. The company I worked for who supplied Eagles and then Lowes sat on a check for $22,000 that

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 05:17 PM, Melvin Davidson wrote: I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for the sake of argument,

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 05:21 PM, Gavin Flower wrote: On 26/08/15 11:34, Adrian Klaver wrote: [...] Agreed, but it happens. When Lowes took over a local hardware chain(Eagles) here in Washington state they moved very quickly on changing the account numbers. The company I worked for who supplied Eagles an

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tuesday, August 25, 2015, Melvin Davidson wrote: > > Before ANYONE continues to insist that a serial id column is good, > consider the case where the number of tuples will exceed a bigint. > Don't say it cannot happen, because it can. > However, if you have an alphanumeric field, let's say varc

Re: [GENERAL] BDR: cannot remove node from group

On 26 August 2015 at 07:19, Florin Andrei wrote: > What do I need to do to start over? I want to delete all traces of the BDR > configuration I've done so far. you need to DROP the database you removed, then re-create it as a new empty database. You cannot re-join a node that has been removed.

Re: [GENERAL] Grouping sets, cube and rollup

Le 26 août 2015 2:06 AM, "Edson Richter" a écrit : > > Any chance to get those amazing wonderful features backported to 9.4? > No. Only bug fixes are backported. -- Guillaume.

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: > I think a lot of people here are missing the point. I was trying to > give examples of natural keys, but a lot of people are taking great > delight > in pointing out exceptions to examples, rather than understanding the > point. > So f