Re: Sort is generating rows

[Nicolas Seinlet]

Hi,

thanks for the answer. The query is based on a view, so here are the view,
the query as well as the query plan.
I've already taken into account remarks like date ranges.

 SELECT min(l.id) AS id,
l.product_id,
t.uom_id AS product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) AS product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) AS qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) AS qty_invoiced,
sum(l.qty_to_invoice / u.factor * u2.factor) AS qty_to_invoice,
sum(l.price_total / COALESCE(cr.rate, 1.0)) AS price_total,
sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal,
sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) AS
amount_to_invoice,
sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) AS
amount_invoiced,
count(*) AS nbr,
s.name,
s.date_order AS date,
s.confirmation_date,
s.state,
s.partner_id,
s.user_id,
s.company_id,
date_part('epoch'::text, avg(date_trunc('day'::text, s.date_order) -
date_trunc('day'::text, s.create_date))) / (24 * 60 *
60)::numeric(16,2)::double precision AS delay,
t.categ_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) AS weight,
sum(p.volume * l.product_uom_qty::double precision / u.factor::double
precision * u2.factor::double precision) AS volume
   FROM sale_order_line l
 JOIN sale_order s ON l.order_id = s.id
 JOIN res_partner partner ON s.partner_id = partner.id
 LEFT JOIN product_product p ON l.product_id = p.id
 LEFT JOIN product_template t ON p.product_tmpl_id = t.id
 LEFT JOIN uom_uom u ON u.id = l.product_uom
 LEFT JOIN uom_uom u2 ON u2.id = t.uom_id
 JOIN product_pricelist pp ON s.pricelist_id = pp.id
 LEFT JOIN LATERAL ( SELECT res_currency_rate.rate
   FROM res_currency_rate
  WHERE res_currency_rate.currency_id = pp.currency_id AND
(res_currency_rate.company_id = s.company_id OR
res_currency_rate.company_id IS NULL) AND daterange(res_currency_rate.name,
COALESCE(res_currency_rate.date_end, now()::date)) @>
COALESCE(s.date_order::timestamp with time zone, now())::date
 LIMIT 1) cr ON true
  GROUP BY l.product_id, l.order_id, t.uom_id, t.categ_id, s.name,
s.date_order, s.confirmation_date, s.partner_id, s.user_id, s.state,
s.company_id, s.pricelist_id, s.analytic_account_id, s.team_id,
p.product_tmpl_id, partner.country_id, partner.commercial_partner_id;

explain analyse select team_id,partner_id,sum(price_total) from sale_report
group by team_id,partner_id;


  QUERY
PLAN

---
 GroupAggregate  (cost=1344575.91..1344986.97 rows=3654 width=40) (actual
time=8934.915..8944.487 rows=43 loops=1)
   Group Key: sale_report.team_id, sale_report.partner_id
   ->  Sort  (cost=1344575.91..1344667.26 rows=36539 width=40) (actual
time=8934.686..8937.833 rows=32732 loops=1)
 Sort Key: sale_report.team_id, sale_report.partner_id
 Sort Method: quicksort  Memory: 3323kB
 ->  Subquery Scan on sale_report  (cost=1339157.70..1341806.77
rows=36539 width=40) (actual time=8870.269..8923.114 rows=32732 loops=1)
   ->  GroupAggregate  (cost=1339157.70..1341441.38 rows=36539
width=395) (actual time=8870.268..8920.155 rows=32732 loops=1)
 Group Key: l.product_id, l.order_id, t.uom_id,
t.categ_id, s.name, s.date_order, s.confirmation_date, s.partner_id,
s.user_id, s.state, s.company_id, s.pricelist_id, s.analytic_account_id,
s.team_id, p.product_tmpl_id, partner.country_id,
partner.commercial_partner_id
 ->  Sort  (cost=1339157.70..1339249.04 rows=36539
width=92) (actual time=8870.247..8875.191 rows=32732 loops=1)
   Sort Key: l.product_id, l.order_id, t.uom_id,
t.categ_id, s.name, s.date_order, s.confirmation_date, s.partner_id,
s.user_id, s.state, s.company_id, s.pricelist_id, s.analytic_account_id,
s.team_id, p.product_tmpl_id, partner.country_id,
partner.commercial_partner_id
   Sort Method: quicksort  Memory: 5371kB
   ->  Nested Loop Left Join
