Re: Enforce primary key on every table during dev?

> On 1 Mar 2018, at 1:47, Melvin Davidson wrote: > > I think you would be better off having an automated report which alerts > >you to tables lacking a primary key and deal with that policy through > >other means. > > Perhaps a better solution is to have a meeting with

Version upgrade: is restoring the postgres database needed?

Or do we just apply the globals.sql created by "pg_dumpall --globals-only"? (We're upgrading by restoring all databases on a new server, that, naturally, has it's own new postgres, template0 and template1 databases.) Thanks -- Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:20 AM, Alban Hertroys wrote: [snip] Not to mention that not all types of tables necessarily have suitable candidates for a primary key. You could add a surrogate key based on a serial type, but in such cases that may not serve any purpose other than to have some arbitrary

Custom ranking function for full text search

are there any examples/documentation for building an alternative ts_rank/ts_rank_cd for full text search?

Re: Version upgrade: is restoring the postgres database needed?

On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson wrote: > > Or do we just apply the globals.sql created by "pg_dumpall --globals-only"? > > (We're upgrading by restoring all databases on a new server, that, > naturally, has it's own new postgres, template0 and template1

Re: Enforce primary key on every table during dev?

>Adding a surrogate key to such a table just adds overhead, although that could >be useful >in case specific rows need updating or deleting without also modifying the >other rows with >that same data - normally, only insertions and selections happen on such >tables though, >and updates or

Re: Posgresql Log: lots of parse statements

On Thu, Mar 1, 2018 at 8:23 AM, Vikas Sharma wrote: > Hi All, > > I need help to understand this please. I was looking to do performance > tuning on slow queries so have stated logging queries taking more than 15 > secs. In the postgresql log I can see a query which appears

Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 08:46 AM, Melvin Davidson wrote: On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson > wrote: Or do we just apply the globals.sql created by "pg_dumpall --globals-only"? (We're upgrading by restoring all databases on a

Re: Enforce primary key on every table during dev?

> On 03/01/2018 02:20 AM, Alban Hertroys wrote: > [snip] > > Not to mention that not all types of tables necessarily have > > suitable candidates for a primary key. You could add a surrogate > > key based on a serial type, but in such cases that may not serve > > any purpose other than to have

Posgresql Log: lots of parse statements

Hi All, I need help to understand this please. I was looking to do performance tuning on slow queries so have stated logging queries taking more than 15 secs. In the postgresql log I can see a query which appears only as "parse" while others appear as execute. 2018-01-21 14:01:16 GMT LOG:

Re: Enforce primary key on every table during dev?

- Original Message - > From: "Tim Cross" > Sent: Wednesday, February 28, 2018 4:07:43 PM > > Jeremy Finzel writes: > > > We want to enforce a policy, partly just to protect those who might forget, > > for every table in a particular schema to

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 10:36 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 1:32 PM, marcelo wrote: > >> What´s the question? >> >> > ​Whether the OP, who hasn't come back, knew they were starting a flame war > by asking this

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, marcelo wrote: On 01/03/2018 17:21 , Ron Johnson wrote: On 03/01/2018 02:08 PM, marcelo wrote: On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a

Re: PQConsumeinput stuck on recv

Hi, I was able to capture the backtrace again, now with libpq debugging symbols. Thread 15 (Thread 0x7f8cec068700 (LWP 68)): #0 0x7f8d252a1d9b in __libc_recv (fd=150, buf=0x7f8cf0034410, n=16384, flags=623517083, flags@entry=0) at ../sysdeps/unix/sysv/linux/x86_64/recv.c:33 #1

Re: Enforce primary key on every table during dev?

On 01/03/2018 19:05 , Gavin Flower wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote:     >Adding a surrogate key to such a table just adds overhead,     although that could be

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote: Why have the overhead of a second unique index?  If it's "ease of joins", then I agree with Francisco Olarte and use the business

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:26 PM, Ron Johnson wrote: On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic.

How to perform PITR when all of the logs won't fit on the drive

Hello, I have a situation where something was deleted from a database that shouldn't have been so I am having to take a base backup and perform a point-in-time-recovery. The problem I have is that the decompressed WAL files will not fit on the drive of the machine I am trying to do the restore

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson wrote: > >> Why have the overhead of a second unique index? If it's "ease of joins", >> then I agree with Francisco Olarte and use the

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:44 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston > wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote:

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic. Actually, no: https://en.wikipedia.org/wiki/Payment_card_number There is a method to the

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:32 , David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote: Why have the overhead of a second unique index?  If it's "ease of joins", then I agree with Francisco Olarte and use the business

Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 11:28 AM, Melvin Davidson wrote: [snip] *>Hmmm.  I just looked at the script, and it says: >$ pg_dumpall --schema-only > globals.sql >That's not good. * *No that's actually correct. pg_dumpall  can and will dump the globals * *pg_dump cannot* I was invoking --schema-only and

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:07 PM, Rakesh Kumar wrote: ... > I routinely add surrogate keys like serial col to a table already having a > nice candidate keys > to make it easy to join tables. SQL starts looking ungainly when you have a > 3 col primary > key and need to

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:05 PM, Melvin Davidson wrote: On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson > wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: [snip] If your only unique index is a synthetic key, then you can insert

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:09 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson > wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:06 PM, Tim Cross wrote: > +1. And a good test of your underlying data model is whether you can > identify a natural primary key. If you can't, chances are your model is > immature/flawed and needs more analysis. >

Re: Enforce primary key on every table during dev?

On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic. Actually, no:

Re: Enforce primary key on every table during dev?

> On Mar 1, 2018, at 12:47 , Daevor The Devoted wrote: > > > I was always of the opinion that a mandatory surrogate key (as you describe) > is good practice. > Sure there may be a unique key according to business logic (which may be > consist of those > "ungainly"

Re: Enforce primary key on every table during dev?

I found this thread very interesting. A pivot table is a perfectly valid use case where a compound unique key on two or more columns performs the same function as a primary key without one. I’m not nearly as familiar with Postgres as I am with MySQL (which is why I recently joined this

Re: Enforce primary key on every table during dev?

Ron Johnson writes: > On 03/01/2018 02:08 PM, marcelo wrote: >> >> >> On 01/03/2018 16:42 , Ron Johnson wrote: >>> On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: >>> [snip] > If your only unique index is a synthetic key, then

Re: Enforce primary key on every table during dev?

On 01/03/2018 18:41 , Adrian Klaver wrote: On 03/01/2018 01:26 PM, Ron Johnson wrote: On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic.

Re: Enforce primary key on every table during dev?

On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: >Adding a surrogate key to such a table just adds overhead, although that could be useful >in case specific rows need

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:32 PM, marcelo wrote: > What´s the question? > > ​Whether the OP, who hasn't come back, knew they were starting a flame war by asking this question... There is no context-less "right place" to place validation logic, nor are the various

Re: How to perform PITR when all of the logs won't fit on the drive

On Thu, 2018-03-01 at 17:28 -0500, Tony Sullivan wrote: Hello, I have a situation where something was deleted from a database that shouldn't have been so I am having to take a base backup and perform a point-in-time-recovery. The problem I have is that the decompressed WAL files will not fit on

Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 09:22 AM, Ron Johnson wrote: On 03/01/2018 11:03 AM, Melvin Davidson wrote: *Current* postgres=# \l List of databases     Name |  Owner   | Encoding | Collate   |    Ctype    | Access privileges

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 9:20 AM, Alban Hertroys wrote: > Not to mention that not all types of tables necessarily have suitable > candidates for a primary key. They do if they are in 1NF. ( no dupes alllowed ) > An example of such tables is a monetary transaction table that

Re: Enforce primary key on every table during dev?

On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson > wrote: On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys. -- Angular momentum makes the world go 'round. IMHO, business

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 5:22 PM, Steven Lembark wrote: > If you can say that "rows containing the same values are not > duplicates" Not a native speaker, but "Rows having the same values" seems to me the definition of duplicate ( ;-), J.K. ) > then you have a database that

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 8:52 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted > wrote: > >> Could you perhaps elaborate on how a surrogate key allows one to insert >> garbage into the table? I'm afraid I don't

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:06 PM, Daevor The Devoted wrote: > >> This seems like hierarchical data > ​Hence the "this is contrived" disclaimer - but if one allows for employee-department to be many-to-many, and thus requiring a joining table, this still applies even if the

Re: Version upgrade: is restoring the postgres database needed?

On Thu, Mar 1, 2018 at 12:22 PM, Ron Johnson wrote: > On 03/01/2018 11:03 AM, Melvin Davidson wrote: > > > > On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson > wrote: > >> On 03/01/2018 10:37 AM, Vick Khera wrote: >> >> On Thu, Mar 1, 2018 at 11:15 AM,

Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 11:46 AM, Adrian Klaver wrote: [snip] Hmmm.  I just looked at the script, and it says: $ pg_dumpall --schema-only > globals.sql That's not good. Well it would dump the globals, but also the schema definitions for all the objects in the cluster. Though at this point we are only

Re: How to avoid trailing zero (after decimal point) for numeric type column

On Wed, Feb 28, 2018 at 1:33 PM, pkashimalla wrote: ... > I did insertion from java program with below code snippet > > Double object = 10.0; > String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES > (?)"; > selectPrepareStmt.setObject(1,

Re: Posgresql Log: lots of parse statements

On Thursday, March 1, 2018, Vikas Sharma wrote: > Thanks David, > > But why are there so many parse statement occurances for one query? Does > postgres parse the statement everytime before execution or parse the query > only first time it is loaded in memory and reuse the

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson wrote: > On 03/01/2018 11:47 AM, Daevor The Devoted wrote: > > > On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: > >> >> >Adding a surrogate key to such a table just adds overhead, although that >>

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson wrote: > On 03/01/2018 12:32 PM, Daevor The Devoted wrote: > > > > On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson wrote: > >> >> On 03/01/2018 11:47 AM, Daevor The Devoted wrote: >> >> >> On Thu, Mar 1, 2018

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:00 , Ron Johnson wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson > wrote: On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM,

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar wrote: > > >Adding a surrogate key to such a table just adds overhead, although that > could be useful > >in case specific rows need updating or deleting without also modifying > the other rows with > >that same data -

Re: Enforce primary key on every table during dev?

On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: >Adding a surrogate key to such a table just adds overhead, although that could be useful >in case specific rows need

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson wrote: > Why have the overhead of a second unique index? If it's "ease of joins", > then I agree with Francisco Olarte and use the business logic keys in your > joins even though it's a bit of extra work. > ​The strongest case,

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:21 , Ron Johnson wrote: On 03/01/2018 02:08 PM, marcelo wrote: On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same

Re: Posgresql Log: lots of parse statements

Thanks David, But why are there so many parse statement occurances for one query? Does postgres parse the statement everytime before execution or parse the query only first time it is loaded in memory and reuse the same parsed plan until it ages out of memory?. In the log I can see these parse

Re: Posgresql Log: lots of parse statements

Vikas Sharma writes: > But why are there so many parse statement occurances for one query? A "parse" log entry is recorded when the client sends a Parse protocol message. So the answer to that question needs to be sought in your client application's logic. > In the log I

Re: Version upgrade: is restoring the postgres database needed?

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson wrote: > Good. What, then, have I forgotten to restore such that the "Access > privileges" are showing on my current 9.2 servers, but not on the > newly-restored 9.6.6 server? > > *Current* > postgres=# \l >

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys. -- Angular momentum

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted wrote: > Could you perhaps elaborate on how a surrogate key allows one to insert > garbage into the table? I'm afraid I don't quite get what you're saying. > ​A bit contrived but it makes the point:​ *Company:* C1 (id c1)

Re: Enforce primary key on every table during dev?

On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower wrote: > On 02/03/18 06:47, Daevor The Devoted wrote: > >> >> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > > wrote: >> >> >> >Adding a surrogate key to such

Flyway and postgree multiple developers

Hi, i added question to stackoverflow. Can you help me and answer there about my problem? https://stackoverflow.com/questions/49063257/multiple-developers-working-on-flyway-and-git thank you ! Jacek

Re: Version upgrade: is restoring the postgres database needed?

On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson wrote: > No, I do: > > $ pg_dump -Fc PROD > PROD.pgdump > $ pg_dump --globals-only postgres > globals.sql > $ pg_dump -Fc postgres > postgres.pgdump > > That's how I back them up as well. You are correct that all you need to do

Re: Enforce primary key on every table during dev?

On 1 March 2018 at 17:22, Steven Lembark wrote: > >> On 03/01/2018 02:20 AM, Alban Hertroys wrote: >> [snip] >> > Not to mention that not all types of tables necessarily have >> > suitable candidates for a primary key. You could add a surrogate >> > key based on a serial

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys wrote: > On 1 March 2018 at 17:22, Steven Lembark wrote: > > > >> On 03/01/2018 02:20 AM, Alban Hertroys wrote: > >> [snip] > >> > Not to mention that not all types of tables necessarily have > >> >

Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 10:37 AM, Vick Khera wrote: On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson > wrote: No, I do: $ pg_dump -Fc PROD > PROD.pgdump $ pg_dump --globals-only postgres > globals.sql $ pg_dump -Fc postgres >

Re: Version upgrade: is restoring the postgres database needed?

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson wrote: > On 03/01/2018 10:37 AM, Vick Khera wrote: > > On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson > wrote: > >> No, I do: >> >> $ pg_dump -Fc PROD > PROD.pgdump >> $ pg_dump --globals-only postgres >

Re: Enforce primary key on every table during dev?

(Melvin's mail doesn't quote properly; I've tried to fix it but may have broken something) > On Mar 1, 2018, at 8:50 AM, Melvin Davidson wrote: > > > On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys wrote: > > >> On 1 March 2018 at 17:22, Steven

Re: Enforce primary key on every table during dev?

On 03/01/2018 11:07 AM, Steve Atkins wrote: [snip] "Every table should have a primary key, whether natural or surrogate" is a great guideline, and everyone should follow it until they understand when they shouldn't. Most people think they know, but they don't. -- Angular momentum makes the

Re: Enforce primary key on every table during dev?

Melvin: On Thu, Mar 1, 2018 at 1:47 AM, Melvin Davidson wrote: > But this begs the question, > why are "developers" allowed to design database tables? That should be the > job of the DBA! That's the DBA wearing her developer hat. ( I agree with the spirit ) Francisco