Re: Enforce primary key on every table during dev?

[Alban Hertroys]

> 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 the developers and 
> explain to them 
> WHY the policy of enforcing a primary key is important. Also, explain the 
> purpose of
> primary keys and why it is not always suitable to just use an integer or 
> serial as the key,
> but rather why natural unique (even multi column) keys are better. But this 
> begs the question, 
> why are "developers" allowed to design database tables? That should be the 
> job of the DBA! At
> the very minimum, the DBA should be reviewing and have the authority to 
> approve of disapprove 
> of table/schema designs/changes .

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 primary key.

An example of such tables is a monetary transaction table that contains records 
for deposits and withdrawals to accounts. It will have lots of foreign key 
references to other tables, but rows containing the same values are probably 
not duplicates.
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 deletes are absolutely 
forbidden - corrections happen by inserting rows with an opposite transaction.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 primary key.

An example of such tables is a monetary transaction table that contains records 
for deposits and withdrawals to accounts. It will have lots of foreign key 
references to other tables, but rows containing the same values are probably 
not duplicates.
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 deletes are absolutely 
forbidden - corrections happen by inserting rows with an opposite transaction.


Wouldn't the natural pk of such a table be timestamp+seqno, just as the 
natural pk of a transaction_detail table be transaction_no+seqno?


--
Angular momentum makes the world go 'round.

Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

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?

[Rakesh Kumar]

>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 deletes are absolutely forbidden - corrections happen by 
>inserting rows with 
>an opposite transaction.

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 join it with child tables.

Custom ranking function for full text search

[Kiran Krishnamachari]

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?

[Melvin Davidson]

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 databases.)
>
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>
>is restoring the postgres database needed?

That would depend on how you did the dump. If you did a_complete pg_dumpall
(did not use -g or any other limiting flags), then all roles and databases
are contained
in the output file created. NOTE: restoring from the dumped file will
require rebuilding all indexes, because indexes are not dumped.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Enforce primary key on every table during dev?

[bto...@computer.org]

- 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 have a primary key.  This can't
> > be done with event triggers as far as I can see, because it is quite
> > legitimate to do:
> >
> > BEGIN;
> > CREATE TABLE foo (id int);
> > ALTER TABLE foo ADD PRIMARY KEY (id);
> > COMMIT;
> >
> > It would be nice to have some kind of "deferrable event trigger" or some
> > way to enforce that no transaction commits which added a table without a
> > primary key.
> >
> 
> 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. Using triggers in this way often leads to unexpected
> behaviour and difficult to identify bugs. The policy is a management
> policy and probably should be dealt with via management channels rather
> than technical ones. Besides, the likely outcome will be your developers
> will just adopt the practice of adding a serial column to every table,
> which in itself doesn't really add any value.

I concur with other respondents that suggest this is more of a policy issue. In 
fact, you yourself identify it right there in the first sentence as a policy 
issue! 

One tool that changed my life (as a PostgreSQL enthusiast) forever is David 
Wheeler's pgTAP (http://pgtap.org/) tool. It includes a suite of functionality 
to assess the database schema via automated testing. Part of a rigorous 
development environment might include using this tool so that any 
application/database changes be driven by tests, and then your code review 
process would assure that the appropriate tests are added to the pgTAP script 
to confirm that changes meet a policy standard such as what you are demanding. 
I can't imagine doing PostgreSQL development without it now.

Same guy also produced a related tool called Sqitch (http://sqitch.org/) for 
data base change management. Use these tools together, so that before a 
developer is allowed to check in a feature branch, your teams' code review 
process maintains rigorous oversight of modifications.

-- B

Posgresql Log: lots of parse statements

[Vikas Sharma]

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:  duration: 62952.558 ms  parse :
select this_.busi_type as business_type from tablea where this_.busi_id =
$1 and this_.busi_date = $2
2018-01-21 14:05:00 GMT LOG:  duration: 62952.558 ms  parse :
select this_.busi_type as business_type from tablea where this_.busi_id =
$1 and this_.busi_date = $2
2018-01-21 14:17:12 GMT LOG:  duration: 62952.558 ms  parse :
select this_.busi_type as business_type from tablea where this_.busi_id =
$1 and this_.busi_date = $2
2018-01-21 14:34:08 GMT LOG:  duration: 62952.558 ms  parse :
select this_.busi_type as business_type from tablea where this_.busi_id =
$1 and this_.busi_date = $2

I don't see this query to appear as execute, so how can find out how much
time it's taking to execute? does this parse timing includes execute also?

Best Regards
Vikas

Re: Posgresql Log: lots of parse statements

[David G. Johnston]

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 only as
> "parse" while others appear as execute.
> ​[...]​
> I don't see this query to appear as execute, so how can find out how much
> time it's taking to execute? does this parse timing includes execute also?
>

​The most likely explanation is that executing the already parsed query
takes less than 15 seconds and so doesn't appear due to your filter.

David J.

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

[Ron Johnson]

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 new server, that,
naturally, has it's own new postgres, template0 and template1 databases.)


>is restoring the postgres database needed?

That would depend on how you did the dump. If you did a_complete 
pg_dumpall (did not use -g or any other limiting flags), then all roles 
and databases are contained
in the output file created. NOTE: restoring from the dumped file will 
require rebuilding all indexes, because indexes are not dumped.


No, I do:

$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dump --globals-only postgres > globals.sql
$ pg_dump -Fc postgres > postgres.pgdump



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Steven Lembark]

> 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 primary key.
> >
> > An example of such tables is a monetary transaction table that
> > contains records for deposits and withdrawals to accounts. It will
> > have lots of foreign key references to other tables, but rows
> > containing the same values are probably not duplicates. 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 deletes are absolutely forbidden -
> > corrections happen by inserting rows with an opposite transaction.  
> 
> Wouldn't the natural pk of such a table be timestamp+seqno, just as
> the natural pk of a transaction_detail table be transaction_no+seqno?

Start with Date's notion that a database exists to correclty represent
data about the real world. Storing un-identified data breaks this 
since we have no idea what the data means or have any good way of 
getting it back out. Net result is that any workable relational 
database will have at least one candidate key for any table in it.

If you can say that "rows containing the same values are not
duplicates" then you have a database that cannot be queried, audited,
or managed effectively. The problem is that you cannot identify the 
rows, and thus cannot select related ones, update them (e.g., to 
expire outdated records), or validate the content against any external
values (e.g., audit POS tapes using the database).

In the case of a monitary transaction you need a transaction
table, which will have most of the FK's, and a ledger for the 
transaction amounts.

A minimum candidate key for the transaction table would be account, 
timestamp, authorizing customer id, and channel. This allows two 
people to, say, make deposits at the same time or the same authorizing 
account (e.g., a credit card number) to be processed at the same time 
in two places.