(cost=695.71..1336388.56 rows=36539 width=92) (actual time=13.468..8797.655
rows=32732 loops=1)
 ->  Hash Left Join  (cost=695.43..3338.19
rows=36539 width=88) (actual time=13.323..65.600 rows=32732 loops=1)
   Hash Cond: (l.product_id = p.id)
   ->  Hash Join  (cost=656.36..2796.71
rows=36539 

Re: Question on disk contention

[Fabio Pardi]

For the sake of completeness I think i have to slightly correct myself.

While I cannot find it in the documentation, (if somebody has pointers, please 
provide them) on my personal notes I found that 'if a scan is already in 
progress, and a new scan starts, then the new scan will start where the 
existing scan is.'

In other words, the new scan will make use of the existing data retrieval, and 
then retrieve the data it misses.

I did some tests which look like my notes are right.

I created a table:

 \dt+ big_series 
  List of relations
  Schema   |Name| Type  | Owner | Size  | Description 
---++---+---+---+-
 pv_public | big_series | table | user | 24 GB | 
(1 row)


 \d big_series ;
 Table "pv_public.big_series"
 Column |  Type   | Modifiers 
+-+---
 a  | integer | 


containing 700+ million records


On a 4 cores VM, with 10GB RAM running Postgres 9.6.3: 

If i run a single query, then it takes around 3.5 minutes

running i=50 queries in parallel, which do

select * from big_series where a = '$i' 

and start with 2 seconds difference one to another,

then the times are:

output.0: Execution time: 213519.445 ms
output.10: Execution time: 223256.190 ms
output.11: Execution time: 224113.198 ms
output.12: Execution time: 225147.653 ms
output.13: Execution time: 226101.934 ms
output.14: Execution time: 227028.992 ms
output.15: Execution time: 228622.188 ms
output.16: Execution time: 229584.695 ms
output.17: Execution time: 229795.122 ms
output.18: Execution time: 229949.648 ms
output.19: Execution time: 229974.346 ms
output.1: Execution time: 214274.906 ms
output.20: Execution time: 230001.553 ms
output.21: Execution time: 230138.985 ms
output.22: Execution time: 230080.078 ms
output.23: Execution time: 230135.255 ms
output.24: Execution time: 230393.351 ms
output.25: Execution time: 230467.203 ms
output.26: Execution time: 230651.052 ms
output.27: Execution time: 230603.229 ms
output.28: Execution time: 230502.608 ms
output.29: Execution time: 230692.864 ms
output.2: Execution time: 215348.711 ms
output.30: Execution time: 230630.022 ms
output.31: Execution time: 230501.983 ms
output.32: Execution time: 230213.728 ms
output.33: Execution time: 229992.756 ms
output.34: Execution time: 229515.631 ms
output.35: Execution time: 228769.224 ms
output.36: Execution time: 228341.051 ms
output.37: Execution time: 227881.148 ms
output.38: Execution time: 226883.588 ms
output.39: Execution time: 225697.683 ms
output.3: Execution time: 216344.473 ms
output.40: Execution time: 224860.213 ms
output.41: Execution time: 222169.478 ms
output.42: Execution time: 221259.683 ms
output.43: Execution time: 220444.522 ms
output.44: Execution time: 219244.326 ms
output.45: Execution time: 218170.045 ms
output.46: Execution time: 217346.484 ms
output.47: Execution time: 216860.823 ms
output.48: Execution time: 216431.684 ms
output.49: Execution time: 216286.271 ms
output.4: Execution time: 217365.770 ms
output.5: Execution time: 218376.528 ms
output.6: Execution time: 219395.868 ms
output.7: Execution time: 220501.178 ms
output.8: Execution time: 221297.149 ms
output.9: Execution time: 222157.996 ms




Please correct me if my notes are wrong, or provide pointers to the 
documentation.


regards,

fabio pardi


On 31/05/18 16:20, Fabio Pardi wrote:
> As far as I know, the OS cache is shared, and shared_buffers too.
> 
> Back to the matter of contention, your statement i think might be true only 
> in cases when you are querying data which does not fit in RAM. 
> 
> Under those circumstances, the OS or Postgres might need to evict blocks from 
> RAM to make room to new blocks fetched from disk, while the concurrent query 
> is trying to do the same. Then the disk contention might happen. 
> 
> 
> regards,
> 
> fabio pardi
> 
> On 31/05/18 16:09, Melvin Davidson wrote:
>>
>>
>> On Thu, May 31, 2018 at 10:04 AM, Ron > > wrote:
>>
>> On 05/31/2018 08:52 AM, Melvin Davidson wrote:
>>>
>>>
>>> On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher 
>>> mailto:clavadetsc...@swisspug.org>> wrote:
>>>
>>> Hi Melvin
>>>
>>> As an answer to a previous post you wrote:
>>>
>>> "Also, your main problem is that when you have two exact same 
>>> queries executing at the same time, they will cause contention in
>>> the disk, and neither one will make much progress."
>>>
>>> Could you elaborate a little more on the meaning of "contention in 
>>> the disk"?
>>> What is it that happens?
>>>
>>> Thank you and have a good day.
>>> Regards
>>> Charles
>>>
>>>
>>>
>>> >Could you elaborate a little more on the meaning of "contention in the 
>>> disk"?
>>> >What is it that happens?
>>>
>>> To simplify, you have two users/jobs, both wanting the exact same 
>>> information. So the system instructs the disk to get
>>> that informati

VBA to connect to postgresql from MS Access

[Łukasz Jarych]

 Hi Guys,

When i have linked table based on DSN odbc connection to postgresql ,
i am going to :

VBA editor, in immediate window inputing:

?CurrentDb.TableDefs("TableName").Connect
i have very strange connection string:

ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*
>
>
>
When i was searching code for this in internet i found only:

DRIVER={PostgreSQL
Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
>
> but this is not working, why? I do not know what these CA, BO, BI strange
paramaters.

I want to use VBA to relink all tables and use DSN less connection string.

Please help,
Best,
Jacek

>

Re: VBA to connect to postgresql from MS Access

[Arnaud L.]

Le 01-06-18 à 14:21, Łukasz Jarych a écrit :

ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*

When i was searching code for this in internet i found only:
DRIVER={PostgreSQL 
Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"

but this is not working, why? I do not know what these CA, BO, BI 
strange paramaters.
Why is this not working, we cannot answer without the error message you 
get. You probably use the wrong driver name.


As for the CA, BO, etc... they are abreviations of the keywords. You can 
use either the keywords or the abreviations in your connection string, 
and they are all explained here :

https://odbc.postgresql.org/docs/config-opt.html


--
Arnaud

Re: reduce number of multiple values to be inserted

[Steven Lembark]

On Wed, 30 May 2018 08:10:05 +0800
tango ward  wrote:

> curr_pgsql.execute('''
>INSERT INTO student (created, modified, name,
> address, age,
> level )
>VALUES (current_timezone, current_timezone,
>'Scott', 'Malayan Village', 21, 2),
>   (current_timezone, current_timezone,
>'Ben', 'Malayan Village', 21, 2),
>   (current_timezone, current_timezone,
>'Scott', 'Malayan Village', 21, 2),
> 
>   (current_timezone, current_timezone,
>'Andrew', 'Malayan Village', 25, 2),
>   (current_timezone, current_timezone,
>'Larry', 'Malayan Village', 25, 2),
>   (current_timezone, current_timezone,
>'Adam', 'Malayan Village', 25, 2),
> 
>   (current_timezone, current_timezone,
>'Elisse', 'Malayan Village', 27, 2),
>   (current_timezone, current_timezone,
>'Xena', 'Malayan Village', 27, 2),
>   (current_timezone, current_timezone,
>'Karen', 'Malayan Village', 27, 2)
> 
> I will repeat the same process for 13 villages so that will be 117 of
> values. I would like to know if there's a way to reduce the script?
> This has to be done strictly via script.

Rather than try to cut+paste SQL it may be easier to put the data
into a flat file and have some code spit the SQL out for you:

#!/usr/bin/env perl

# housekeeping

use v5.22;
use YAML::XSqw( Load );


# package variables


my $data 
= do
{
# slurp the data from named files or stdin.

local $/;
Load 
};

my $prefix  = <<'SQL';

/*
 * Input data for vx.y.z of student data schema.
 */

insert into student 
(
created,
modified,
name,
address,
age,
level
)
values
(
SQL

my $suffix = <<'SQL';
);

/*
 * end of input
 */
SQL


# output the SQL


say $prefix;

while( my ( $place, $place_valz ) = each %$data )
{
while( my ( $nums, $namz ) = each %$place_valz )
{
for my $name ( @$namz )
{
say <<"SQL";
(
current_timezone,
current_timezone,
'$name' ,
'$place',
'$nums'
)
}
SQL
}
}

say $suffix;

# this is not a module
0
__END__

=head1 NAME

output_sql - dump insert ... values ... from YAML

=head1 SYNOPSIS

Inputs arrive via stdin, literal, or glob-ed paths:


output_sql << /path/to/foobar.yaml;
output_sql /path/to/foobar.yaml;
output_sql /path/to/*.yaml;


gzip -dc < /path/to/bigfile.yaml | output_sql;
gzip -dc < /path/to/bigfile.yaml | output_sql | psql;



Your data file could look like this if you want a single flat file
for all of it:

---
Malayan Village :
  21, 2 :
  - Ben
  - Scott
  25, 2 :
  - Anderew
  - Larry
  - Adam
  ...
Another Village :
 ...

Or your could break it into chunks using multiple documents within
the YAML file (notice the extra '---'):

---
Malayan Village :
  21, 2 :
  - Ben
  - Scott
  25, 2 :
  - Anderew
  - Larry
  - Adam
  ...
---
Another Village :
 ...

At which point $data, above, is an array and you get:

for my $chunk ( @$data )
{
while( my ( $place, $place_valz ) = each %$chunk )
{
...
}
}

with the added flexibility of breaking the input data into 
multiple files if needed.

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

Re: Sort is generating rows

[Adrian Klaver]

On 06/01/2018 02:36 AM, Nicolas Seinlet wrote:

Hi,

thanks for the answer. The query is based on a view, so here are the 
view, the query as well as the query plan.

I've already taken into account remarks like date ranges.


You changed the query from the original, besides just adding the 
daterange, I see an addition of a LATERAL, where there other changes?


The changes did eliminate the 300 million line sort from what I can see.

The new query takes ~9 secs is that an improvement over the old?

I took the liberty of running the EXPLAIN ANALYZE through 
explain.depesz.com:


https://explain.depesz.com/s/9thl

The largest amount of time was in the Index Scan(8,706.712ms) and that 
was because the scan was looped 32,732 times. I have not used LATERAL in 
my own code so I looked it up:


https://www.postgresql.org/docs/10/static/sql-select.html

LATERAL

"
...

When a FROM item contains LATERAL cross-references, evaluation proceeds 
as follows: for each row of the FROM item providing the cross-referenced 
column(s), or set of rows of multiple FROM items providing the columns, 
the LATERAL item is evaluated using that row or row set's values of the 
columns. The resulting row(s) are joined as usual with the rows they 
were computed from. This is repeated for each row or set of rows from 
the column source table(s).

...
"

If I am following correctly that might explain some of looping seen above.



  SELECT min(l.id ) AS id,
     l.product_id,
     t.uom_id AS product_uom,
     sum(l.product_uom_qty / u.factor * u2.factor) AS product_uom_qty,
     sum(l.qty_delivered / u.factor * u2.factor) AS qty_delivered,
     sum(l.qty_invoiced / u.factor * u2.factor) AS qty_invoiced,
     sum(l.qty_to_invoice / u.factor * u2.factor) AS qty_to_invoice,
     sum(l.price_total / COALESCE(cr.rate, 1.0)) AS price_total,
     sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal,
     sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) AS 
amount_to_invoice,
     sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) AS 
amount_invoiced,

     count(*) AS nbr,
s.name ,
     s.date_order AS date,
     s.confirmation_date,
     s.state,
     s.partner_id,
     s.user_id,
     s.company_id,
     date_part('epoch'::text, avg(date_trunc('day'::text, s.date_order) 
- date_trunc('day'::text, s.create_date))) / (24 * 60 * 
60)::numeric(16,2)::double precision AS delay,

     t.categ_id,
     s.pricelist_id,
     s.analytic_account_id,
     s.team_id,
     p.product_tmpl_id,
     partner.country_id,
     partner.commercial_partner_id,
     sum(p.weight * l.product_uom_qty / u.factor * u2.factor) AS weight,
     sum(p.volume * l.product_uom_qty::double precision / 
u.factor::double precision * u2.factor::double precision) AS volume

    FROM sale_order_line l
      JOIN sale_order s ON l.order_id = s.id 
      JOIN res_partner partner ON s.partner_id = partner.id 


      LEFT JOIN product_product p ON l.product_id = p.id 
      LEFT JOIN product_template t ON p.product_tmpl_id = t.id 
      LEFT JOIN uom_uom u ON u.id  = l.product_uom
      LEFT JOIN uom_uom u2 ON u2.id  = t.uom_id
      JOIN product_pricelist pp ON s.pricelist_id = pp.id 
      LEFT JOIN LATERAL ( SELECT res_currency_rate.rate
            FROM res_currency_rate
           WHERE res_currency_rate.currency_id = pp.currency_id AND 
(res_currency_rate.company_id = s.company_id OR 
res_currency_rate.company_id IS NULL) AND 
daterange(res_currency_rate.name , 
COALESCE(res_currency_rate.date_end, now()::date)) @> 
COALESCE(s.date_order::timestamp with time zone, now())::date

          LIMIT 1) cr ON true
   GROUP BY l.product_id, l.order_id, t.uom_id, t.categ_id, s.name 
, s.date_order, s.confirmation_date, s.partner_id, 
s.user_id, s.state, s.company_id, s.pricelist_id, s.analytic_account_id, 
s.team_id, p.product_tmpl_id, partner.country_id, 
partner.commercial_partner_id;


explain analyse select team_id,partner_id,sum(price_total) from 
sale_report group by team_id,partner_id;


 
   
QUERY PLAN

---
  GroupAggregate  (cost=1344575.91..1344986.97 rows=3654 width=40) 
(actual time=8934.915..8944.487 rows=43 loops=1)

    Group Key: sale_report.team_id, sale_report.partner_id
    ->  Sort  (cost=1344575.91..1344667.26 rows=36539 width=40) (actual 
time=8934.686..8937.833 rows=32732 loops=1)

          Sort Key: sale_report.team_id, sale_report

Re: Pgagent is not reading pgpass file either in Windows or Linux.

[nageswara Bandla]

On Thu, May 31, 2018 at 5:16 PM, George Neuner  wrote:

> On Thu, 31 May 2018 15:40:21 -0500, nageswara Bandla
>  wrote:
>
> >On Thu, May 31, 2018 at 12:57 PM, George Neuner 
> >wrote:
> >
> >> It just occurred to me that you said PGPASSFILE was set to
> >>
> >> %APPDATA%/postgresql/pgpass.conf
> >>
> >>
> >> The problem may be that when LocalSystem expands %APPDATA%, it is
> >> finding its own directory, which might be any of:
> >>
> >>C:\Windows\ServiceProfiles\LocalService\appdata
> >>C:\Windows\System32\config\systemprofile\AppData
> >>C:\Windows\SysWOW64\config\systemprofile\AppData
> >>
> >> depending on your Windows version, policies (if any), and whether the
> >> executable is 32 or 64 bit.
> >>
> >>
> >> I wouldn't try messing with any of these directories. Instead try
> >> setting PGPASSFILE to the full path to your file.
> >>
> >>
> >I have tried all of them, pgagent is not recognizing any of the above
> >locations. In fact, I have tried both options
> >
> > #1. By defining PGPASSFILE to the above locations one after the other.
> > #2. By copying pgpass.conf to all the three locations by creating
> >Roaming/postgresql directories.
> >
> >And also I have defined PGPASSFILE=C:\pgpass.conf; I think, this should be
> >accessible to any system account. This also not working.
>
>
> One more stupid question and then I'm out of ideas ...
>
>
> Have you rebooted after changing the environment variable?
>
> Global environment changes normally don't take effect until the user
> logs out/in again.  LocalSystem is not an interactive user - you have
> to restart the system to let it see environment changes.  PITA.
>
>
> Yes, I did. But no luck..I guess, we have to live with this problem for
pgagent running as a Local System account.
We need to run pgagent service as  "Logon user account" and provide user
logon credentials for running pgagent service.

In Linux case, pgagent is not even reading .pgpass itself. The issue here
is that the logs (debug level log) are no help. It don't have much
information.
Which password file it is trying to read.







> George
>
>
>

Whither 1:1?

[Guyren Howe]

It’s come to my attention that what seems an obvious and useful database design 
pattern — 1:1 relations between tables by having a shared primary key — is 
hardly discussed or used.

It would seem to be a very simple pattern, and useful to avoid storing nulls or 
for groups of fields that tend to be used together.

Thoughts? Is there some downside I can’t see?

Re: Whither 1:1?

[David G. Johnston]

On Fri, Jun 1, 2018 at 9:52 AM, Guyren Howe  wrote:

> It’s come to my attention that what seems an obvious and useful database
> design pattern — 1:1 relations between tables by having a shared primary
> key — is hardly discussed or used.
>
> It would seem to be a very simple pattern, and useful to avoid storing
> nulls or for groups of fields that tend to be used together.
>
> Thoughts? Is there some downside I can’t see?
>

​I make considerable use of one-to-one tables.  There isn't any inherent
absolute downside to doing this that I can think of (potentially more joins
are neither inherent ​nor absolute) though the specific benefits or
concerns rely upon the specific model being implemented.

The two ways I've done this is "static vs dynamic data" split and,
basically, "attribute grouping".  This is one that can avoid storing nulls
though in my particular usage I still have them since I'm basically
implementing a key-value json document and so have a record for the ID in
every table even if no data is present.  The advantage is reducing model
complexity by adding meta-data to all of the fields contained fields.

David J.

>

Re: Whither 1:1?

[James Keener]

I don't think I fully understand. Do you mean all pk using a single
sequence? I'm not sure how this would avoid nulls or grouping fields.

Jim

On Fri, Jun 1, 2018 at 12:52 PM, Guyren Howe  wrote:

> It’s come to my attention that what seems an obvious and useful database
> design pattern — 1:1 relations between tables by having a shared primary
> key — is hardly discussed or used.
>
> It would seem to be a very simple pattern, and useful to avoid storing
> nulls or for groups of fields that tend to be used together.
>
> Thoughts? Is there some downside I can’t see?
>

Re: Whither 1:1?

[Pavel Stehule]

2018-06-01 18:52 GMT+02:00 Guyren Howe :

> It’s come to my attention that what seems an obvious and useful database
> design pattern — 1:1 relations between tables by having a shared primary
> key — is hardly discussed or used.
>
> It would seem to be a very simple pattern, and useful to avoid storing
> nulls or for groups of fields that tend to be used together.
>
> Thoughts? Is there some downside I can’t see?
>

Postgres has not multi table statistics - every JOIN increase estimation
error.

Regards

Pavel

>

Re: Whither 1:1?

[Guyren Howe]

On Jun 1, 2018, at 10:00 , James Keener  wrote:
> 
> I don't think I fully understand. Do you mean all pk using a single sequence? 
> I’m not sure how this would avoid nulls or grouping fields.

Presumably, you would create the multiple tables together or after some main 
one or something where you know the PK you’re going to use. Perhaps you create 
a view with triggers to present them as a single table.

It could avoid nulls by not having rows in some of the 1:1 tables. Say you have 
a users table with login credentials and also personal information (phone 
number, email and such). But some of your users are strictly API users so have 
no personal information. If you split the person information into a separate 
table, you can just not have rows for those users.

One of the originators of the relational model didn’t want to have null at all, 
presumably wanting folks to use this pattern instead.

RPM Packaging Question - Fedora 28 & Postgis

[John Woltman]

 I'm attempting to install PostGIS2 (specifically postgis2_95 for Postgres
9.5) on Fedora 28.  When I run ``sudo dnf install postgis23_95`` I get the
the following the error:

Error:
  Problem: conflicting requests
 - nothing provides geos36 >= 3.6.2 needed by postgis23_95

Fedora 28 provides GEOS 3.6.1.  Is my issue something that I should bring
up to the Fedora maintainers or to whoever's in charge of the Postgres Yum
repos?

Thank you,
John

John Woltman
TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260

Re: VBA to connect to postgresql from MS Access

[Bret Stern]

Not sure what version of access you're using or how you are calling the
connection..ADO, DAO, .NET

I don't hard code connection strings anymore, but I did when I first
started.
Nowadays I call a registry function to put the drivers on a specific pc
into a listbox
and have a setup module which then builds the connection string on the
fly from the
select listbox value.

The below sample may not work, I haven't tested it, but the basic
elements are there to help
you start thinking about how it does work.

The DRIVER= element of the connection must have the name of the driver
installed on your
pc. Usually they can be found under the ODBC administrator tool, found
in Control Panel, Administrative Tools,
ODBC Data Source Administrator. If you're creating a DSN-LESS
connection, then click the Drivers tab and scroll to the
Postgresql drivers

The DRIVER= element must exactly match the name of the driver in the
[Drivers] tab under the ODBC administrator
tool. In the code example below, the driver name on my pc is PostgreSQL
ANSI

The other elements are well documented
Server= (an ip address or hostname of the database server you are
connecting to)
Port= (Can generally be left at 5432...the postgresql default port)
Database= (is the case sensitive name of the postgresql database you are
connecting)
UID= (postgresql user name to make the connection under)
PWD= (the password the connecting user)

If you are still having problems, there is the pg_hba.conf file which
may block access.
You can read about that here.
https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html


try creating a simple form, add a button, and have the button click
event call the below
function AFTER you have modified the elements to suit your database
name, server etc

hopefully some of this info will help make sense


Function pg_db_open() as boolean
   Dim conNew As New ADODB.Connection
   Dim cs as string
   cs = "DRIVER=PostgreSQL ANSI; Server=your-server-ip; Port=5432;
Database=your-database-name; UID=username; PWD=password"

On Error GoTo conError

conNew.ConnectionString = cs
conNew.Open
Set conNew = Nothing

pg_db_open = True
Exit Function

conError:
Set conNew = Nothing
pg_db_open = False
End Function




On Fri, 2018-06-01 at 14:27 +0200, Arnaud L. wrote:

> Le 01-06-18 à 14:21, Łukasz Jarych a écrit :
> > ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*
> > 
> > When i was searching code for this in internet i found only:
> > DRIVER={PostgreSQL 
> > Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
> > 
> > but this is not working, why? I do not know what these CA, BO, BI 
> > strange paramaters.
> Why is this not working, we cannot answer without the error message you 
> get. You probably use the wrong driver name.
> 
> As for the CA, BO, etc... they are abreviations of the keywords. You can 
> use either the keywords or the abreviations in your connection string, 
> and they are all explained here :
> https://odbc.postgresql.org/docs/config-opt.html
> 
> 


-- 
Bret Stern
Machine Management
Industrial and Commercial IT Services

707-775-9792 (cell-text-direct)

Re: RPM Packaging Question - Fedora 28 & Postgis

[Adrian Klaver]

On 06/01/2018 10:06 AM, John Woltman wrote:
I'm attempting to install PostGIS2 (specifically postgis2_95 for 
Postgres 9.5) on Fedora 28.  When I run ``sudo dnf install 
postgis23_95`` I get the the following the error:


Error:
   Problem: conflicting requests
  - nothing provides geos36 >= 3.6.2 needed by postgis23_95

Fedora 28 provides GEOS 3.6.1.  Is my issue something that I should 
bring up to the Fedora maintainers or to whoever's in charge of the 
Postgres Yum repos?


What repo source are you using for the PostGIS package?



Thank you,
John

John Woltman
TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260




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

Re: RPM Packaging Question - Fedora 28 & Postgis

[Tom Lane]

John Woltman  writes:
>  I'm attempting to install PostGIS2 (specifically postgis2_95 for Postgres
> 9.5) on Fedora 28.  When I run ``sudo dnf install postgis23_95`` I get the
> the following the error:

> Error:
>   Problem: conflicting requests
>  - nothing provides geos36 >= 3.6.2 needed by postgis23_95

> Fedora 28 provides GEOS 3.6.1.  Is my issue something that I should bring
> up to the Fedora maintainers or to whoever's in charge of the Postgres Yum
> repos?

The latter; specifically, whoever produced the allegedly-F28-compatible
postgis RPM you're trying to install.

regards, tom lane

Re: Whither 1:1?

[Olivier Gautherot]

On Fri, Jun 1, 2018 at 12:52 PM, Guyren Howe  wrote:

> It’s come to my attention that what seems an obvious and useful database
> design pattern — 1:1 relations between tables by having a shared primary
> key — is hardly discussed or used.
>
> It would seem to be a very simple pattern, and useful to avoid storing
> nulls or for groups of fields that tend to be used together.
>
> Thoughts? Is there some downside I can’t see?
>

You will get a benefit in terms of space only if the optional fields in the
second table exist in a reduced number of instances - and the second table
is significantly wider. This can make a difference on big tables but this
gain may be offset by the cost of the join. In this perspective, I don't
think that there is a clear benefit or drawback: it should be evaluated on
a case-by-case basis.

Olivier Gautherot

Re: Whither 1:1?

[Guyren Howe]

On Jun 1, 2018, at 10:16 , Olivier Gautherot  wrote:
> 
> You will get a benefit in terms of space only if the optional fields in the 
> second table exist in a reduced number of instances - and the second table is 
> significantly wider. This can make a difference on big tables but this gain 
> may be offset by the cost of the join. In this perspective, I don’t think 
> that there is a clear benefit or drawback: it should be evaluated on a 
> case-by-case basis.

It seems to me that people take time to catch up with modern hardware reality. 
SSDs reduce seek time to virtually zero. Surely, joins are now much, much 
cheaper. If so, I’m inclined to describe wide tables as a premature 
optimization.

Re: RPM Packaging Question - Fedora 28 & Postgis

[John Woltman]

According to "dnf info", the repo is pgdg95, which I got from the Postgres
website.  Since this looks like a packaging bug and not specifically a
Postgres bug, should I submit it pgsql-b...@postgresql.org?

-John

John Woltman
TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260

On Fri, Jun 1, 2018 at 1:12 PM, Adrian Klaver 
wrote:

> On 06/01/2018 10:06 AM, John Woltman wrote:
>
>> I'm attempting to install PostGIS2 (specifically postgis2_95 for Postgres
>> 9.5) on Fedora 28.  When I run ``sudo dnf install postgis23_95`` I get the
>> the following the error:
>>
>> Error:
>>Problem: conflicting requests
>>   - nothing provides geos36 >= 3.6.2 needed by postgis23_95
>>
>> Fedora 28 provides GEOS 3.6.1.  Is my issue something that I should bring
>> up to the Fedora maintainers or to whoever's in charge of the Postgres Yum
>> repos?
>>
>
> What repo source are you using for the PostGIS package?
>
>
>
>> Thank you,
>> John
>>
>> John Woltman
>> TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: RPM Packaging Question - Fedora 28 & Postgis

[Adrian Klaver]

On 06/01/2018 10:27 AM, John Woltman wrote:
According to "dnf info", the repo is pgdg95, which I got from the 
Postgres website.  Since this looks like a packaging bug and not 
specifically a Postgres bug, should I submit it 
pgsql-b...@postgresql.org ?


No I would file it here:

https://redmine.postgresql.org/issues/3038

missing packages "proj49" and "geos36" in postgresql 9.5 repository 
(dependencies of "postgis24_95")


You will need either a Postgres community account to log in:

https://www.postgresql.org/account/signup/

or third party accounts, see below:

https://www.postgresql.org/account/auth/4/



-John

John Woltman
TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260


On Fri, Jun 1, 2018 at 1:12 PM, Adrian Klaver > wrote:


On 06/01/2018 10:06 AM, John Woltman wrote:

I'm attempting to install PostGIS2 (specifically postgis2_95 for
Postgres 9.5) on Fedora 28.  When I run ``sudo dnf install
postgis23_95`` I get the the following the error:

Error:
    Problem: conflicting requests
   - nothing provides geos36 >= 3.6.2 needed by postgis23_95

Fedora 28 provides GEOS 3.6.1.  Is my issue something that I
should bring up to the Fedora maintainers or to whoever's in
charge of the Postgres Yum repos?


What repo source are you using for the PostGIS package?



Thank you,
John

John Woltman
TPI • 302 New Mill Lane • Exton, PA 19341 • (610) 524-7260



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: Whither 1:1?

[Olivier Gautherot]

On Fri, Jun 1, 2018 at 1:25 PM, Guyren Howe  wrote:

> On Jun 1, 2018, at 10:16 , Olivier Gautherot 
> wrote:
>
>
> You will get a benefit in terms of space only if the optional fields in
> the second table exist in a reduced number of instances - and the second
> table is significantly wider. This can make a difference on big tables but
> this gain may be offset by the cost of the join. In this perspective, I
> don’t think that there is a clear benefit or drawback: it should be
> evaluated on a case-by-case basis.
>
>
> It seems to me that people take time to catch up with modern hardware
> reality. SSDs reduce seek time to virtually zero. Surely, joins are now
> much, much cheaper. If so, I’m inclined to describe wide tables as a
> premature optimization.
>

Guyren, I'm only saying it is not a one-size-fits-all and we don't all have
last-generation hardware to run our systems on. I actually have a use-case
for the structure you describe, which supports optional fields in a table:
the main one is partitioned into 15GB monthly tables with 30+ millions
rows. The parallel table is used for devices that generate additional
information but are a small subset of the fleet. This way, I could prevent
the overhead of 150 columns across the board (the main table has around
30). By keeping things simple (I mean a single table), you get more bloat
but you can reduce the response time and may eventually be able to run on a
smaller, cheaper server. It is really a project-specific design issue.

Olivier Gautherot

Re: Whither 1:1?

[Ron]

On 06/01/2018 12:25 PM, Guyren Howe wrote:
On Jun 1, 2018, at 10:16 , Olivier Gautherot > wrote:


You will get a benefit in terms of space only if the optional fields in 
the second table exist in a reduced number of instances - and the second 
table is significantly wider. This can make a difference on big tables 
but this gain may be offset by the cost of the join. In this perspective, 
I don’t think that there is a clear benefit or drawback: it should be 
evaluated on a case-by-case basis.


It seems to me that people take time to *catch up with modern hardware 
reality. SSDs reduce seek time to virtually zero.* Surely, joins are now 
much, much cheaper. If so, I’m inclined to describe wide tables as a 
premature optimization.


Sure, SSDs are uber-wonderful, but a rack full of rotating media is still 
going to be a lot cheaper and have a lot more capacity than a rack full of 
SSDs, and that makes all the difference...


--
Angular momentum makes the world go 'round.

notes from transition to relkind='p'

[Justin Pryzby]

Before I forget any more, this is a brain of issues/considerations/concerns
with our (partial) transition to partitioned tables over the last ~9 months.  I
believe these are all documented behaviors, but could be seen by users as a
gratuitious/unexpected change or rough edge and the differences could perhaps
be mitigated.  I realize there's maybe no good time or way to change most of
these, but maybe the list will be helpful to somebody in avoiding unexpected
transitional issues.

 . DROP TABLE on a parent no longer fails without CASCADE (dependencies of
   relkind='p' are deptype='a' "soft" dependencies)
   8b4d582d279d784616c228be58af1e39aa430402
 . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r
   only updates stats for parent); it's unclear if there's any reason why it
   wasn't always done this way(?).  I end up having to conditionize processing
   based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3
 . The docs say: if detaching/re-attach a partition, should first ADD CHECK to
   avoid a slow ATTACH operation.  Perhaps DETACHing a partition could
   implicitly CREATE a constraint which is usable when reATTACHing?
 . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc).  Maybe
   the view could do the needed CASE ... (SELECT min() FROM pg_inherits JOIN 
psut)
 . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to
   suggest one would want to avoid that; but consider: we ended up adding both
   shell and python logic to parse the table name to allow detaching and
   reattaching partitions.  I think it'd be a nice if the bounds were inferred
   if there was a single constraint on the partition key.
 . ALTER TABLE ATTACH has reversed order of child vs parent relative to
   NO/INHERIT.
 . And actually, having both ALTER TABLE DE/TACH vs NO/INHERIT is itself messy:
   we ended up having branches (both shell and python) to handle both cases (at
   least for a transitional period, but probably we'll need to continue
   handling both into the indeterminate future).

Cheers,
Justin

Re: Whither 1:1?

[Tim Cross]

Olivier Gautherot  writes:

> On Fri, Jun 1, 2018 at 12:52 PM, Guyren Howe  wrote:
>
>> It’s come to my attention that what seems an obvious and useful database
>> design pattern — 1:1 relations between tables by having a shared primary
>> key — is hardly discussed or used.
>>
>> It would seem to be a very simple pattern, and useful to avoid storing
>> nulls or for groups of fields that tend to be used together.
>>
>> Thoughts? Is there some downside I can’t see?
>>
>
> You will get a benefit in terms of space only if the optional fields in the
> second table exist in a reduced number of instances - and the second table
> is significantly wider. This can make a difference on big tables but this
> gain may be offset by the cost of the join. In this perspective, I don't
> think that there is a clear benefit or drawback: it should be evaluated on
> a case-by-case basis.
>

Well said. Like many database design decisions, there are more
guidelines than rules. Nearly all decisions have pros and
cons. Therefore, you need to assess on a per project basis. In addition
to the structure of data (size, null frequency etc), you also need to
consider how the data is used. It is also important to consider who will
be using the data, how they will access it and what level of
sophistication/understanding they have. The clarity of your data model
is also important as future decisions may be made by others and the
better they understand the design/model, the higher the likelihood the
system will evolve in a sane and maintainable manner.

There other point to remember is that all designs often have an element
of surprise - you make a decision under an assumption which turns out
not to hold due to variables you didn't consider or don't have control
over. Sometimes there are alternatives which may actually perform better
because they are optimised within the system - for example, some of the
benefits for this approach could be realised using partitions. 

I have used this technique, but from memory, this was done as the system
evolved and we found there was a benefit from having a smaller 'main'
table. It isn't a design decision I recall adopting during an initial
modelling of the system, but it may be something to consider once you
find a performance problem (along with other options) which needs to be
addressed. 

I'm not aware of any guideline or school of thought which rules out this
as an option. Possibly the reason it appears to be used infrequently is
because it doesn't realise the benefits you might expect or is simply
not an problem in a majority of use cases. 

Tim



-- 
Tim Cross