The data for a transaction would include things like the final status, 
in-house authorizing agent, completion time.

The ledger entries would include the transaction SK, sequence within
the transaction, amount, and account. The ledger's candidate key is 
a transaction SK + sequence number -- the amount and account don't 
work because you may end up, say, making multiple deposits of $10
to your checking account on the same transaction.

The ledger's sequence value can be problematic, requiring external
code or moderately messy triggers to manage. Timestamps don't always
work and are subject to clock-skew. One way to avoid this is require
that a single transaction contain only unique amounts and accounts.
At that point the ledger becomes a degenerate table of  transaction id, 
amount, account (i.e., the entire table is nothing but a unique index).

This might require generating multiple database transactions for a
single external process (e.g., a customer walking up to the teller)
but does simplify processing quite a bit.

In both cases, having an SK on the ledger is useful for audit queries,
which might have to process a large number of ledger entries in code.
Extracting the ledger SK's in one query and walking down them using
a unique index can be more effecient than having to extract the values.

Either way, you can identify all of the transactions as unique and 
all of the ledger entries for that transaction. At that point the 
database can be queried for data, updated as necessary, audited 
against external data.

If you have a design with un-identified data it means that you havn't
normalized it properly: something is missing from the table with 
un-identifiable rows.

-- 
Steven Lembark   1505 National Ave
Workhorse Computing Rockford, IL 61103
lemb...@wrkhors.com+1 888 359 3508

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

[Vick Khera]

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
is restore the globals.sql, then each "pgdump" file individually. Just
ignore the warning when it tries to restore your initial postgres
superuser, since it was created by the initdb already.

You probably don't need the "postgres" db at all, since it is just there to
allow the client to connect to something on initial install. Normally you
don't use it in production.

Re: Enforce primary key on every table during dev?

[Alban Hertroys]

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 type, but in such cases that may not serve
>> > any purpose other than to have some arbitrary primary key.
>> >
>> > An example of such tables is a monetary transaction table that
>> > contains records for deposits and withdrawals to accounts.

(...)

> Start with Date's notion that a database exists to correclty represent
> data about the real world. Storing un-identified data breaks this
> since we have no idea what the data means or have any good way of
> getting it back out. Net result is that any workable relational
> database will have at least one candidate key for any table in it.

(...)

> If you have a design with un-identified data it means that you havn't
> normalized it properly: something is missing from the table with
> un-identifiable rows.

While that holds true for a relational model, in reporting for
example, it is common practice to denormalize data without a
requirement to be able to identify a single record. The use case for
such tables is providing quick aggregates on the data. Often this
deals with derived data. It's not that uncommon to not have a primary
or even a uniquely identifiable key on such tables.

I do not disagree that having a primary key on a table is a bad thing,
but I do disagree that a primary key is a requirement for all tables.

More generally: For every rule there are exceptions. Even for this one.
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

[Ron Johnson]

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 > postgres.pgdump


That's how I back them up as well. You are correct that all you need to do 
is restore the globals.sql, then each "pgdump" file individually. Just 
ignore the warning when it tries to restore your initial postgres 
superuser, since it was created by the initdb already.


You probably don't need the "postgres" db at all, since it is just there 
to allow the client to connect to something on initial install. Normally 
you don't use it in production.


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
   List of databases
    Name |  Owner   | Encoding |   Collate   | Ctype    |   Access 
privileges

-+--+--+-+-+---
CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS
CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS
CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS

*Newly restored*
postgres=# \l
   List of databases
    Name |  Owner   | Encoding |   Collate   | Ctype    |   Access 
privileges

-+--+--+-+-+---
CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Melvin Davidson]

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
> >> > 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 primary key.
> >> >
> >> > An example of such tables is a monetary transaction table that
> >> > contains records for deposits and withdrawals to accounts.
>
> (...)
>
> > Start with Date's notion that a database exists to correclty represent
> > data about the real world. Storing un-identified data breaks this
> > since we have no idea what the data means or have any good way of
> > getting it back out. Net result is that any workable relational
> > database will have at least one candidate key for any table in it.
>
> (...)
>
> > If you have a design with un-identified data it means that you havn't
> > normalized it properly: something is missing from the table with
> > un-identifiable rows.
>
> While that holds true for a relational model, in reporting for
> example, it is common practice to denormalize data without a
> requirement to be able to identify a single record. The use case for
> such tables is providing quick aggregates on the data. Often this
> deals with derived data. It's not that uncommon to not have a primary
> or even a uniquely identifiable key on such tables.
>
> I do not disagree that having a primary key on a table is a bad thing,
> but I do disagree that a primary key is a requirement for all tables.
>
> More generally: For every rule there are exceptions. Even for this one.
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>
>



*> it is common practice to denormalize data without a>requirement to be
able to identify a single record *

*You may perceive that to be "common practice", but in reality it is not,
and in fact a bad one. As was previously stated, PosgreSQL is a
_relational_ database,*
*and breaking that premise will eventually land you in very big trouble.
There is no solid reason not to a primary key for every table.*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

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

[Melvin Davidson]

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 > 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
> is restore the globals.sql, then each "pgdump" file individually. Just
> ignore the warning when it tries to restore your initial postgres
> superuser, since it was created by the initdb already.
>
> You probably don't need the "postgres" db at all, since it is just there
> to allow the client to connect to something on initial install. Normally
> you don't use it in production.
>
>
> 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
>List of databases
> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges
> -+--+--+-+--
> ---+---
> CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
> CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
> CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
>
> *Newly restored*
> postgres=# \l
>List of databases
> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges
> -+--+--+-+--
> ---+---
> CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>
>
> --
> Angular momentum makes the world go 'round.
>





*>$ pg_dump -Fc PROD > PROD.pgdump >$ pg_dump --globals-only postgres >
globals.sql >$ pg_dump -Fc postgres > postgres.pgdump *

*The last I looked, pg_dump does not have a "--globals-only"*








*Did you mean? $ pg_dump -Fc PROD > PROD.pgdump $ pg_dumpall --globals-only
postgres > globals.sqlOR $ pg_dumpall -g > globals.sql $ pg_dump -Fc
postgres > postgres.pgdump *

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Enforce primary key on every table during dev?

[Steve Atkins]

(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 Lembark  wrote:
>>> If you have a design with un-identified data it means that you havn't
>>> normalized it properly: something is missing from the table with
>>> un-identifiable rows.
>> 
>> While that holds true for a relational model, in reporting for
>> example, it is common practice to denormalize data without a
>> requirement to be able to identify a single record. The use case for
>> such tables is providing quick aggregates on the data. Often this
>> deals with derived data. It's not that uncommon to not have a primary
>> or even a uniquely identifiable key on such tables.
>> 
>> I do not disagree that having a primary key on a table is a bad thing,
>> but I do disagree that a primary key is a requirement for all tables.
>> 
>> More generally: For every rule there are exceptions. Even for this one.
> 
> You may perceive that to be "common practice", but in reality it is not, and 
> in fact a bad one. As was previously stated, PosgreSQL is a _relational_ 
> database,
> and breaking that premise will eventually land you in very big trouble. There 
> is no solid reason not to a primary key for every table.

Sure there is. It's an additional index and significant additional insert / 
update overhead.
If you're never going to retrieve single rows, nor join in such a way that 
uniqueness
on this side is required there's no need for a unique identifier.

It's a rare case that you won't want a primary key, and I'll often add 
a surrogate one for convenience even when it's not actually needed,
but there are cases where it's appropriate not to have one, even in
OLTP work. Log tables, for example.

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

More generally: For every rule there are exceptions. Even for this one.

Cheers,
  Steve

Re: Posgresql Log: lots of parse statements

[Vikas Sharma]

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 statement occurances about 400 times in a
day and everytime taking longer than 15 secs.

Regards
Vikas

On Mar 1, 2018 15:30, "David G. Johnston" 
wrote:

> 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 only as
>> "parse" while others appear as execute.
>> ​[...]​
>> I don't see this query to appear as execute, so how can find out how much
>> time it's taking to execute? does this parse timing includes execute also?
>>
>
> ​The most likely explanation is that executing the already parsed query
> takes less than 15 seconds and so doesn't appear due to your filter.
>
> David J.
>
>

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

[Ron Johnson]

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, Ron Johnson mailto:ron.l.john...@cox.net>> 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 is restore the globals.sql, then each "pgdump" file
individually. Just ignore the warning when it tries to restore your
initial postgres superuser, since it was created by the initdb already.

You probably don't need the "postgres" db at all, since it is just
there to allow the client to connect to something on initial install.
Normally you don't use it in production.


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
 List of databases
    Name |  Owner   | Encoding | Collate   |    Ctype    |  
Access privileges

-+--+--+-+-+---
CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | | app_user=CTc/CSS
CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | | app_user=CTc/CSS
CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | | app_user=CTc/CSS

*Newly restored*
postgres=# \l
 List of databases
    Name |  Owner   | Encoding | Collate   |    Ctype    |  
Access privileges

-+--+--+-+-+---
CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |


-- 
Angular momentum makes the world go 'round.



*>$ pg_dump -Fc PROD > PROD.pgdump
>$ pg_dump --globals-only postgres > globals.sql
>$ pg_dump -Fc postgres > postgres.pgdump

*
*The last I looked, pg_dump does not have a "--globals-only"
*
*Did you mean?
$ pg_dump -Fc PROD > PROD.pgdump

$ pg_dumpall --globals-only postgres > globals.sql
OR
$ pg_dumpall -g > globals.sql

$ pg_dump -Fc postgres > postgres.pgdump*


Hmmm.  I just looked at the script, and it says:

$ pg_dumpall --schema-only > globals.sql

That's not good.


--
Angular momentum makes the world go 'round.

Re: Posgresql Log: lots of parse statements

[Tom Lane]

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 can see these parse statement occurances about 400 times in a
> day and everytime taking longer than 15 secs.

That seems like a mighty long time for parse analysis.  Maybe you're
having difficulties with something taking exclusive locks, thereby
blocking other queries?

regards, tom lane

Re: Posgresql Log: lots of parse statements

[David G. Johnston]

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 same parsed plan until
> it ages out of memory?.
>
> In the log I can see these parse statement occurances about 400 times in a
> day and everytime taking longer than 15 secs.
>

Please don't top-post replies.

Normally "parse unnamed" happens when client interfaces execute prepared
statements using parse-bind-execute protocol.  If you want to reduce the
number you will need to change your application.  As for the time it takes
its impossible to say without knowing more about the environment and query.

David J.

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

[Melvin Davidson]

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, 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 is restore the globals.sql, then each "pgdump" file individually. Just
>> ignore the warning when it tries to restore your initial postgres
>> superuser, since it was created by the initdb already.
>>
>> You probably don't need the "postgres" db at all, since it is just there
>> to allow the client to connect to something on initial install. Normally
>> you don't use it in production.
>>
>>
>> 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
>>List of databases
>> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
>> privileges
>> -+--+--+-+--
>> ---+---
>> CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> CSS=CTc/CSS  +
>>  |  |  | | |
>> =Tc/CSS  +
>>  |  |  | | |
>> app_user=CTc/CSS
>> CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> CSS=CTc/CSS  +
>>  |  |  | | |
>> =Tc/CSS  +
>>  |  |  | | |
>> app_user=CTc/CSS
>> CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> CSS=CTc/CSS  +
>>  |  |  | | |
>> =Tc/CSS  +
>>  |  |  | | |
>> app_user=CTc/CSS
>>
>> *Newly restored*
>> postgres=# \l
>>List of databases
>> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
>> privileges
>> -+--+--+-+--
>> ---+---
>> CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>
>
>
>
>
> *>$ pg_dump -Fc PROD > PROD.pgdump >$ pg_dump --globals-only postgres >
> globals.sql >$ pg_dump -Fc postgres > postgres.pgdump *
>
> *The last I looked, pg_dump does not have a "--globals-only" *
>
>
>
>
>
>
>
> *Did you mean? $ pg_dump -Fc PROD > PROD.pgdump $ pg_dumpall
> --globals-only postgres > globals.sql OR $ pg_dumpall -g > globals.sql $
> pg_dump -Fc postgres > postgres.pgdump*
>
>
> Hmmm.  I just looked at the script, and it says:
>
> $ pg_dumpall --schema-only > globals.sql
>
> That's not good.
>
>
> --
> Angular momentum makes the world go 'round.
>








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

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 world go 'round.

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

[Adrian Klaver]

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


-+--+--+-+-+---
CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | |
app_user=CTc/CSS
CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | |
app_user=CTc/CSS
CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSS=CTc/CSS  +
 |  | | | |
=Tc/CSS  +
 |  | | | |
app_user=CTc/CSS

*Newly restored*
postgres=# \l
 List of databases
    Name |  Owner   | Encoding | Collate   |    Ctype    |  
Access privileges


-+--+--+-+-+---
CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |


-- 
Angular momentum makes the world go 'round.



*>$ pg_dump -Fc PROD > PROD.pgdump
>$ pg_dump --globals-only postgres > globals.sql
>$ pg_dump -Fc postgres > postgres.pgdump

*
*The last I looked, pg_dump does not have a "--globals-only"
*
*Did you mean?
$ pg_dump -Fc PROD > PROD.pgdump

$ pg_dumpall --globals-only postgres > globals.sql
OR
$ pg_dumpall -g > globals.sql

$ pg_dump -Fc postgres > postgres.pgdump*


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 half way 
through the process. What is you restore procedure?





--
Angular momentum makes the world go 'round.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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 - normally, only insertions and selections happen on such
> tables though,
> >and updates or deletes are absolutely forbidden - corrections happen by
> inserting rows with
> >an opposite transaction.
>
> 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 join it with child tables.
>
>
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" multiple columns), but guess what, business
logic changes, and then you're screwed! So using a primary key whose sole
purpose is to be a primary key makes perfect sense to me.

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

[Ron Johnson]

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 piping it to globals.sql.  That's deceptive.

--
Angular momentum makes the world go 'round.

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

[Ron Johnson]

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 half way 
through the process. What is you restore procedure?


$ psql < globals.sql
$ pg_restore --clean --create --if-exists --exit-on-error --jobs=2 
${SRC}/${DB}.pgdump



--
Angular momentum makes the world go 'round.

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

[Francisco Olarte]

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, object,Types.NUMERIC);
> int count = selectPrepareStmt.executeUpdate();

That's incomplete. But why are you using Double to bind a NUMERIC?
IIRC BigDecimal seems to me the natural java conunterpart to it.

>
> it inserted like this.
> /
> select id from blob_test_table;
>
> id
> numeric
> -
> 10.0/
>
>
> In this case, when a decimal point is equal to 0 then,  I don't want to see
> the precision and the value in the column should just 10

And the driver is supposed to know this how?



> And If I execute code,
>
> Double object = 10.5801
> String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES
> (?)";
> selectPrepareStmt.setObject(1, object,Types.NUMERIC);
> int count = selectPrepareStmt.executeUpdate();
>
> Now ,the value in the column should be 10.5801 as the precision is greater
> than ZERO

Doubles do not have precision ( well, they have, double precision ).
Also note doubles are binary, and that 10.5801 is not bounded in
binary. This is perl but you can see the thing:

$ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 10.5801'
10.580100 10.580099983800 0x1.52902de00d1b7p+3
0x1.52902de00d1b7000p+3

Note how the decimal varies with the requested precision, as 10.5801
cannot be represented exactly in binary, and how the hex
representation does not vary ( because hexadecimal has an exact binary
representation ).

> Because of this, the migrated data (from Oracle) is without PRECISION ZERO
> and the new data which is being inserted is with PRECISION ZERO.

I think you are hitting an implementation-defined behaviour of the
driver. To convert a double to a numeric you must select a precision,
and it seems oracle and postgres do it differently. I would try
BigDecimal which has less uncertainity.

> Oracle's NUMBER column type is handling it as I expected.

Oracle behaves as you are used to, and so is what you expect.

> I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL

Probably using a BigDecimal would do the trick, as you can convert sql
numeric => java bigdecimal => sql numeric without losing info, but not
with Doubles. Bear in mind numeric is decimal, double is binary, and
one thing such simpel looking as 0.1 does not have an exact binary
representation:

$ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 0.1'
0.10 0.1555 0x1.ap-4 0x1.a000p-4

See the trailing 555 in the second? or the continous 1.(9) fraction
rounded to a in excess on the least significant place in hex?

In numeric you can hace 1.0, 1.0, 1.1
and 0.99, but in doubles they all map to
1.0

Francisco Olarte.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 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 deletes are absolutely forbidden - corrections happen
by inserting rows with
>an opposite transaction.

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 join it with child tables.


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" multiple columns), but guess what, business 
logic changes, and then you're screwed!


And so you drop the existing index and build a new one.  I've done it 
before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key makes 
perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so 
purposelessly arbitrary, and allow you to insert garbage into the table.


--
Angular momentum makes the world go 'round.

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

[Vick Khera]

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
>List of databases
> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges
> -+--+--+-+--
> ---+---
> CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
> CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
> CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS  +
>  |  |  | | |
> =Tc/CSS  +
>  |  |  | | |
> app_user=CTc/CSS
>
> *Newly restored*
> postgres=# \l
>List of databases
> Name |  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges
> -+--+--+-+--
> ---+---
> CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>
>
FWIW none of my databases other than template0 and template1 have anything
listed for Access privileges like that. I'm not even sure exactly what
those are for :(

Any privileges from REVOKEs and GRANTs will be in the dump, so those are
restored.

Re: Enforce primary key on every table during dev?

[Francisco Olarte]

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

Re: Enforce primary key on every table during dev?

[Martin Moore]

 

On 03/01/2018 11:47 AM, Daevor The Devoted wrote:

>I can't stand synthetic keys.  By their very nature, they're so purposelessly 
>arbitrary, and allow you to insert garbage into the >table.

How does not having a ‘real’ PK allow you to insert garbage and a ‘real’ one 
prevent garbage? 

If there’s no natural PK, at least a synthetic one will allow very quick record 
identification if used.

 

 

Martin.

 

Re: Enforce primary key on every table during dev?

[Francisco Olarte]

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 contains 
> records for deposits and withdrawals to accounts. It will have lots of 
> foreign key references to other tables, but rows containing the same values 
> are probably not duplicates.

That's a bad example. They would normally have a transaction id, or a
timestamp, or a sequence counter. PKs can expand all non-nullable
columns. You could try to come with a real example, but all the times
I've found these in one of my dessigns is because I didn't correctly
model the "real world".

> 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 deletes are 
> absolutely forbidden - corrections happen by inserting rows with an opposite 
> transaction.

And normally you would need to pinpoint an individual transaction for
selection, hard to do if you do not have a pk.

Francisco Olarte.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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
>> 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 deletes are absolutely forbidden - corrections happen by
>> inserting rows with
>> >an opposite transaction.
>>
>> 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 join it with child tables.
>>
>>
> 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" multiple columns), but guess what, business
> logic changes, and then you're screwed!
>
>
> And so you drop the existing index and build a new one.  I've done it
> before, and I'll do it again.
>
> So using a primary key whose sole purpose is to be a primary key makes
> perfect sense to me.
>
>
> I can't stand synthetic keys.  By their very nature, they're so
> purposelessly arbitrary, and allow you to insert garbage into the table.
>
> --
> Angular momentum makes the world go 'round.
>

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.

Re: Enforce primary key on every table during dev?

[Francisco Olarte]

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 join it with child tables.

It does, but many times useful, let me explain:

table currencies ( code text, description text), primary key code (
i.e. "USD", "US Dollars" )
table sellers ( currency text, id number, .), primary key
(currency, id), foreign key currency references currencies
table buyers ( currency text, id number, .), primary key
(currency, id)  foreign key currency references currencies
table transactions ( currency text, seller_id number, buyer_id number,
trans_id number )
   primery key trans_id,
   foreign key currency references currencies,
   foreign key (currency, seller_id ) references sellers,
   foreign key (currency, buyer_id ) references buyers

This is a bit unwieldy, but it expreses my example constraint, buyers
can only buy from a seller with the same currency, there is no way to
insert a cross-currency transaction.

Of course, 3 femtoseconds after deployment the PHB will decide you can
do cross-currency sales.

Francisco Olarte.

Re: Enforce primary key on every table during dev?

[Francisco Olarte]

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 cannot be queried, audited,
> or managed effectively. The problem is that you cannot identify the
> rows, and thus cannot select related ones, update them (e.g., to
> expire outdated records), or validate the content against any external
> values (e.g., audit POS tapes using the database).

Good point. All the times I've found myself with complete duplicates
allowed I've alwasy found the correct model is no duplicates + count
field ( with possible splits as you pointed below ). I would not have
a "marbles" table with (red, red, blue, white, red, white), I would
switch it to red=3, blue=1, white=2.

Francisco Olarte.

Re: Enforce primary key on every table during dev?

[David G. Johnston]

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)
C2 (id c2)

*Department:*
C1-D1 (id d1)
C1-D2 (id d2)
C2-D1 (id d3)
C2-D2 (id d4)

*Employee:*
C1-E1 (id e1)
C1-E2 (id e2)
C2-E1 (id e3)
C2-E2 (id e4)

*​Employee-Department​:*
e1-d1
e2-d2
e3-d2
e4-d4

The pair e3-d2 is invalid because e3 belongs to company c2 while d2 belongs
to company c1 - but we've hidden the knowledge ​of c# behind the surrogate
key and now we can insert garbage into employee-department.

David J.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 mailto:rakeshkumar...@aol.com>> 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 - normally, only insertions and selections happen
on such tables though,
>and updates or deletes are absolutely forbidden - corrections
happen by inserting rows with
>an opposite transaction.

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 join it with child tables.


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" multiple columns), but guess what,
business logic changes, and then you're screwed!


And so you drop the existing index and build a new one. I've done it
before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key
makes perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so
purposelessly arbitrary, and allow you to insert garbage into the table.


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.


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.

Re: Enforce primary key on every table during dev?

[Melvin Davidson]

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 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 - normally, only insertions and selections happen on
>>> such tables though,
>>> >and updates or deletes are absolutely forbidden - corrections happen by
>>> inserting rows with
>>> >an opposite transaction.
>>>
>>> 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 join it with child tables.
>>>
>>>
>> 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" multiple columns), but guess what, business
>> logic changes, and then you're screwed!
>>
>>
>> And so you drop the existing index and build a new one.  I've done it
>> before, and I'll do it again.
>>
>> So using a primary key whose sole purpose is to be a primary key makes
>> perfect sense to me.
>>
>>
>> I can't stand synthetic keys.  By their very nature, they're so
>> purposelessly arbitrary, and allow you to insert garbage into the table.
>>
>
> 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.
>
>
> 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.
>

* If you are going to go to the trouble of having a surrogate/synthetic
key, then you may as well have a primary key , which is much better. *

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Enforce primary key on every table during dev?

[marcelo]

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, Rakesh Kumar
mailto:rakeshkumar...@aol.com>> 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 - normally, only insertions and selections
happen on such tables though,
>and updates or deletes are absolutely forbidden -
corrections happen by inserting rows with
>an opposite transaction.

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 join it with child tables.


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" multiple columns), but
guess what, business logic changes, and then you're screwed!


And so you drop the existing index and build a new one.  I've
done it before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key
makes perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so
purposelessly arbitrary, and allow you to insert garbage into the
table.


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.


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 logic can and must preclude "garbage insertion". Except 
you are inserting data directly to database using SQL, any n-tier 
architecture will be checking data validity.



---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 the
same "business data" multiple times with different synthetic keys.



-- 
Angular momentum makes the world go 'round.



*If you are going to go to the trouble of having a surrogate/synthetic 
key, then you may as well have a primary key , which is much better. *


I completely agree.

--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 logic can and must preclude "garbage insertion". Except you 
are inserting data directly to database using SQL, any n-tier architecture 
will be checking data validity.


Any n-tier architecture that's bug-free.

--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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 quite get what you're saying.
>>
>
> ​A bit contrived but it makes the point:​
>
> *Company:*
> C1 (id c1)
> C2 (id c2)
>
> *Department:*
> C1-D1 (id d1)
> C1-D2 (id d2)
> C2-D1 (id d3)
> C2-D2 (id d4)
>
> *Employee:*
> C1-E1 (id e1)
> C1-E2 (id e2)
> C2-E1 (id e3)
> C2-E2 (id e4)
>
> *​Employee-Department​:*
> e1-d1
> e2-d2
> e3-d2
> e4-d4
>
> The pair e3-d2 is invalid because e3 belongs to company c2 while d2
> belongs to company c1 - but we've hidden the knowledge ​of c# behind the
> surrogate key and now we can insert garbage into employee-department.
>
> David J.
>
>
This seems like hierarchical data, where employee's parent should be
department, and department's parent is company. So it wouldn't be possible
to "insert garbage" since Company is not stored in the Employee table, only
a reference to Department (and Company determined via Department). Isn't
that how normal hierarchical data works?

Re: Enforce primary key on every table during dev?

[marcelo]

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 makes the world go 'round.

IMHO, business logic can and must preclude "garbage insertion".
Except you are inserting data directly to database using SQL, any
n-tier architecture will be checking data validity.


Any n-tier architecture that's bug-free.


Do you know about unit testing?

---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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  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 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 deletes are absolutely forbidden - corrections happen by
>>> inserting rows with
>>> >an opposite transaction.
>>>
>>> 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 join it with child tables.
>>>
>>>
>> 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" multiple columns), but guess what, business
>> logic changes, and then you're screwed!
>>
>>
>> And so you drop the existing index and build a new one.  I've done it
>> before, and I'll do it again.
>>
>> So using a primary key whose sole purpose is to be a primary key makes
>> perfect sense to me.
>>
>>
>> I can't stand synthetic keys.  By their very nature, they're so
>> purposelessly arbitrary, and allow you to insert garbage into the table.
>>
>
> 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.
>
>
> 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.
>


That might be where we're talking past each other: I do not advocate for
the arbitrary primary key being the only unique index. Absolutely not.
Whatever the business rules say is unique must also have unique indexes. If
it's a business constraint on the data, it must be enforced in the DB (at
least, that's how I try to do things).

Re: Enforce primary key on every table during dev?

[David G. Johnston]

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 specific choice to nouns doesn't make sense.

David J.
​

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 "business data" multiple times with different synthetic keys.



--
Angular momentum makes the world go 'round.
IMHO, business logic can and must preclude "garbage insertion". Except 
you are inserting data directly to database using SQL, any n-tier 
architecture will be checking data validity.


Any n-tier architecture that's bug-free.


Do you know about unit testing?


Way Back When Dinosaurs Still Roamed The Earth and I first learned the 
trade, the focus was on proper design instead of throwing crud against the 
wall and hoping tests caught any bugs.  Because, of course, unit tests are 
only as good as you imagination in devising tests.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 mailto:ron.l.john...@cox.net>> wrote:


On 03/01/2018 11:47 AM, Daevor The Devoted wrote:


On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar
mailto:rakeshkumar...@aol.com>> 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 - normally, only insertions and selections
happen on such tables though,
>and updates or deletes are absolutely forbidden -
corrections happen by inserting rows with
>an opposite transaction.

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 join it with child tables.


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" multiple columns), but
guess what, business logic changes, and then you're screwed!


And so you drop the existing index and build a new one.  I've
done it before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key
makes perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so
purposelessly arbitrary, and allow you to insert garbage into the
table.


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.


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.




That might be where we're talking past each other: I do not advocate for 
the arbitrary primary key being the only unique index. Absolutely not. 
Whatever the business rules say is unique must also have unique indexes. 
If it's a business constraint on the data, it must be enforced in the DB 
(at least, that's how I try to do things).


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.


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[David G. Johnston]

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, for me, when a surrogate key is highly desirable is
when there is no truly natural key and the best key for the model is
potentially alterable.  Specific, the "name" of something.  If I add myself
to a database and make name unique, so David Johnston, then someone else
comes along with the same name and now I want to add the new person as, say
David A. Johnston AND rename my existing record to David G. Johnston.  I
keep the needed uniqueness ​and don't need to cobble together other data
elements.  Or, if I were to use email address as the key the same physical
entity can now change their address without me having to cascade update all
FK instances too.  Avoiding the FK cascade when enforcing a non-ideal PK is
a major good reason to assign a surrogate.

David J.

Re: Enforce primary key on every table during dev?

[marcelo]

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 "business data" multiple times with different synthetic 
keys.



--
Angular momentum makes the world go 'round.
IMHO, business logic can and must preclude "garbage insertion". 
Except you are inserting data directly to database using SQL, any 
n-tier architecture will be checking data validity.

bl
Any n-tier architecture that's bug-free.


Do you know about unit testing?


Way Back When Dinosaurs Still Roamed The Earth and I first learned the 
trade, the focus was on proper design instead of throwing crud against 
the wall and hoping tests caught any bugs.  Because, of course, unit 
tests are only as good as you imagination in devising tests.



So, you are fully convinced that there´s no bug free software... Same as 
I (and you) can code following the business rules, you (and me) can 
design unit tests not from "imagination" but from same rules.
Moreover: you can have a surrogate key (to speedup foreign keys) and 
simultaneously put a unique constraint on the columns requiring it. 
What´s the question?


---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Enforce primary key on every table during dev?

[David G. Johnston]

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 options mutually exclusive.

David J.
​

Re: Enforce primary key on every table during dev?

[Rakesh Kumar]

> 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" multiple columns), but guess what, business logic changes, and 
> then you're screwed!


> So using a primary key whose sole purpose is to be a primary key makes 
> perfect sense to me.

I did not get your point.  Can you explain why a change of business logic makes 
it difficult to change existing 
rows with surrogate key.

thanks.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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 business logic keys in your
>> joins even though it's a bit of extra work.
>>
>
> ​The strongest case, for me, when a surrogate key is highly desirable is
> when there is no truly natural key and the best key for the model is
> potentially alterable.  Specific, the "name" of something.  If I add myself
> to a database and make name unique, so David Johnston, then someone else
> comes along with the same name and now I want to add the new person as, say
> David A. Johnston AND rename my existing record to David G. Johnston.  I
> keep the needed uniqueness ​and don't need to cobble together other data
> elements.  Or, if I were to use email address as the key the same physical
> entity can now change their address without me having to cascade update all
> FK instances too.  Avoiding the FK cascade when enforcing a non-ideal PK is
> a major good reason to assign a surrogate.
>
> David J.
>
>
This is exactly my point: you cannot know when a Business Rule is going to
change. Consider, for example, your Social Security number (or ID number as
we call it in South Africa). This is unique, right?. Tomorrow, however,
data of people from multiple countries gets added to your DB, and BAM! that
ID number is suddenly no longer unique. Business Rules can and do change,
and we do not know what may change in the future. Hence, it is safest to
have the surrogate in place from the start, and avoid the potential
migraine later on.

Disclaimer: this is just my opinion based on my experience (and the pain I
had to go through when Business Rules changed). I have not done any
research or conducted any studies on this.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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 question...
>
> There is no context-less "right place" to place validation logic, nor are
> the various options mutually exclusive.
>
> David J.
> ​
>

This I can wholeheartedly agree with. And my apologies if I came across as
"flaming". Not my intention at all. I'm simply here to learn (and, well,
offer my opinion from time to time :) )

Re: Enforce primary key on every table during dev?

[marcelo]

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
logic keys in your joins even though it's a bit of extra work.


​The strongest case, for me, when a surrogate key is highly desirable 
is when there is no truly natural key and the best key for the model 
is potentially alterable. Specific, the "name" of something.  If I add 
myself to a database and make name unique, so David Johnston, then 
someone else comes along with the same name and now I want to add the 
new person as, say David A. Johnston AND rename my existing record to 
David G. Johnston.  I keep the needed uniqueness ​and don't need to 
cobble together other data elements.  Or, if I were to use email 
address as the key the same physical entity can now change their 
address without me having to cascade update all FK instances too. 
Avoiding the FK cascade when enforcing a non-ideal PK is a major good 
reason to assign a surrogate.


David J.


I suffered myself what David said as an example...


---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 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 logic can and must preclude "garbage insertion". Except 
you are inserting data directly to database using SQL, any n-tier 
architecture will be checking data validity.

bl
Any n-tier architecture that's bug-free.


Do you know about unit testing?


Way Back When Dinosaurs Still Roamed The Earth and I first learned the 
trade, the focus was on proper design instead of throwing crud against 
the wall and hoping tests caught any bugs.  Because, of course, unit 
tests are only as good as you imagination in devising tests.



So, you are fully convinced that there´s no bug free software... Same as I 
(and you) can code following the business rules, you (and me) can design 
unit tests not from "imagination" but from same rules.
Moreover: you can have a surrogate key (to speedup foreign keys) and 
simultaneously put a unique constraint on the columns requiring it. What´s 
the question?


Implementing tests to cover edge cases is much harder than implementing 
business rules in natural (and foreign) keys.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:44 PM, Daevor The Devoted wrote:



On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson mailto:ron.l.john...@cox.net>>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, for me, when a surrogate key is highly desirable
is when there is no truly natural key and the best key for the model
is potentially alterable.  Specific, the "name" of something.  If I
add myself to a database and make name unique, so David Johnston, then
someone else comes along with the same name and now I want to add the
new person as, say David A. Johnston AND rename my existing record to
David G. Johnston.  I keep the needed uniqueness ​and don't need to
cobble together other data elements.  Or, if I were to use email
address as the key the same physical entity can now change their
address without me having to cascade update all FK instances too.
Avoiding the FK cascade when enforcing a non-ideal PK is a major good
reason to assign a surrogate.

David J.


This is exactly my point: you cannot know when a Business Rule is going to 
change. Consider, for example, your Social Security number (or ID number 
as we call it in South Africa). This is unique, right?.


No, the SSN is not unique. 
https://www.computerworld.com/article/2552992/it-management/not-so-unique.html


Tomorrow, however, data of people from multiple countries gets added to 
your DB, and BAM! that ID number is suddenly no longer unique. Business 
Rules can and do change, and we do not know what may change in the future. 
Hence, it is safest to have the surrogate in place from the start, and 
avoid the potential migraine later on.


Disclaimer: this is just my opinion based on my experience (and the pain I 
had to go through when Business Rules changed). I have not done any 
research or conducted any studies on this.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[geoff hoffman]

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 list)... it may be possible to define a collection of 
tables as a primary key. But if only a unique key is specified in this case, 
everyone would still be ok with the result from a logic design standpoint.

 I think Melvin, way up the thread, had the best answer- be the DBA and have a 
review process. Don’t let folks go adding tables as they like.

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

[Adrian Klaver]

On 03/01/2018 09:59 AM, Ron Johnson wrote:

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 half 
way through the process. What is you restore procedure?


$ psql < globals.sql


Assuming globals.sql was creating your previously shown command:

pg_dumpall --schema-only > globals.sql

Then the above added the globals to the cluster and installed the schema 
objects(but not data) for the cluster.


$ pg_restore --clean --create --if-exists --exit-on-error --jobs=2 


When you do --clean and --create you DROP the database from the cluster 
before it is restored. A quick test here shows that the database 
permissions are not restored in that case. This is something that us 
contained in the globals.


I fix for this I believe is covered in this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3f8401205afdaf63cb20dc316d44644c933d5a1


${SRC}/${DB}.pgdump





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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 logic
keys in your joins even though it's a bit of extra work.


​The strongest case, for me, when a surrogate key is highly desirable is 
when there is no truly natural key and the best key for the model is 
potentially alterable. Specific, the "name" of something.  If I add myself 
to a database and make name unique, so David Johnston, then someone else 
comes along with the same name and now I want to add the new person as, 
say David A. Johnston AND rename my existing record to David G. Johnston.  
I keep the needed uniqueness ​and don't need to cobble together other data 
elements.  Or, if I were to use email address as the key the same physical 
entity can now change their address without me having to cascade update 
all FK instances too. Avoiding the FK cascade when enforcing a non-ideal 
PK is a major good reason to assign a surrogate.


There's always the "account number", which is usually synthetic. Credit Card 
numbers are also synthetic.  ICD numbers are (relatively) synthetic, too.


But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Tim Cross]

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 you can insert the 
> same "business data" multiple times with different synthetic keys.
>
>
> -- 
> Angular momentum makes the world go 'round.
 IMHO, business logic can and must preclude "garbage insertion". Except 
 you are inserting data directly to database using SQL, any n-tier 
 architecture will be checking data validity.
>>>
>>> Any n-tier architecture that's bug-free.
>>>
>> Do you know about unit testing?
>
> Way Back When Dinosaurs Still Roamed The Earth and I first learned the 
> trade, the focus was on proper design instead of throwing crud against the 
> wall and hoping tests caught any bugs. Because, of course, unit tests are 
> only as good as you imagination in devising tests.

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

-- 
Tim Cross

Re: Enforce primary key on every table during dev?

[Adrian Klaver]

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 madness, not just random issuance of numbers. 
It was made it relatively easy for folks to generate numbers. Hence the 
addition of CSC codes.


ICD numbers are (relatively)

synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Enforce primary key on every table during dev?

[David G. Johnston]

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

​https://en.wikipedia.org/wiki/All_models_are_wrong

Unfortunately identifying a natural primary key doesn't guarantee that
one's model is mature, unblemished, and complete - the model writer may
just not know what they don't know.  But they may know enough, or the
application is constrained enough, for it to be useful anyway.

David J.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

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:

https://en.wikipedia.org/wiki/Payment_card_number

There is a method to the madness, not just random issuance of numbers. It 
was made it relatively easy for folks to *generate numbers*. Hence the 
addition of CSC codes.


Right.  And how do the issuers generate the individual account identifier 
within their IIN ranges?




ICD numbers are (relatively)

synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Adrian Klaver]

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. 


Actually, no:

https://en.wikipedia.org/wiki/Payment_card_number

There is a method to the madness, not just random issuance of numbers. 
It was made it relatively easy for folks to *generate numbers*. Hence 
the addition of CSC codes.


Right.  And how do the issuers generate the individual account 
identifier within their IIN ranges?


Who knows, that is their business, though there is nothing to say they 
don't use some sort of internal 'natural' logic. It has been awhile 
since we have gone down this rabbit hole on this list, mostly because it 
is an issue that is usually left at 'we agree to disagree'. Though the 
thing that always strikes me is the assumption that a number/surrogate 
key is less 'natural' then some other sort of tag or combination of 
tags. Because that is what PK's are, a tag to identify a record.






ICD numbers are (relatively)

synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Enforce primary key on every table during dev?

[Gavin Flower]

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 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 deletes are absolutely forbidden - corrections
happen by inserting rows with
>an opposite transaction.

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 join it with child tables.


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" multiple columns), but guess what, 
business logic changes, and then you're screwed! So using a primary 
key whose sole purpose is to be a primary key makes perfect sense to me.


I once worked in a data base that had primary keys of at least 4 
columns, all character fields, Primary Key could easily exceed 45 
characters.  Parent child structure was at least 4 deep.


A child table only needs to know its parent, so there is no logical need 
to include its parent and higher tables primary keys, and then have to 
add a field to make the composite primary key unique!  So if every table 
has int (or long) primary keys, then a child only need a single field to 
reference its parent.


Some apparently safe Natural Keys might change unexpectedly.  A few 
years aback there was a long thread on Natural versus Surrogate keys - 
plenty of examples were using Natural Keys can give grief when they had 
to be changed!  I think it best to isolate a database from external 
changes as much as is practicable.


Surrogate keys also simply coding, be it in SQL or Java, or whatever 
language is flavour of the month.  Also it makes setting up testdata and 
debugging easier.


I almost invariably define a Surrogate key when I design tables.


Cheers,
Gavin

Re: Enforce primary key on every table during dev?

[marcelo]

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. 
Credit Card numbers are also synthetic. 


Actually, no:

https://en.wikipedia.org/wiki/Payment_card_number

There is a method to the madness, not just random issuance of 
numbers. It was made it relatively easy for folks to *generate 
numbers*. Hence the addition of CSC codes.


Right.  And how do the issuers generate the individual account 
identifier within their IIN ranges?


Who knows, that is their business, though there is nothing to say they 
don't use some sort of internal 'natural' logic. It has been awhile 
since we have gone down this rabbit hole on this list, mostly because 
it is an issue that is usually left at 'we agree to disagree'. Though 
the thing that always strikes me is the assumption that a 
number/surrogate key is less 'natural' then some other sort of tag or 
combination of tags. Because that is what PK's are, a tag to identify 
a record.

+1.






ICD numbers are (relatively)

synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.






---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: PQConsumeinput stuck on recv

[Andre Oliveira Freitas]

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  0x7f8d26689783 in recv (__flags=0, __n=,
__buf=, __fd=) at
/usr/include/x86_64-linux-gnu/bits/socket2.h:44
#2  pqsecure_raw_read (conn=conn@entry=0x7f8cf001e390, ptr=, len=) at
/build/postgresql-10-rIfDLC/postgresql-10-10.2/build/../src/interfaces/libpq/fe-secure.c:230
#3  0x7f8d26689863 in pqsecure_read
(conn=conn@entry=0x7f8cf001e390, ptr=, len=) at 
/build/postgresql-10-rIfDLC/postgresql-10-10.2/build/../src/interfaces/libpq/fe-secure.c:217
#4  0x7f8d266810ea in pqReadData (conn=conn@entry=0x7f8cf001e390)
at 
/build/postgresql-10-rIfDLC/postgresql-10-10.2/build/../src/interfaces/libpq/fe-misc.c:683
#5  0x7f8d2667e6f2 in PQconsumeInput (conn=0x7f8cf001e390) at
/build/postgresql-10-rIfDLC/postgresql-10-10.2/build/../src/interfaces/libpq/fe-exec.c:1704

In this case, I also checked the pg_stat_activity and this particular
connection on pg server side was idle for 15 minutes. I killed it
using pg_terminate_backend, and then somehow the application resumed
execution.

As you can see, recv has received a non-zero value in flags, which has
the MSG_WAITALL bit is set, however in this case the application
expects the read to be non-blocking.

Thanks in advance,

André Freitas

2018-02-23 16:10 GMT-03:00 Andres Freund :
> Hi,
>
> On 2018-02-23 15:59:18 -0300, Andre Oliveira Freitas wrote:
>> I'm using the libpq that comes with debian, however I can install the
>> library from the official repository to be sure, I assume the one from
>> the official repo has it enabled.
>
> That should be fine. Could you get the backtrace again, after installing
> debugging symbols? It'd certainly be helpful to see the exact path to
> the blocking syscall.
>
> Greetings,
>
> Andres Freund



-- 

André Luis O. Freitas
System Architect

Rua do Rócio, 220 - Cj. 72
São Paulo - SP - 04552-000
55 11 4063 4222

afrei...@callix.com.br
www.callix.com.br

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

[Tony Sullivan]

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

I am wondering if I can arrange the WAL files by date and copy them to the
directory where they belong and then copy another batch when those are
restored or if I will need to find some other way of performing the
recovery.

Re: Enforce primary key on every table during dev?

[marcelo]

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 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 deletes are absolutely forbidden - corrections
    happen by inserting rows with
    >an opposite transaction.

    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 join it with child tables.


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" multiple columns), but guess what, 
business logic changes, and then you're screwed! So using a primary 
key whose sole purpose is to be a primary key makes perfect sense to me.


I once worked in a data base that had primary keys of at least 4 
columns, all character fields, Primary Key could easily exceed 45 
characters.  Parent child structure was at least 4 deep.


A child table only needs to know its parent, so there is no logical 
need to include its parent and higher tables primary keys, and then 
have to add a field to make the composite primary key unique!  So if 
every table has int (or long) primary keys, then a child only need a 
single field to reference its parent.


Some apparently safe Natural Keys might change unexpectedly.  A few 
years aback there was a long thread on Natural versus Surrogate keys - 
plenty of examples were using Natural Keys can give grief when they 
had to be changed!  I think it best to isolate a database from 
external changes as much as is practicable.


Surrogate keys also simply coding, be it in SQL or Java, or whatever 
language is flavour of the month.  Also it makes setting up testdata 
and debugging easier.


I almost invariably define a Surrogate key when I design tables.


Cheers,
Gavin





+5. I fully agree.

---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

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

[Alan Hodgson]

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 the drive of the machine I am trying to do the
restore on.

Your restore_command can be a complex command or shell script that can
transfer WAL files as needed from a network source.

Re: Enforce primary key on every table during dev?

[Daevor The Devoted]

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 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 deletes are absolutely forbidden - corrections
>> happen by inserting rows with
>> >an opposite transaction.
>>
>> 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 join it with child tables.
>>
>>
>> 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" multiple columns), but guess what, business
>> logic changes, and then you're screwed! So using a primary key whose sole
>> purpose is to be a primary key makes perfect sense to me.
>>
>
> I once worked in a data base that had primary keys of at least 4 columns,
> all character fields, Primary Key could easily exceed 45 characters.
> Parent child structure was at least 4 deep.
>
> A child table only needs to know its parent, so there is no logical need
> to include its parent and higher tables primary keys, and then have to add
> a field to make the composite primary key unique!  So if every table has
> int (or long) primary keys, then a child only need a single field to
> reference its parent.
>
> Some apparently safe Natural Keys might change unexpectedly.  A few years
> aback there was a long thread on Natural versus Surrogate keys - plenty of
> examples were using Natural Keys can give grief when they had to be
> changed!  I think it best to isolate a database from external changes as
> much as is practicable.
>
> Surrogate keys also simply coding, be it in SQL or Java, or whatever
> language is flavour of the month.  Also it makes setting up testdata and
> debugging easier.
>
> I almost invariably define a Surrogate key when I design tables.
>
>
> Cheers,
> Gavin
>
>
> Thank you! I think you have expressed far more clearly what I have been
trying to say. +10 to you.

Flyway and postgree multiple developers

[Łukasz Jarych]

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