Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On 7 October 2017 at 2:49 PM, David Rowley wrote:
> 
> Yeah, PostgreSQL does not make any effort to convert subqueries in the
> target list into joins. SQL server does.

[...]
> You'll probably find it'll run faster if you convert the subquery in
> the target list into a join with a GROUP BY, like ...
>
Wow, David, that is perfect – 0.91 seconds. Problem well and truly solved.
It took me a little while to modify my original query to use that concept, but 
now it flies.
Many thanks
Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On 5 Oct 2017, at 9:51 AM, Frank Millman wrote:
> 
> I should have re-stated the reason for my original post.
>  
> Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql 
> Server, 1.0 seconds on SQLite3, and 1607 seconds, or 26 minutes, on 
> PostgreSQL 9.4.4.
>  

I will give this another shot. I have made this as simple as I can. Just as a 
reminder, ‘ar_trans’ is a VIEW.

1. Simple select from ‘artrans_due’
SELECT *
FROM ar_trans_due

Sql Server: 0.56 sec; PostgreSQL 0.41 sec

2. Select from ‘ar_trans_due’ including join to ‘ar_trans’
SELECT *
FROM ar_trans_due a
LEFT JOIN ar_trans b
   ON b.tran_type = a.tran_type
   AND b.tran_row_id = a.tran_row_id

Sql Server: 0.90 sec; PostgreSQL 0.70 sec

3. Select from ar_trans_due including join to ar_trans,
plus sub-select from ar_trans_alloc
SELECT *,
(SELECT SUM(c.alloc_cust)
FROM ar_trans_alloc c
WHERE c.due_row_id = a.row_id)
FROM ar_trans_due a
LEFT JOIN ar_trans b
   ON b.tran_type = a.tran_type
   AND b.tran_row_id = a.tran_row_id

Sql Server: 0.92 sec; PostgreSQL 1.00 sec

4. Select from ar_trans_due including join to ar_trans,
plus sub_select from ar_trans_alloc including join to ar_trans
SELECT *,
(SELECT SUM(c.alloc_cust)
FROM ar_trans_alloc c
LEFT JOIN ar_trans d
   ON d.tran_type = c.tran_type
   AND d.tran_row_id = c.tran_row_id
WHERE c.due_row_id = a.row_id)
FROM ar_trans_due a
LEFT JOIN ar_trans b
   ON b.tran_type = a.tran_type
   AND b.tran_row_id = a.tran_row_id

Sql Server: 1.01 sec; PostgreSQL 1683 sec

As you can see, it is the join inside the sub-select that kills it.

Someone has kindly tested this for me on version 9.6.5 and on version 10, and 
the results are similar.

Here is the EXPLAIN ANALYSE for the last of the above queries -

https://explain.depesz.com/s/yhr2

Frank Millman

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On 5 Oct 2017, at 8:20 AM, Frank Millman wrote:

> If anyone wants to take this further, maybe this is a good place to start.

I should have re-stated the reason for my original post.

Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql 
Server, 1.0 seconds on SQLite3, and 1607 seconds, or 26 minutes, on PostgreSQL 
9.4.4.

Frank
 

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote:

> On 2 Oct 2017, at 8:32, Frank Millman <fr...@chagford.com> wrote:
> 
>  
> On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote:
> >  
> >   On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> >   > Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> >   > query?
> >   
>   >   > Here it is -
> >
>   > https://explain.depesz.com/s/cwm
> >
> >  
> > Just checking – is this under investigation, or is this thread considered 
> > closed?
> >  

> There are a few problems keeping track of this issue. First of all, above 
> plan does not include the query (I don't know whether that's a thing with 
> depesz's plan analyzer, but ISTR > seeing plans _with_ their queries in other 
> cases). That means we have to track back through the thread (quite a bit) to 
> find a query that _may_ be the one that the plan is for. Add > that to the 
> fact that most of us are busy people, so we have to invest too much time into 
> your problem to be able to help - and hence we don't.

[snip a lot of good suggestions]
Thanks for the reply, Alban.
I agree that the query is a bit complex and not easy to analyse. I have taken 
note of all your suggestions and will investigate them further.
I did attempt to narrow this down to a simple example in one of my earlier 
posts. I could not find a way to provide a link to a single message, but this 
is the thread -

http://www.postgresql-archive.org/a-JOIN-to-a-VIEW-seems-slow-tt5983241.html#none

and the relevant post is the third one down, dated 21st September.

You will have to read the whole message for the details, but the key point was 
the difference between these two queries -

2. ===

EXPLAIN SELECT * FROM ar_trans WHERE tran_type = ‘ar_inv’ AND tran_row_id = 1;

QUERY PLAN  
  
---
Append  (cost=0.29..8.32 rows=1 width=117)
   ->  Index Scan using ar_tran_inv_pkey on ar_tran_inv  (cost=0.29..8.31 
rows=1 width=46)
 Index Cond: (row_id = 1)
 Filter: posted
(4 rows)

This is a select against the view ‘ar_trans’. It has worked out that the 
underlying table to use is ‘ar_tran_inv’, and performed an indexed read.

4. ===

EXPLAIN SELECT * FROM ar_trans_due a LEFT JOIN ar_trans b ON b.tran_type = 
a.tran_type AND b.tran_row_id = a.tran_row_id where a.row_id = 1;

 QUERY PLAN 

-
Hash Right Join  (cost=8.32..2072.99 rows=1 width=169)
   Hash Cond: ((('ar_inv'::text) = (a.tran_type)::text) AND (ar_tran_inv.row_id 
= a.tran_row_id))
   ->  Append  (cost=0.00..1739.47 rows=43357 width=117)
 ->  Seq Scan on ar_tran_inv  (cost=0.00..676.01 rows=21601 width=46)
   Filter: posted
 ->  Seq Scan on ar_tran_crn  (cost=0.00..13.88 rows=155 width=124)
   Filter: posted
 ->  Seq Scan on ar_tran_rec  (cost=0.00..616.01 rows=21601 width=40)
   Filter: posted
   ->  Hash  (cost=8.31..8.31 rows=1 width=52)
 ->  Index Scan using ar_trans_due_pkey on ar_trans_due a  
(cost=0.29..8.31 rows=1 width=52)
   Index Cond: (row_id = 1)
(12 rows)

Here I have set up a join against the view ‘ar_trans’. It seems to have all the 
information necessary to perform an indexed read, but instead it performs a 
sequential scan of all three of the underlying tables.

If anyone wants to take this further, maybe this is a good place to start.

I do have a workaround. It is not pretty – denormalise my data to avoid the 
need for a join against the view. But it works, so there is no longer any 
urgency on my part.

Thanks

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On Wednesday, October 4, 2017 06:07 PM Jan de Visser wrote:

> On Monday, October 2, 2017 2:32:34 AM EDT Frank Millman wrote:
> > 
> > Just checking – is this under investigation, or is this thread considered 
> > closed?
> 
> That's not how it works. This is a community list; if somebody finds your 
> problem interesting you will get feedback, but there is no guarantee you will 
> get any.
> 
I appreciate the reply, Jan, but I am actually aware of that.
I had posted my query earlier, with full details, and received some responses. 
I provided additional information as requested, but then the responses dried 
up. I was just wondering why. If the answer is ‘lack of interest’, I am quite 
ok with that. I do have a workaround, so I will bash on regardless.
Thanks
Frank
 

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

From: Frank Millman 
Sent: Friday, September 22, 2017 7:34 AM
To: pgsql-general@postgresql.org 
Subject: Re: a JOIN to a VIEW seems slow


On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote:

>  
>  On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:

>  > Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
>  > query?
>  
 >  > Here it is -
> 
> https://explain.depesz.com/s/cwm
> 

Just checking – is this under investigation, or is this thread considered 
closed?

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Frank Millman wrote:
>  
>  On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:

>  > Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
>  > query?

>  Here it is -
>  
>  https://explain.depesz.com/s/cwm
>  

There is one thing I have not mentioned. I am pretty sure it has no effect on 
the outcome, but just in case, here it is.

The underlying physical tables, ‘ar_tran_inv’, ‘ar_tran_crn’, and 
‘ar_tran_rec’, have this index declared -

CREATE UNIQUE INDEX _ar_tran_inv ON ar_tran_inv (tran_number) WHERE 
deleted_id = 0;

and similar for the other two tables.

I have not included the column ‘deleted_id’ in the VIEW ‘ar_trans’, so I cannot 
add ‘WHERE deleted_id = 0’ to any queries.

This could mean a slow result if sorting by ‘tran_number’ or joining on 
‘tran_number’.

However, as this particular query joins on ‘tran_type’ (a literal string) and 
‘tran_row_id’ (the primary key to the underlying table), I don’t think  it 
causes a problem.

[UPDATE] I actually tried adding ‘deleted_id’ to the VIEW, and including it in 
the WHERE clause, but the timings did not improve.

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Merlin Moncure wrote:

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman <fr...@chagford.com> wrote:
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_30 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30'
> THEN q.balance ELSE 0 END
> ) AS "balance_60 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_90 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
> ) AS "balance_120 AS [DECTEXT]"
> FROM
> (SELECT
> due_trans.cust_row_id,
> due_trans.tran_date,
> trans_due.amount_cust +
> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)
> AS balance
> FROM prop.ar_trans_due trans_due
> LEFT JOIN prop.ar_trans due_trans ON
> due_trans.tran_type = trans_due.tran_type
> AND due_trans.tran_row_id = trans_due.tran_row_id
> WHERE due_trans.tran_date <= '2015-09-30'
> ) AS q
> GROUP BY q.cust_row_id
> ORDER BY q.cust_row_id;


> What is the performance with this portion simplified out?

> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)

> Change that to just '0' and rerun the query.  If timings are good, I
> think we want to explore converting this to LATERAL type join.  I
> think (but am not sure) this is defeating the optimizer.   Also, is
> this the actual query you want to run quickly?  You are not filtering
> on cust_row_id?


It makes a big difference – the query runs in 0.18 seconds.

This query can be used to return the age analysis for a single debtor or for 
all debtors, so yes I would sometimes run it without filtering.

A couple of comments -

1. My app supports 3 databases, PostgreSQL, SqlServer, and SQLite3. I am trying 
to keep my SQL as generic as possible. However, if I have to use something that 
is PostgreSQL-specific, I may have to live with that.

2. This is probably irrelevant but here is the query plan that SQLite3 creates -

3|0|0|SCAN TABLE ar_tran_inv
4|0|0|SCAN TABLE ar_tran_crn
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|0|SCAN TABLE ar_tran_rec
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN TABLE ar_trans_due AS trans_due
0|1|1|SEARCH SUBQUERY 1 AS due_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
9|0|0|SCAN TABLE ar_tran_inv
10|0|0|SCAN TABLE ar_tran_crn
8|0|0|COMPOUND SUBQUERIES 9 AND 10 (UNION ALL)
11|0|0|SCAN TABLE ar_tran_rec
7|0|0|COMPOUND SUBQUERIES 8 AND 11 (UNION ALL)
6|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
6|1|1|SEARCH SUBQUERY 7 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 12
15|0|0|SCAN TABLE ar_tran_inv
16|0|0|SCAN TABLE ar_tran_crn
14|0|0|COMPOUND SUBQUERIES 15 AND 16 (UNION ALL)
17|0|0|SCAN TABLE ar_tran_rec
13|0|0|COMPOUND SUBQUERIES 14 AND 17 (UNION ALL)
12|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
12|1|1|SEARCH SUBQUERY 13 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 18
21|0|0|SCAN TABLE ar_tran_inv
22|0|0|SCAN TABLE ar_tran_crn
20|0|0|COMPOUND SUBQUERIES 21 AND 22 (UNION ALL)
23|0|0|SCAN TABLE ar_tran_rec
19|0|0|COMPOUND SUBQUERIES 20 AND 23 (UNION ALL)
18|0|0|SEARCH TABLE ar_trans_alloc AS trans_alloc USING INDEX 
_ar_trans_alloc_due (due_row_id=?)
18|1|1|SEARCH SUBQUERY 19 AS alloc_trans USING AUTOMATIC COVERING INDEX 
(tran_type=? AND tran_row_id=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 24
27|0|0|SCAN TABLE ar_tran_inv
28|0|0|SCAN TABLE ar_tran_c

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:

> Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> query?

Here it is -

https://explain.depesz.com/s/cwm

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> 
> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <fr...@chagford.com> wrote:
> >
> > I did not get any response to this, but I am still persevering, and feel
> > that I am getting closer. Instead of waiting 26 minutes for a result, I
> > realise that I can learn a lot by using EXPLAIN. This is what I have found
> > out.
> 
 > Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute 
 > query?
> 
 
I’m working on it, but my machine is playing up and it is getting late, so I 
will try again tomorrow.
A passing comment – the 26 minute query is more complex, so will need some 
explaining (no pun intended). I was hoping that my simplified example would 
illustrate what I think is the problem.
Anyway, here is the query -
SELECT q.cust_row_id,
SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
) AS "balance_curr AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31' THEN 
q.balance ELSE 0 END
) AS "balance_30 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30' THEN 
q.balance ELSE 0 END
) AS "balance_60 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31' THEN 
q.balance ELSE 0 END
) AS "balance_90 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
) AS "balance_120 AS [DECTEXT]"
FROM
(SELECT 
due_trans.cust_row_id,
due_trans.tran_date,
trans_due.amount_cust +
COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
FROM prop.ar_trans_alloc trans_alloc
LEFT JOIN prop.ar_trans alloc_trans ON
alloc_trans.tran_type = trans_alloc.tran_type
AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
WHERE trans_alloc.due_row_id = trans_due.row_id
AND alloc_trans.tran_date <= '2015-09-30'
), 0)
AS balance
FROM prop.ar_trans_due trans_due
LEFT JOIN prop.ar_trans due_trans ON
due_trans.tran_type = trans_due.tran_type
AND due_trans.tran_row_id = trans_due.tran_row_id
WHERE due_trans.tran_date <= '2015-09-30'
) AS q
GROUP BY q.cust_row_id
ORDER BY q.cust_row_id;

I will report back with the EXPLAIN ANALYSE tomorrow.

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

On 2017-09-18 Frank Millman wrote:
> 
> Here are the timings for running the query on identical data sets using 
> Postgresql, Sql Server, and Sqlite3 -
> 
> PostgreSQL -
> Method 1 - 0.28 sec
> Method 2 – 1607 sec, or 26 minutes
> 
> Sql Server -
> Method 1 – 0.33 sec
> Method 2 – 1.8 sec
> 
> Sqlite3 -
> Method 1 – 0.15 sec
> Method 2 – 1.0 sec
> 
> It seems that Sql Server and Sqlite3 are able to analyse the ‘join’, and 
> execute an indexed read against the underlying physical tables.
> 

I did not get any response to this, but I am still persevering, and feel that I 
am getting closer. Instead of waiting 26 minutes for a result, I realise that I 
can learn a lot by using EXPLAIN. This is what I have found out.

To recap, I have the following tables -

1. ‘ar_tran_inv’, to store invoices
2. ‘ar_tran_crn’, to store credit notes
3. ‘ar_tran_rec’ to store receipts

This is a subset of their common columns -
row_id INT SERIAL PRIMARY KEY,
tran_number VARCHAR,
posted BOOL,

I have created a VIEW called ‘ar_trans’ to combine them -

CREATE VIEW ar_trans AS
  SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_inv WHERE posted = ‘1’
  UNION ALL
  SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_crn WHERE posted = ‘1’
  UNION ALL
  SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_rec WHERE posted = ‘1’

I have another table called ‘ar_trans_due’, where a row is inserted whenever a 
row is inserted into any of the three transaction tables. To identify the 
source transaction, I have the following columns -
tran_type VARCHAR – can be ‘ar_inv’, ‘ar_crn’, or ‘ar_rec’
tran_row_id INT – the primary key of the originating transaction

Now here are my tests -

1. ===

SELECT tran_type, tran_row_id FROM ar_trans_due WHERE row_id = 1;

tran_type | tran_row_id 
---+-
ar_inv|   1
(1 row)

Just to give me some sample data to work with.

2. ===

EXPLAIN SELECT * FROM ar_trans WHERE tran_type = ‘ar_inv’ AND tran_row_id = 1;

QUERY PLAN  
   
---
Append  (cost=0.29..8.32 rows=1 width=117)
   ->  Index Scan using ar_tran_inv_pkey on ar_tran_inv  (cost=0.29..8.31 
rows=1 width=46)
 Index Cond: (row_id = 1)
 Filter: posted
(4 rows)

This is a select against the view. It has worked out that the underlying table 
to use is ‘ar_tran_inv’, and performed an indexed read.

3. ===

EXPLAIN SELECT * FROM ar_trans_due a LEFT JOIN ar_tran_inv b ON b.row_id = 
a.tran_row_id where a.row_id = 1;

  QUERY PLAN
   
---
Nested Loop Left Join  (cost=0.58..16.62 rows=1 width=142)
   ->  Index Scan using ar_trans_due_pkey on ar_trans_due a  (cost=0.29..8.31 
rows=1 width=52)
 Index Cond: (row_id = 1)
   ->  Index Scan using ar_tran_inv_pkey on ar_tran_inv b  (cost=0.29..8.30 
rows=1 width=90)
 Index Cond: (row_id = a.tran_row_id)
(5 rows)

Here I have selected a row from ar_trans_due, and joined the underlying 
physical table directly. It uses an indexed read to perform the join.

4. ===

EXPLAIN SELECT * FROM ar_trans_due a LEFT JOIN ar_trans b ON b.tran_type = 
a.tran_type AND b.tran_row_id = a.tran_row_id where a.row_id = 1;

 QUERY PLAN 
 
-
Hash Right Join  (cost=8.32..2072.99 rows=1 width=169)
   Hash Cond: ((('ar_inv'::text) = (a.tran_type)::text) AND (ar_tran_inv.row_id 
= a.tran_row_id))
   ->  Append  (cost=0.00..1739.47 rows=43357 width=117)
 ->  Seq Scan on ar_tran_inv  (cost=0.00..676.01 rows=21601 width=46)
   Filter: posted
 ->  Seq Scan on ar_tran_crn  (cost=0.00..13.88 rows=155 width=124)
   Filter: posted
 ->  Seq Scan on ar_tran_rec  (cost=0.00..616.01 rows=21601 width=40)
   Filter: posted
   ->  Hash  (cost=8.31..8.31 rows=1 width=52)
 ->  Index Scan using ar_trans_due_pkey on ar_trans_due a  
(cost=0.29..8.31 rows=1 width=52)
   Index Cond: (row_id = 1)
(12 rows)

Here I have selected the same row, and joined the view ‘ar_trans’. It seems to 
have all the information necessary to perform an indexed read, but instead it 
performs a sequen

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Merlin Moncure wrote:
On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote:
>
> >
> > The PostgreSQL cannot to push join - in slow case, the UNIONS should be done
> > first - and it requires full scan ar_tran_inv - used filter (posted AND
> > (deleted_id = 0) is not too effective - maybe some composite or partial
> > index helps.
> 
> In my testing JOINS can push through UNION ALL.   Why do we need to
> materialize  union first?  What version is this?
> 

I am using version 9.4.4 on Fedora 22.
Frank Millman

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

2017-09-14 15:09 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:



  2017-09-14 14:59 GMT+02:00 Frank Millman <fr...@chagford.com>:

Pavel Stehule wrote:

2017-09-14 10:14 GMT+02:00 Frank Millman <fr...@chagford.com>:

  Hi all

  This is a follow-up to a recent question I posted regarding a slow query. 
I thought that the slowness was caused by the number of JOINs in the query, but 
with your assistance I have found the true reason. I said in the previous 
thread that the question had become academic, but now that I understand things 
better, it is no longer academic as it casts doubt on my whole approach.

  I have split my AR transaction table into three physical tables – 
ar_tran_inv, ar_tran_crn, ar_tran_rec. I will probably add others at some 
point, such as ar_tran_jnl.

  I then create a VIEW to view all transactions combined. The view is 
created like this -

  CREATE VIEW ar_trans AS
SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ... 
FROM ar_tran_inv WHERE posted = ‘1’
UNION ALL
SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ... 
FROM ar_tran_crn WHERE posted = ‘1’
UNION ALL
SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ... 
FROM ar_tran_rec WHERE posted = ‘1’

  I have another table called ‘ar_trans_due’, to keep track of outstanding 
transactions. All of the three transaction types generate entries into this 
table. To identify the source of the transaction, I have created columns in 
ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row into 
‘ar_tran_inv’, I invoke this -

INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES 
(‘ar_inv’, ar_tran_inv.row_id, ...), and similar for the other transaction 
types. It is handled by a Python program, and it all happens within a 
transaction.

  When I view a row in ar_trans_due, I want to retrieve data from the 
source transaction, so I have this -

SELECT * FROM ar_trans_due a
LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id = 
a.tran_row_id

  I understand that PostgreSQL must somehow follow a path from the view 
‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would execute 
the equivalent of SELECT * FROM ar_tran_inv WHERE row_id = a.tran_row_id AND 
posted = ‘1’.

  If this was the case, it would be an indexed read, and very fast. 
Instead, according to EXPLAIN, it performs a sequential scan of the 
‘ar_tran_inv’ table.

  It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it 
uses a Bitmap Heap Scan on those. I assume that is because the tables are 
currently empty.

  Is this analysis correct?

please, send EXPLAIN ANALYZE result :) 


I tried to reduce this to its simplest form.

Here is a SQL statement -

SELECT *
FROM ccc.ar_trans_due a
LEFT JOIN ccc.ar_trans b ON
b.tran_type = a.tran_type AND b.tran_row_id = a.tran_row_id
WHERE a.row_id = 1

ar_trans_due is a physical table, ar_trans is a view.

It takes about 28ms. Here is the explain - https://explain.depesz.com/s/8YY


> The PostgreSQL cannot to push join - in slow case, the UNIONS should be done 
> first - and it requires full scan ar_tran_inv - used filter (posted AND 
> (deleted_id = 0) is not too effective - maybe some composite or partial index 
> helps.
> 
> The fast query doesn't contains unions - so there are bigger space for 
> optimizer - ar_tran_inv is filtered effective - by primary key. 
> 
> So main problem is impossible to push information a.row_id = 1 to deep to 
> query.
> 

Sorry for banging on about this, but someone might be interested in the 
following timings.

The only solution I could find was to ‘denormalise’ (if that is a word) and 
create additional columns on ar_trans_due for cust_row_id and tran_date, to 
avoid using any joins.

Once I had done that, I could run my query two ways – 
  1. using the newly created columns
  2. as before, using a join to the view, which in turn retrieved data from the 
underlying tables.

This was a more complex query than the example above – details available on 
request.

Here are the timings for running the query on identical data sets using 
Postgresql, Sql Server, and Sqlite3 -

PostgreSQL -
Method 1 - 0.28 sec
Method 2 – 1607 sec, or 26 minutes

Sql Server -
Method 1 – 0.33 sec
Method 2 – 1.8 sec

Sqlite3 -
Method 1 – 0.15 sec
Method 2 – 1.0 sec

It seems that Sql Server and Sqlite3 are able to analyse the ‘join’, and 
execute an indexed read against the underlying physical tables.

Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Pavel Stehule wrote:

2017-09-14 10:14 GMT+02:00 Frank Millman <fr...@chagford.com>:

  Hi all

  This is a follow-up to a recent question I posted regarding a slow query. I 
thought that the slowness was caused by the number of JOINs in the query, but 
with your assistance I have found the true reason. I said in the previous 
thread that the question had become academic, but now that I understand things 
better, it is no longer academic as it casts doubt on my whole approach.

  I have split my AR transaction table into three physical tables – 
ar_tran_inv, ar_tran_crn, ar_tran_rec. I will probably add others at some 
point, such as ar_tran_jnl.

  I then create a VIEW to view all transactions combined. The view is created 
like this -

  CREATE VIEW ar_trans AS
SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_inv WHERE posted = ‘1’
UNION ALL
SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_crn WHERE posted = ‘1’
UNION ALL
SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_rec WHERE posted = ‘1’

  I have another table called ‘ar_trans_due’, to keep track of outstanding 
transactions. All of the three transaction types generate entries into this 
table. To identify the source of the transaction, I have created columns in 
ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row into 
‘ar_tran_inv’, I invoke this -

INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES (‘ar_inv’, 
ar_tran_inv.row_id, ...), and similar for the other transaction types. It is 
handled by a Python program, and it all happens within a transaction.

  When I view a row in ar_trans_due, I want to retrieve data from the source 
transaction, so I have this -

SELECT * FROM ar_trans_due a
LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id = 
a.tran_row_id

  I understand that PostgreSQL must somehow follow a path from the view 
‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would execute 
the equivalent of SELECT * FROM ar_tran_inv WHERE row_id = a.tran_row_id AND 
posted = ‘1’.

  If this was the case, it would be an indexed read, and very fast. Instead, 
according to EXPLAIN, it performs a sequential scan of the ‘ar_tran_inv’ table.

  It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it uses 
a Bitmap Heap Scan on those. I assume that is because the tables are currently 
empty.

  Is this analysis correct?

please, send EXPLAIN ANALYZE result :) 


I tried to reduce this to its simplest form.

Here is a SQL statement -

SELECT *
FROM ccc.ar_trans_due a
LEFT JOIN ccc.ar_trans b ON
b.tran_type = a.tran_type AND b.tran_row_id = a.tran_row_id
WHERE a.row_id = 1

ar_trans_due is a physical table, ar_trans is a view.

It takes about 28ms. Here is the explain - https://explain.depesz.com/s/8YY

Then I changed it to join each of the physical tables, instead of the view -

SELECT *
FROM ccc.ar_trans_due a
LEFT JOIN ccc.ar_tran_inv b ON
b.row_id = a.tran_row_id
LEFT JOIN ccc.ar_tran_crn c ON
c.row_id = a.tran_row_id
LEFT JOIN ccc.ar_tran_rec d ON
d.row_id = a.tran_row_id
WHERE a.row_id = 1

This takes just over 1ms. Here is the explain - 
https://explain.depesz.com/s/U29h

I tried setting enable_seq_scan to off – it ran even slower!

Frank

[GENERAL] a JOIN to a VIEW seems slow

[Frank Millman]

Hi all

This is a follow-up to a recent question I posted regarding a slow query. I 
thought that the slowness was caused by the number of JOINs in the query, but 
with your assistance I have found the true reason. I said in the previous 
thread that the question had become academic, but now that I understand things 
better, it is no longer academic as it casts doubt on my whole approach.

I have split my AR transaction table into three physical tables – ar_tran_inv, 
ar_tran_crn, ar_tran_rec. I will probably add others at some point, such as 
ar_tran_jnl.

I then create a VIEW to view all transactions combined. The view is created 
like this -

CREATE VIEW ar_trans AS
  SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_inv WHERE posted = ‘1’
  UNION ALL
  SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_crn WHERE posted = ‘1’
  UNION ALL
  SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ... FROM 
ar_tran_rec WHERE posted = ‘1’

I have another table called ‘ar_trans_due’, to keep track of outstanding 
transactions. All of the three transaction types generate entries into this 
table. To identify the source of the transaction, I have created columns in 
ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row into 
‘ar_tran_inv’, I invoke this -

  INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES (‘ar_inv’, 
ar_tran_inv.row_id, ...), and similar for the other transaction types. It is 
handled by a Python program, and it all happens within a transaction.

When I view a row in ar_trans_due, I want to retrieve data from the source 
transaction, so I have this -

  SELECT * FROM ar_trans_due a
  LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id = 
a.tran_row_id

I understand that PostgreSQL must somehow follow a path from the view 
‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would execute 
the equivalent of SELECT * FROM ar_tran_inv WHERE row_id = a.tran_row_id AND 
posted = ‘1’.

If this was the case, it would be an indexed read, and very fast. Instead, 
according to EXPLAIN, it performs a sequential scan of the ‘ar_tran_inv’ table.

It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it uses a 
Bitmap Heap Scan on those. I assume that is because the tables are currently 
empty.

Is this analysis correct?

If so, is there any way to force it to use an indexed read?

Thanks for any pointers.

Frank Millman

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

From: Chris Travers 
Sent: Tuesday, September 12, 2017 3:36 PM
To: Frank Millman 
Cc: Postgres General 
Subject: Re: [GENERAL] Joining 16 tables seems slow

Chris Travers wrote:

On Tue, Sep 12, 2017 at 3:15 PM, Frank Millman <fr...@chagford.com> wrote:


  2017-09-12 12:39 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:


> please use https://explain.depesz.com/ for both plans (slow, fast)

  Here are the results - 

  sql_slow - https://explain.depesz.com/s/9vn3

  sql_fast - https://explain.depesz.com/s/oW0F

> 
> I am not convinced that the nested loop is a problem here.  I cannot think of 
> a faster join plan than a nested loop when you only have one iteration of the 
> loop (and looking through I did not see any loop counts above 1).
> 
> If you read and count ms carefully you will find that ar_tran_inv is scanned 
> 6 times and each of these times is taking about 25ms.  25x6 is half of your 
> query time right there and then you have the overhead in the joins on top of 
> that.  Quick eyeball estimates is that this is where approx 200ms of your 
> query time comes from.  Looking at this in more detail it doesn't look 
> 
> This is not a problem with too many tables in the join but the fact that you 
> are joining the same tables in multiple times in ways you end up needing to 
> repeatedly sequentially scan them.
> 
> I also don't think an index is going to help unless you have accounting data 
> going way back (since you are looking for about a year's worth of data) or 
> unless 90% of your transactions get marked as deleted.  So I think you are 
> stuck with the sequential scans on this table and optimizing will probably 
> mean reducing the number of times you scan that table.

Ok, I have a bit more information.

A couple of general comments first.

1. This is now purely an academic exercise. The SQL query that triggered this 
thread is unnecessarily complex, and I have a better solution. However, I think 
it is still worth the effort to understand what is going on.

2. explain.depesz.com is a brilliant tool – thanks for suggesting it.

As Pavel and Chris have pointed out, the problem seems to be that ar_tran_inv 
is scanned six times. The question is why? I have an idea, but I will need some 
assistance.

I have split my transaction table into three separate tables – ar_tran_inv, 
ar_tran_crn, ar_tran_rec. I then have a VIEW called ar_trans to view the 
transactions in total.

Each physical table has a primary key called ‘row_id’, and an index on 
‘tran_date’.

The view is created like this -

CREATE VIEW ccc.ar_trans AS
  SELECT ‘ar_inv’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_inv
  UNION ALL
  SELECT ‘ar_crn’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_crn
  UNION ALL
  SELECT ‘ar_rec’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_rec

In my sql_slow query, I have this 5 times, using different dates -

LEFT JOIN ccc.ar_trans trans_alloc_curr ON
trans_alloc_curr.tran_type = alloc_curr.tran_type AND
trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
trans_alloc_curr.tran_date <= '2015-09-30'

Is it possible that it has to perform a full scan of each of the underlying 
tables to make the join?

If so, is it possible to speed this up?

Frank

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

2017-09-12 12:39 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:



  2017-09-12 12:25 GMT+02:00 Frank Millman <fr...@chagford.com>:

Pavel Stehule wrote:

2017-09-12 9:36 GMT+02:00 Frank Millman <fr...@chagford.com>:

  Pavel Stehule wrote:
  > 
  > 2017-09-12 8:45 GMT+02:00 Frank Millman <fr...@chagford.com>:

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 
LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the 
filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE 
statements.

My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
  > 
  > please send result of explain analyze
  > 
  > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher 
number 14 maybe 16
  > 
  I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

  I have attached files containing my SQL command, and the results of 
EXPLAIN ANALYSE

> please use https://explain.depesz.com/ for both plans (slow, fast)


Here are the results - 

sql_slow - https://explain.depesz.com/s/9vn3

sql_fast - https://explain.depesz.com/s/oW0F

  I don't see any issue there - it looks like some multi dimensional query and 
it should not be well optimized due not precious estimations. The slow query 
has much more complex - some bigger logic is under nested loop - where 
estimation is not fully correct, probably due dependencies between columns.

  what does SET enable_nestloop to off;
> 
> from statistics - the ar_tran_inv table is scanned 6x in slow query and 
> 2times in fast query. Maybe there should be some index 
>

Setting enable_nestloop to off makes no difference.

Setting from_collapse_limit and join_collapse_limit to 16, as suggested by Tom, 
actually slowed it down.

I mentioned before that I was running this from python, which complicated it 
slightly. I have now saved the command to a file on the Fedora side, so I can 
execute it in psql using the ‘\i’ command. It makes life easier, and I can use 
‘\timing’ to time it. It shows exactly the same results.

It could be an index problem, but I have just double-checked that, if I remove 
the lines from the body of the statement that actually select from the joined 
tables, it makes virtually no difference. However, maybe the planner checks to 
see what indexes it has before preparing the query, so that does not rule it 
out as a possibility.

I will play with it some more tomorrow, when my  brain is a bit fresher. I will 
report back with any results.

Frank

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Pavel Stehule wrote:

2017-09-12 9:36 GMT+02:00 Frank Millman <fr...@chagford.com>:

  Pavel Stehule wrote:
  > 
  > 2017-09-12 8:45 GMT+02:00 Frank Millman <fr...@chagford.com>:

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
  > 
  > please send result of explain analyze
  > 
  > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher 
number 14 maybe 16
  > 
  I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

  I have attached files containing my SQL command, and the results of EXPLAIN 
ANALYSE

> please use https://explain.depesz.com/ for both plans (slow, fast)


Here are the results - 

sql_slow - https://explain.depesz.com/s/9vn3

sql_fast - https://explain.depesz.com/s/oW0F

Frank
 

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Ron Johnson wrote:
> 
On 09/12/2017 01:45 AM, Frank Millman wrote:

Hi all

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first version 
takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, 
the time for the second one seems to taken in setting up the joins, because if 
I omit selecting anything from the joined tables, it still takes 0.23 seconds.

Exactly the same exercise on Sql Server results in 0.06 seconds for both 
versions.

I realise that, if I was selecting a large number of rows, 0.23 seconds is 
trivial and the overall result could be different. But still, it seems odd.

> Just out of curiosity, what if you PREPARE the statement, and take multiple 
> timings?


My setup is a bit complicated, as I am executing the commands from a python 
program on Windows against a PostgreSQL database on Fedora, so I hope I did it 
correctly!With that caveat, the results are that the time was reduced from 0.23 
seconds to 0.22 seconds. The difference is consistent, so I think it is 
real.Frank 

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Pavel Stehule wrote:
> 
> 2017-09-12 8:45 GMT+02:00 Frank Millman <fr...@chagford.com>:

  I am using 9.4.4 on Fedora 22.

  I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

  My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
> 
> please send result of explain analyze
> 
> you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher number 
> 14 maybe 16
> 
I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

I have attached files containing my SQL command, and the results of EXPLAIN 
ANALYSE

Frank
 SELECT a.row_id,
(SELECT (b.inv_net_tot_cus + b.inv_tax_tot_cus + b.crn_net_tot_cus + 
b.crn_tax_tot_cus
+ b.jnl_tot_cus + b.rec_net_tot_cus + b.rec_dsc_tot_cus + b.rec_dtx_tot_cus)
FROM ccc.ar_cust_totals b
WHERE b.cust_row_id = a.row_id AND b.tran_date <= '2015-09-30'
ORDER BY b.tran_date DESC LIMIT 1)
as "balance_cust AS [DECTEXT]",

COALESCE(SUM(due_curr.amount_cust), 0) +
COALESCE(SUM(alloc_curr.alloc_cust + alloc_curr.disc_cust), 0)
AS "bal_cust_curr AS [DECTEXT]",
COALESCE(SUM(due_30.amount_cust), 0) +
COALESCE(SUM(alloc_30.alloc_cust + alloc_30.disc_cust), 0)
AS "bal_cust_30 AS [DECTEXT]",
COALESCE(SUM(due_60.amount_cust), 0) +
COALESCE(SUM(alloc_60.alloc_cust + alloc_60.disc_cust), 0)
AS "bal_cust_60 AS [DECTEXT]",
COALESCE(SUM(due_90.amount_cust), 0) +
COALESCE(SUM(alloc_90.alloc_cust + alloc_90.disc_cust), 0)
AS "bal_cust_90 AS [DECTEXT]",
COALESCE(SUM(due_120.amount_cust), 0) +
COALESCE(SUM(alloc_120.alloc_cust + alloc_120.disc_cust), 0)
AS "bal_cust_120 AS [DECTEXT]"

FROM ccc.ar_customers a
LEFT JOIN ccc.ar_trans trans ON trans.cust_row_id = a.row_id

LEFT JOIN ccc.ar_trans_due due_curr ON
due_curr.tran_type = trans.tran_type AND due_curr.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_curr ON
alloc_curr.due_row_id = due_curr.row_id
LEFT JOIN ccc.ar_trans trans_alloc_curr ON
trans_alloc_curr.tran_type = alloc_curr.tran_type AND
trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
trans_alloc_curr.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_30 ON
due_30.tran_type = trans.tran_type AND due_30.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-07-31' AND trans.tran_date <= '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_30 ON
alloc_30.due_row_id = due_30.row_id
LEFT JOIN ccc.ar_trans trans_alloc_30 ON
trans_alloc_30.tran_type = alloc_30.tran_type AND
trans_alloc_30.tran_row_id = alloc_30.tran_row_id AND
trans_alloc_30.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_60 ON
due_60.tran_type = trans.tran_type AND due_60.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-06-30' AND trans.tran_date <= '2015-07-31'
LEFT JOIN ccc.ar_trans_alloc alloc_60 ON
alloc_60.due_row_id = due_60.row_id
LEFT JOIN ccc.ar_trans trans_alloc_60 ON
trans_alloc_60.tran_type = alloc_60.tran_type AND
trans_alloc_60.tran_row_id = alloc_60.tran_row_id AND
trans_alloc_60.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_90 ON
due_90.tran_type = trans.tran_type AND due_90.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-05-31' AND trans.tran_date <= '2015-06-30'
LEFT JOIN ccc.ar_trans_alloc alloc_90 ON
alloc_90.due_row_id = due_90.row_id
LEFT JOIN ccc.ar_trans trans_alloc_90 ON
trans_alloc_90.tran_type = alloc_90.tran_type AND
trans_alloc_90.tran_row_id = alloc_90.tran_row_id AND
trans_alloc_90.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_120 ON
due_120.tran_type = trans.tran_type AND due_120.tran_row_id = 
trans.tran_row_id
AND trans.tran_date <= '2015-05-31'
LEFT JOIN ccc.ar_trans_alloc alloc_120 ON
alloc_120.due_row_id = due_120.row_id
LEFT JOIN ccc.ar_trans trans_alloc_120 ON
trans_alloc_120.tran_type = alloc_120.tran_type AND
trans_alloc_120.tran_row_id = alloc_120.tran_row_id AND
trans_alloc_120.tran_date <= '2015-09-30'

WHERE a.ledger_row_id = ? AND a.party_row_id = ? AND a.deleted_id = ?
GROUP BY a.row_id
('HashAggregate  (cost=11123.83..11211.17 rows=1 width=234) (actual 
time=299.781..299.782 rows=1 loops=1)',)
('  Group Key: a.row_id',)
('  ->  Hash Right Join  (cost=9833.36..11122.59 rows=31 width=234) (actual 
time=295.962..296.496 rows=1801 loops=1)',)
('Hash Cond: (("*SELECT* 

[GENERAL] Joining 16 tables seems slow

[Frank Millman]

Hi all

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first version 
takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, 
the time for the second one seems to taken in setting up the joins, because if 
I omit selecting anything from the joined tables, it still takes 0.23 seconds.

Exactly the same exercise on Sql Server results in 0.06 seconds for both 
versions.

I realise that, if I was selecting a large number of rows, 0.23 seconds is 
trivial and the overall result could be different. But still, it seems odd.

Is this normal, or should I investigate further?

Frank Millman

[GENERAL] Function not inserting rows

[Frank Foerster]

Hi,

i have the following question:

Given an empty database with only schema api_dev in it, a table and a
function is created as follows:

CREATE TABLE api_dev.item_texts
(
  item_id integer,
  item_text text
)
WITH (
  OIDS=FALSE
);


CREATE OR REPLACE FUNCTION api_dev.add_texts_to_item(
p_item_id integer,
p_item_texts text[])
  RETURNS boolean AS
$BODY$

BEGIN

   insert into api_dev.item_texts( item_id, item_text )
   (
select p_item_id, unnest( p_item_texts )
   );
   return true;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


When i call this function in pgadmin (3, 1.22.2) like this:

select * from api_dev.add_texts_to_item( 444, array['PGADM1', 'PGADM2'] );

i get the true-result and the table will have two rows:

444, PGADM1
444, PGADM2

Now (this is NOT a Python question), when i connect with the same user via
Python psycopg2 to the same database via the following function:

  def add_texts_to_item( self, item_id, texts ):
sql = "select * from api_dev.add_texts_to_item( %s, %s );"
self.cur_.execute( sql, (doc_id, isins, ) )
data = self.cur_.fetchone()

if data is None:
return None

return data[0]


I will also get the true result, but nothing is being added. But the
SQL-Statement that gets to the DB is identical. When i force a syntax error
into the statement to see the actual statement it creates, like this:

sql = "select * from api_dev.add_texts_to_item( %s, %s ); x x"
i get the following python-error:
psycopg2.ProgrammingError: FEHLER:  Syntaxfehler bei »s«
LINE 1: ...dd_texts_to_item( 1234, ARRAY['PSYCOPG1', 'PSYCOPG2'] ); s s

But the created statement looks syntax-wise identical to the
pgadmin-statement (except for the forced error of course):

select * from api_dev.add_texts_to_item( 444, array['PGADM1', 'PGADM2'] );


When i change the return type of the pgsql-function from true to false, i
will also get the respective result back in Python, so it is actually
calling the psql-function and i can also see it in the logs, but nothing
gets inserted.

I noticed this behavior first in a Linux 64 bit / 9.6.3 machine and then
reproduced this isolated sample on Windows 7 64 bit/ 9.6.2 version.

Any ideas ?


Thanks

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Frank van Vugt]

Hi Stephen,

Op maandag 13 februari 2017 09:10:42 schreef Stephen Frost:
> We should be able to get it addressed shortly.

Great, 'as always', I'd like to add!

Thanks for the great work, people. This cannot be stated too often...

> For your specific case

Thanks for the additional info, interesting.

> Yes, it's unfortunate that many users aren't really familiar with
> schema-level privileges.

Well, I didn't run into this issue with any of my db's that 'nicely' use 
tables in various schema's, it was actually the one 'older' db with everything 
in the public schema that brought it up, so maybe keeping one of those around 
isn't too bad an idea ;)




-- 

Best,




Frank.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Frank van Vugt]

Hi Tom/Stephen/Adrian,

Op zaterdag 11 februari 2017 15:28:55 schreef Tom Lane:
> I'm inclined to argue that it was a mistake to include any non-pinned
> objects in pg_init_privs.

> We might need to fix pg_dump too, but I think these entries in
> pg_init_privs should simply not be there.

Thanks for picking this up, I'll probably see this subject pop up on hackers 
and/or committers at some point ;)

Allow me to emphasize that this issue basically means that for v9.6 after 
restoring a dump created with the '-c' option one ends up in a situation that 
might be quite confusing for users that didn't have to pay much attention yet 
to handling priviliges... i.e. trying even a plain select on table_a in the 
public schema as a non-system user returns something like:
ERROR:  relation "table_a" does not exist



-- 

Best,




Frank.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Frank van Vugt]

Hi Adrian,

Op zaterdag 11 februari 2017 13:31:17 schreef Adrian Klaver:
> I see the same thing now.

Glad you do ;)

> That seems to cause a problem

Yeah, I originally ran into this when I noticed that on a restored db a 
regular user lost access to tables created by him in the public schema. 

Granting 'usage' solves it, but I expect this isn't suppose to happen.



-- 

Best,




Frank.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Frank van Vugt]

Hi Adrian,

Op zaterdag 11 februari 2017 13:02:29 schreef Adrian Klaver:
> What version of Postgres?

Ah, sorry, missed copying that in:

postgres=# select version();
   version
--
 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2, 64-bit
(1 row)



-- 

Best,




Frank.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Frank van Vugt]

L.S.

I noticed the following and wondered whether this is intentional or an 
oversight in pg_dump's '-c' option?

The clean option causes the public schema to be dropped and recreated, but 
this is done with the default schema priviliges, which are not the same as the 
ones assigned during create database:


*** USING PSQL

postgres=# create database publictest;

postgres=# \c publictest;

publictest=# \dn+
  List of schemas
  Name  |  Owner   |  Access privileges   |  Description   
+--+--+
 public | postgres | postgres=UC/postgres+| standard public schema
|  | =UC/postgres | 
(1 row)



*** USING SHELL

host:~ # pg_dump -c -f /tmp/publictest -h localhost -Fp -U postgres publictest



*** USING PSQL

publictest=# \i /tmp/publictest

publictest=# \dn+
List of schemas
  Name  |  Owner   | Access privileges |  Description   
+--+---+
 public | postgres |   | standard public schema
(1 row)

publictest=# grant usage on schema public to public;
GRANT
publictest=# grant create on schema public to public;
GRANT

testje=# \dn+
  List of schemas
  Name  |  Owner   |  Access privileges   |  Description   
+--+--+
 public | postgres | postgres=UC/postgres+| standard public schema
|  | =UC/postgres | 
(1 row)



-- 

Best,




Frank.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Difficulty modelling sales taxes

[Frank Millman]

From: amul sul 
Sent: Monday, January 02, 2017 12:42 PM
To: Frank Millman 
Cc: pgsql-general 
Subject: Re: [GENERAL] Difficulty modelling sales taxes

> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <fr...@chagford.com> wrote:
> > Hi all
> >
> > It is a bit ugly, because I have to use the ‘NVARCHAR code’ column from
> > tax_codes, not the primary key, but I think it would work.
> >
> NVARCHAR ?  Are you using PostgreSQL as database server?
> 


Oops, sorry.

I am testing with PostgreSQL and with SQL Server, so I was in the wrong mindset 
when I posted.

I should have said VARCHAR.

Frank

[GENERAL] Difficulty modelling sales taxes

[Frank Millman]

Hi all

I want to model sales taxes in a flexible manner. I need one table to define 
tax categories (e.g. VAT) and a subsidiary table to define tax codes within 
each category (e.g. Standard Rate).

CREATE TABLE tax_categories (
row_id SERIAL PRIMARY KEY,
category NVARCHAR NOT NULL,
description NVARCHAR NOT NULL,
CONSTRAINT _tax_cats UNIQUE (category));

CREATE TABLE tax_codes (
row_id SERIAL PRIMARY KEY,
category_id INT NOT NULL REFERENCES tax_categories,
code NVARCHAR NOT NULL,
description NVARCHAR NOT NULL,
CONSTRAINT _tax_codes UNIQUE (category_id, code));

Now I want to assign tax codes to product codes. As each product could 
potentially have more than one tax code, I need a many-to-many table.

My difficulty is that each product can have tax codes from different tax 
categories, but it cannot have more than one tax code from the same tax 
category. I am not sure how to model this ‘uniqueness’.

The best I can come up with is this -

CREATE TABLE prod_tax_codes (
product_id INT NOT NULL REFERENCES prod_codes,
category_id INT NOT NULL REFERENCES tax_categories,
tax_code NVARCHAR NOT NULL,
CONSTRAINT _prod_tax_code_1 UNIQUE (product_id, category_id),
CONSTRAINT _prod_tax_code_2 FOREIGN KEY (category_id, tax_code) REFERENCES 
tax_codes (category_id, code));

It is a bit ugly, because I have to use the ‘NVARCHAR code’ column from 
tax_codes, not the primary key, but I think it would work.

Does anyone have any better ideas?

Thanks

Frank Millman

Re: [GENERAL] Locking question

[Frank Millman]

From: Frank Millman 
Sent: Wednesday, October 26, 2016 10:42 AM
To: pgsql-general@postgresql.org 
Subject: [GENERAL] Locking question

> I am designing an inventory application, and I want to ensure that the stock 
> level of any item cannot go negative.

Thanks to all for some really great replies. Much food for thought there.

As mentioned previously, I am trying to avoid using PostgreSQL-specific 
techniques, as I need to support sqlite3 and SQL Server as well.

There is an additional complication that I forgot to mention in my original 
post.

For costing purposes, I want to run a FIFO system. This means I have to 
maintain separate entries for each receipt of stock, and allocate any sales of 
stock against the receipts ‘oldest first’.

Assume the following purchases -

2016-06-01  qty 5  Unit price $5.00
2016-06-02  qty 10  Unit price $5.50
2016-06-03  qty 15  Unit price $6.00

Quantity on hand after the third purchase is 30. Whether this should be 
maintained as a total somewhere, or derived from totalling the receipts, is a 
matter for debate, but I think that it is not relevant for this discussion.

Then assume the following sales -

2016-06-11  qty 8
2016-06-12  qty 12
2016-06-13  qty 16

The first sale will succeed, and will record a ‘cost of  sale’ of (5 x $5.00) + 
(3 x $5.50).
The second sale will succeed, and will record a ‘cost of  sale’ of (7 x $5.50) 
+ (5 x $6.00).
The third sale must be rejected, as there is insufficient stock.

This is how I propose to achieve this -

CREATE TABLE inv_rec
(row_id SERIAL PRIMARY KEY,
product_id INT REFERENCES inv_products,
rec_date DATE,
qty INT
unit_price DEC(15, 2));

CREATE TABLE inv_alloc
(row_id SERIAL PRIMARY KEY,
rec_id INT REFERENCES inv_rec,
qty INT);

INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
  VALUES (99, ‘2016-06-01’, 5, 5.00);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
  VALUES (99, ‘2016-06-02’, 10, 5.50);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
  VALUES (99, ‘2016-06-03’, 15, 6.00);

The sales will be handled at application level. Here is some pseudo code -

qty_to_allocate = sale_qty
cost_of_sale = 0

BEGIN TRANSACTION

SELECT a.row_id, a.unit_price,
  a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
  WHERE b.rec_id = a.row_id), 0) AS balance
FROM inv_rec a
WHERE a.product_id = 99
AND 
  a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
  WHERE b.rec_id = a.row_id), 0)
  > 0
ORDER BY a.rec_date
FOR UPDATE

for row in rows:
if row.balance >= qty_to_allocate:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –qty_to_allocate)
cost_of_sale += (qty_to_allocate * unit_price)
qty_to_allocate = 0
else:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –row.balance)
cost_of_sale += (row.balance * unit_price)
qty_to_allocate –= row.balance

if qty_to_allocate:  # i.e. insufficient stock
raise exception and ROLLBACK
else: 
COMMIT

My main concern is that this should be robust.

A secondary concern is that it should be reasonably efficient, but that is not 
a priority at this stage. If it became a problem, I would look at maintaining a 
‘balance’ column on each ‘inv_rec’.

Comments welcome.

Frank
 

Re: [GENERAL] Locking question

[Frank Millman]

From: hubert depesz lubaczewski 
Sent: Wednesday, October 26, 2016 10:46 AM
To: Frank Millman 
Cc: pgsql-general@postgresql.org 
Subject: Re: [GENERAL] Locking question

On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
> > 
> > I am designing an inventory application, and I want to ensure that the 
> > stock level of any item cannot go negative.
> > 
[...]
> 
> What I would do, is to add trigger on inv_alloc, than when you
> insert/update/delete row there, it updates appropriate row in inv_rec by
> correct number.
> 
> Then, I'd add check on inv_rec to make sure qty is never < 0.
> 

Thanks, depesz

I can see how that would work, but I have two comments.
1. I am writing my application to support 3 databases – PostgreSQL, sqlite3, 
and MS SQL Server. Because they are all so different when it comes to triggers 
and procedures, I am trying to avoid using them, and do as much within the 
application as possible.
2. I think you are suggesting maintaining a ‘balance’ column on inv_rec. This 
raises the question of whether or when you should create and maintain a column 
if the same information could be derived from other sources. I realise that 
this is a judgement call, and sometimes I struggle to get the balance right. Is 
this a situation where people would agree that it is warranted?
I would still appreciate some feedback as to whether my proposed solution would 
work.
Thanks
Frank
 

[GENERAL] Locking question

[Frank Millman]

Hi all

I am designing an inventory application, and I want to ensure that the stock 
level of any item cannot go negative.

Instead of maintaining a running balance per item, I store the original 
quantities received in one table (call it ‘inv_rec’), and any amounts removed 
in another table (call it ‘inv_alloc’).

CREATE TABLE inv_rec
(row_id SERIAL PRIMARY KEY,
product_id INT REFERENCES inv_products,
qty INT);

CREATE TABLE inv_alloc
(row_id SERIAL PRIMARY KEY,
rec_id INT REFERENCES inv_rec,
qty INT);

To get the balance of a particular item -

SELECT SUM(
a.qty + COALESCE(
  (SELECT SUM(b.qty) FROM inv_alloc b
  WHERE b.rec_id = a.row_id), 0))
FROM inv_rec a
WHERE a.product_id = 99;

To remove a quantity from a particular item -

INSERT INTO inv_alloc (rec_id, qty)
  VALUES (23, -1);

I want the application to check that there is sufficient quantity before 
attempting to execute the INSERT command.

If ok, it will look for a suitable row in ‘inv_rec’ to allocate against.

The danger of course is that, in a multi-user system, another user might have 
removed an additional quantity from the same item in between the SELECT and the 
INSERT.

I *think* that the solution is to BEGIN the transaction, then perform SELECT 
... WITH UPDATE, then proceed with INSERT and COMMIT if ok, else ROLLBACK.

Is this the correct approach, or am I missing something?

Thanks

Frank Millman

Re: [GENERAL] Unexpected result using floor() function

[Frank Millman]

On Tue, Mar 15, 2016 at 12:02 PM, Francisco Olarte wrote:


> Hi Frank:
> 
> This may byte you any day, so I wuld recommend doing
> 
> s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10 as
> numeric), 2) + 0.5)) as aux(v);
>   v  | pg_typeof
> -+---
>  473 | numeric
> (1 row)
> 
> which makes your intention clear.

Good advice. Thank you, Francisco
Frank

Re: [GENERAL] Unexpected result using floor() function

[Frank Millman]

> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
> 
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
> 
> Please can someone explain the anomaly.

I think I have a solution to my problem, but I would appreciate a review in 
case I have missed some corner cases.

I understand it better now. Here are some of the things I have learned.

1. In Python, 4.725 is assumed to be a float. You need some extra steps to turn 
it into a Decimal type. PostgreSQL seems to take the opposite approach – it is 
assumed to be numeric, unless you explicitly cast it to a float.

2. As pointed out, there are two forms of the power function.

test=> select pg_typeof(power(10, 2));
pg_typeof
--
double precision

test=> select pg_typeof(power(10., 2));
pg_typeof
--
numeric

I found that adding a decimal point after the 10 is the easiest way to force it 
to return a numeric.

Putting this together, my solution is -

test=> select floor(4.725 * power(10., 2) + 0.5);
floor 
---
   473

Can anyone see any problems with this?

Thanks

Frank
 

[GENERAL] Unexpected result using floor() function

[Frank Millman]

Hi all

I am running PostgreSQL 9.4.4 on Fedora 22.

SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.

SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.

Please can someone explain the anomaly.

Thanks

Frank Millman

Re: [GENERAL] BDR-Plugin make install on RHEL7.1

[Frank Nagel]

On Thu, 2015-10-29 at 08:33 -0400, Will McCormick wrote:
> Trying to get the bdr-plugin to install make install on RHEL7.1. Having some 
> issues with make of the plugin.
> 
> 
>
> # make -j4 -s all make -s install
> make: *** No rule to make target `make'.  Stop.
> make: *** Waiting for unfinished jobs

Your last command should probably look more like this:

# make -j4 -s all && make -s install




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Success story full text search

[Frank Langel]

Hi,

Does someone have a success story of using Postgres Full Search Capability
with significant data, lets say  50-100 GB  ?

Any pointers would be much appreciated

Thanks
Frank

Re: [GENERAL] newbie how to access the information scheme

[frank ernest]

Thanks


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] newbie how to access the information scheme

[frank ernest]

Hello, I'd like to see all the tables in my data base,
but can't figure out how to access th information scheme.

Thanks


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?

[frank]

Jeff Janes wrote
 That's not really true.  There are no per-row WAL records.  There is still
 a per-transaction WAL record, the commit record. If you only care about
 the
 timing of the WAL and not the volume, changing to unlogged will not make a
 difference.  (These commit-only records are automatically dealt with
 asynchronously, even if synchronous-commit is on.)
 
 Cheers,
 
 Jeff

Hi,

just to understand this answer:

Is it *NOT* possible to create an unlogged table where inserts or updates
will *NOT* modify the WAL files?

Regards, hall




--
View this message in context: 
http://postgresql.nabble.com/Will-modifications-to-unlogged-tables-also-be-flused-to-disk-tp5792158p5829600.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL on AIX platform

[Frank Pinto]

Payal,

I think you completely ignored john r pierce...any reason you're using
9.2.4? Whoevers going to help you will want to be testing on what you're
eventually going to compile

Frank


On Fri, Aug 8, 2014 at 4:54 PM, Payal Shah payals...@fico.com wrote:

  Hello John,



 Thank you for your response. If you or someone can provide steps on how to
 bundle PostgreSQL on AIX (using IBM XL C compiler), that would be great so
 I can try it out to see if works for AIX 7 platform. I’m new to AIX
 platform and appreciate any help that you can provide.



 Thank you,

 Payal Shah

 *From:* pgsql-general-ow...@postgresql.org [mailto:
 pgsql-general-ow...@postgresql.org] *On Behalf Of *John R Pierce
 *Sent:* Friday, August 08, 2014 3:46 PM
 *To:* pgsql-general@postgresql.org
 *Subject:* Re: [GENERAL] PostgreSQL on AIX platform



 On 8/8/2014 3:27 PM, Payal Shah wrote:

 Can you please confirm if PostgreSQL 9.2.4 is supported on AIX 7 platform?
 In the following URL, it mentions support for AIX 6 (but not AIX 7) -
 http://www.postgresql.org/docs/9.2/static/installation-platform-notes.html


 it should work fine, although I've not personally tested.


 why 9.2.4 and not the latest 9.2.9 with 5 rounds of bug and security fixes
 ?   bug fixes since 9.2.4 include...
 http://www.postgresql.org/docs/current/static/release-9-2-5.html
 http://www.postgresql.org/docs/current/static/release-9-2-6.html
 http://www.postgresql.org/docs/current/static/release-9-2-7.html
 http://www.postgresql.org/docs/current/static/release-9-2-8.html
 http://www.postgresql.org/docs/current/static/release-9-2-9.html


 note that 9.2.6 and 9.2.9 both fixed some potential data and/or index
 corruption bugs.




  --

 john r pierce  37N 122W

 somewhere on the middle of the left coast


 This email and any files transmitted with it are confidential, proprietary
 and intended solely for the individual or entity to whom they are
 addressed. If you have received this email in error please delete it
 immediately.


 This email and any files transmitted with it are confidential, proprietary
 and intended solely for the individual or entity to whom they are
 addressed. If you have received this email in error please delete it
 immediately.

Re: [GENERAL] Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

[Frank Pinto]

Looks like you're doing it right, you actually have to specify the user
though:

psql -U postgres

and make sure you restarted the server so your changes take effect.

Frank


On Wed, Aug 6, 2014 at 4:43 PM, Jorge Arevalo jorgearev...@libregis.org
wrote:

 Hello,

 I want to connect to my local installation of PostgreSQL 9.1 using my
 machine user (who is vagrant). So, after reading PostgreSQL documentation,
 I thought I just needed to:

 1. Add username map in pg_ident.conf:

 # MAPNAME   SYSTEM-USERNAME PG-USERNAME

 vp   vagrantpostgres


 2. Using the map in pg_hba.conf

 # TYPE  DATABASEUSERADDRESS METHOD

 local   all  all
 peer map=vp

 But I'm getting the error

 sql: FATAL:  Peer authentication failed for user vagrant

 If I try to connect to my server using psql.

 I guess I'm misunderstanding the PostgreSQL manual. But, how could I get
 what I need? (locally connect with the user vagrant like if it was the
 postgres user)

 Many thanks in advance (and sorry for the cross-posting. I asked this in
 serverfault too, but I think this is the right place)

 --
 Jorge Arevalo

 http://about.me/jorgeas80

Re: [GENERAL] Petition: Treat #!... shebangs as comments

[Frank Pinto]

I personally like Francisco Olarte's approach. Hashbang's don't support
arguments well (
http://stackoverflow.com/questions/4303128/how-to-use-multiple-arguments-with-a-shebang-i-e)
and being able to put JUST psql as the command to execute the script
doesn't scale across environments. Previously I've just used a quick
wrapper:

https://gist.github.com/frankpinto/3427cf769a72ef25ffac

It can be modified to accept arguments for the script name, run a sql
script by the same name, have a default environment, etc.

Frank


On Fri, Jul 18, 2014 at 10:43 AM, Martin Gudmundsson 
martingudmunds...@gmail.com wrote:

 +1

 Skickat från min iPhone

  18 jul 2014 kl. 17:58 skrev Adrian Klaver adrian.kla...@aklaver.com:
 
  On 07/18/2014 08:52 AM, Karsten Hilbert wrote:
  On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote:
 
 
  I think the OP is talking about executable scripts so both of
 
 $ psql -f the-file.sql
 
  and
 
 $ ./the-file.sql
 
 (where the-file.sql starts with #!/usr/bin/env psql)
 
  would work given that the-file.sql has got execute permission.
 
  Yea, it finally dawned on me what was being asked, so ignore my previous
 post.
 
 
  Karsten
 
 
  --
  Adrian Klaver
  adrian.kla...@aklaver.com
 
 
  --
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Define OID

[frank ernest]

Hello, I cant seem to find the allowed values for OIDs.

result = PQprepare(parrentcon, insertstmt, insert, 2, /*FIXME*/);

Both arguments are to be of type char * in C and of type varchar(255) in sql.

I looked in the docs but I cant seem to find where OIDs are enumerated, please point me in the right direction.



Thanks, David

Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

[Frank Pinto]

That actually sounds terrifying. I'd throw up a caching layer ASAP to try
to decrease the speed those transactions are happening.

Frank


On Mon, Jul 7, 2014 at 2:25 PM, Nicolas Zin 
nicolas@savoirfairelinux.com wrote:

 Maybe you can priorize your worker with a ionice?

 - Mail original -
 De: Mike Christensen m...@kitchenpc.com
 À: Prabhjot Sheena prabhjot.she...@rivalwatch.com
 Cc: pgsql-ad...@postgresql.org, Forums postgresql 
 pgsql-general@postgresql.org
 Envoyé: Lundi 7 Juillet 2014 16:15:18
 Objet: Re: [ADMIN] [GENERAL] WARNING: database must be vacuumed within
 8439472 transactions


 Sounds like you just have to wait until it finishes..



 On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena 
 prabhjot.she...@rivalwatch.com  wrote:









 Hello
 We are using postgresql 8.3 database for last 5 yrs for this production
 database and its running fine. This is our critical database which runs
 24*7. This weekend we started getting these messages

 HINT: To avoid a database shutdown, execute a full-database VACUUM.
 WARNING: database must be vacuumed within 8439472 transactions

 i am currently running this command

 vacuumdb --analyze db

 while this command is running i m still getting these messages

 WARNING: database must be vacuumed within 2645303 transactions.

 The value of number of transactions is going down every minute

 Can anyone tell me what is the best way to sort up this issue.

 Thanks
 Avi











 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to create multiple databases running in different dirs

[frank ernest]

 how would an instance of your program know what to connect to, or which
 previous instance its predecessor was ?

 normally, you have ONE database for a given set of applications, and all
 the applications share the same database tables and such.


Thats the problem, is there some way to tell pgsql Go to dir X, open your data base Y and prepare for connections at adress Z and port P? And could pgsql accept multiple connections on the same address and port? I was thinking of using my pID but that would change and if I used a user created string then if I started only on a single instace of pgsql and pointed it to its databases the user might get the strings duplicated and that would be no good. I also thought of naming each database with a name generated by using the uuid library but Im not sure which would be best.



If I choose to have multiple servers running on the same machine how would my program start them?



 Is each runtime instance of your application to have a new, empty
 database? No data from any other run of the application?



No, the dynamically generated content is to be dropped (drop table dynamic_content;) but the rest is to be preserved. The idea is to create a database of a file system but the files contain no data, I only want their metadata and I will add a few additional metadta values to each file.



Thanks, David

[GENERAL] how to create multiple databases running in different dirs

[frank ernest]

Hi, Im new to postgresql and sql in general. I desired to write a program in C that used an sql data base for IPC and because multiple copies of my program might run on the same machine I wanted a way to ensure that only one copy of each multithreaded program got one database but Im uncertain how to go about this. As the program might be run several different times and each time it should only get the data base from its predisesor I cant use the pid as a unique data base name. I thought that I might start multiple pgsql instances but somehow that seems wrong. Any ideas?



Thanks, David

Re: [GENERAL] Is it possible to pip pg_dump output into new db ?

[Frank]

Ray, Alan,

thanks for your replies.

We have tested the dump/restore procedure with a few smaller databases and
it worked fine. We had a few smaller hiccups with the large database as it
required a few modules and special tablespaces before it would start
actually copying data. But not a real problem, a test-upgrade is currently
running.

We will use this upgrade-cycle to systematically test and evaluate all
upgrade options.

We have an if-all-else-fails-full plain dump that we just restored
testwise. In this case to see performance but we regularly restore it to
make sure it actually works. The file is compressed with rar. Packed size
is about 100 GB, unpacked about 1 TB. Uncompressing alone on a decent
machine (256GB Ram, 12 Core) took about 1 day. Importing via psql took
about 12 hours (fsync off, wal_segments adjusted, etc.).

Currently we are running the direct pg_dump / pg_restore upgrade from a
slave that we just took off wal-replication. As it is progressing i am
expecting something in the 12-15 hour range.

Finally we will try the pg_upgrade-option on a test-slave. I expect that to
be quite fast as it more or less just needs to copy the data once and
correct/adjust system tables, if i am not mistaken. So that should take
about as long as it takes to copy 1 TB of data plus the table-adjustments.

Fortunately we can use the weekend to freeze the database so the
reduced-downtime that might be achieved by the slony-approach is not a true
requirement and we can avoid the complexities of that approach.


Thanks,

Frank














On Tue, Mar 25, 2014 at 4:46 PM, Raymond O'Donnell r...@iol.ie wrote:

 On 25/03/2014 13:56, Frank Foerster wrote:
 
  Hi,
 
  we are currently in the process of upgrading a production/live 1 TB
  database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process.
 
  Fortunately we have a capable spare-server so we can restore into a
  clean, freshly setup machine.
 
  I just wondered wether the intermediate step of writing the dump-file
  and re-reading it to have it written to the database is really
  necessary. Is there any way to pipe the dump-file directly into the
  new database-process or would such functionality make sense ?

 Surely:

   pg_dump [...etc...] | psql [...etc...]

 Though I'm sure it will still take a long time for a database of that size.

 Another option to explore would be to use Slony, which can replicate
 databases between different Postgres versions - one of its design
 use-cases is to perform upgrades like this with a minimum of down-time.
 You can replicate the database over to the new server, and then
 switching need take only seconds once the new one is ready.

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie

[GENERAL] Is it possible to pip pg_dump output into new db ?

[Frank Foerster]

Hi,

we are currently in the process of upgrading a production/live 1 TB
database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process.

Fortunately we have a capable spare-server so we can restore into a clean,
freshly setup machine.

I just wondered wether the intermediate step of writing the dump-file and
re-reading it to have it written to the database is really necessary. Is
there any way to pipe the dump-file directly into the new
database-process or would such functionality make sense ?

I can only speak for us, but each time we do a dump/restore we need to
extract/copy/move very large files and piping directly into something like
psql/pg_restore on another machine etc. would greatly reduce
upgrade-time/pain.

Thanks and best regards,

Frank

[GENERAL] Please ignore my previous mail: piping pg_dump output / solved

[Frank]

Hi,

please ignore my previous question about piping pg_dump output to
pg_restore. This is already working.

Thanks,

Frank

Re: [GENERAL] Hard upgrade (everything)

[Frank Broniewski]

Hi everybody,

I just wanted to let you know my notes I took during the upgrade process
from Postgresql 9.1 to 9.3 and Postgis 1.5 to 2.1. Maybe someone finds
them useful. I'm running the cluster on FreeBSD 9.2 so all commands
apply to FreeBSD of course, but it should be fairly easy to translate
the process to any other OS. So here they come:

-- plain sql files are suffixe with .sql, binary files with .dump
-- -- (double dashes) are comments,  is user shell, # is root shell, $
is pqsql user shell

-- just in case
 pg_dumpall -U postgres -h localhost -f cluster.sql

-- pg_dumpall for roles on the cluster
 pg_dumpall --roles-only -f roles.sql

-- dump each database
 pg_dump -h localhost -U postgres -Fc -b -v -f db.dump db

-- become root
 su -

-- update the ports tree
# portsnap fetch update

-- stop the cluster
# service postgresql stop

-- delete the target dir contents
-- remember to backup also the config files!
# rm -r /data/pgdata/*

-- uninstall the old versions
-- watch out for dependent ports!
# pkg delete -f postgis-1.5.3_3 postgresql-contrib-9.1.9
postgresql91-client-9.1.11 postgresql91-server-9.1.10

-- postgresql91-client-9.1.11 is required by: py27-psycopg2-2.5.1
mapnik-2.2.0_2 gdal-1.10.1_1 mapserver-6.2.1_3 php5-pgsql-5.4.23
php5-pdo_pgsql-5.4.23 py27-gdal-1.10.1 php5-extensions-1.7
osm2pgsql-0.84.0, deleting anyway

-- install PostgreSQL Server, Client, Contrib (client comes as
dependency anyway)
# portmaster databases/postgresql93-server databases/postgresql93-contrib

-- install PostGIS
# portmaster databases/postgis21

-- rebuild ports that depend on (the old) postgresql-client
# portmaster py27-psycopg2 mapnik-2 mapserver php5-pgsql php5-pdo_pgsql
php5-extensions osm2pgsql

-- init database cluster  postgis
# su pgsql
$ initdb --pgdata=MOUNTPOINT-DATA/pgdata --locale=de_DE.UTF-8
$ exit
# service postgresl start
# su pgsql
$ createuser -srdP postgres
$ exit
# psql -U postgres -d postgres
postgres=# create extension postgis; // and others if required
postgres=# \q
# exit

-- install legacy.sql if required (from 1.5 to 2.1 it was)
-- legacy.sql is in
/usr/local/share/postgresql/contrib/postgis-2.1/legacy.sql
# psql -U postgres -f
/usr/local/share/postgresql/contrib/postgis-2.1/legacy.sql

-- restore the roles to the cluster
 psql -d postgres -U postgres -f roles.sql

-- restore the databases through postgis_restore.pl
-- FreeBSD: postgis_restore.pl is in /usr/local/share/postgis/utils/
 perl /usr/local/share/postgis/utils/postgis_restore.pl epc.dump | psql
-U postgres epc

-- uninstall legacy.sql
 psql -U postgres -f
/usr/local/share/postgresql/contrib/postgis-2.1/uninstall_legacy.sql


Hth,

Frank

Am 2014-02-12 18:58, schrieb Bruce Momjian:
 On Thu, Feb  6, 2014 at 10:07:18AM +0100, Rémi Cura wrote:
 On my private computer I upgraded first the postgres to 9.3, then upgraded
 postgis.
 Sadly according to http://trac.osgeo.org/postgis/wiki/
 UsersWikiPostgreSQLPostGIS ,
 postgis 1.5 is not compatible with postgres 9.3.
 However POstgis 2.1 is compatible with you current postgres option.
 So as suggested you can upgrade postgis (see hard/soft upgrade), the upgrade
 postgres.
 By the way postgis is very easy to compil with ubuntu (use package system to
 get dependecies, then simply sudo ./autogen.sh, sudo ./configure, make , sudo
 make install)
 
 Yes, that is the order I have head.  You can upgrade postgis from 1.5 to
 2.1 on the old server, then use pg_upgrade to upgrade Postgres.
 


-- 
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Hard upgrade (everything)

[Frank Broniewski]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi list,

I've asked this question already on the PostGIS list, but I think it
might get great answers here too. I'm running two database cluster
with PostGIS 1.5 and PostgreSQL 9.1 on FreeBSD 9.2-RELEASE-p3 and
apparently my PostGIS and PostgreSQL are a little bit outdated.
Now the plan is to upgrade both PostGIS to 2.1 and PostgreSQL to 9.3,
 which are the latest versions in the ports.

Now, I am a little bit lost with all the precautions and measures one
has to undertake to do the hard upgrade. From what I read on the
PostgreSQL site, I can choose whether I want to use pg_upgrade
(without the need of a intermediate dump) or pg_dumpall to put the
data aside. I presume I can't use pg_upgrade since this wouldn't take
PostGIS into account, right?

That leaves me with pg_dumpall for the PostgreSQL upgrade. Now,
reading the PostGIS instructions to upgrade I come to the conclusion
that a binary dump is required to put the data aside while doing the
upgrade. Thing is pg_dump only dumps one spatial database, and I have
several in my clusters, so I'd need to dump all of them, right?

And here's where my confusion starts, since there are different tools
used for PostgreSQL and PostGIS for the individual upgrade. What would
be the correct procedure to dump a complete cluster in a PostGIS and
PostgreSQL compliant way? My ideas so far:

Step one: Use pg_dumpall to dump the roles and cluster metadata
Step two: Iterate and use pg_dump in binary mode to dump every
database in the cluster
Step three: rename/empty the target drive/folder
Step four: do the upgrade of PostgreSQL and PostGIS
Step five: restore the roles and metadata
Step six: use the command utils/postgis_restore.pl to restore each
individual database

Does that look sound?

On a side note, I tried upgrading each part individually, but the port
dependencies won't let me do that because upgrading PostgreSQL to 9.3
will also pull PostGIS 2.1 and upgrading PostGIS 2.1 will also pull
PostgreSQL 9.3, so I only get the two of them ...


Any tips on the procedure are greatly welcome :-)

Frank

- -- 
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJS80FVAAoJEHopqW0d1cQVjzMIAJQ6povfOSYg0NIw5DXF0KlZ
0MQQjwoOwRIPrCkllwDEpmioI2OtkBH03LYuQQYi8SArovtBRlTwyvZsUgFuuxy6
qAQhHcGxLyJPvxBIXVRhqnmn89a1otGxXTI+ZedNbJsj74vW0h29KRBAyklphe/C
iAGw8+2zr0yiBLJdHWZvnMdS0PkL4jc7UY1XfmCg3AvNQU1EgiUYdWOEn26fqj0g
bXrpHERgv8c+Hk8r8/G4WRD6rC0aMirB0lynxn+FHhSc9mzXUbDbER99M06vXrtF
uIIeOTfr/Pu5eyjHDc3stg2LAtoNTvnvvJ0S+5Shi6ndLRy3P7AHZ6y915AMkRA=
=4KIY
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cannot delete some records [9.3] - semi-resolved

[Frank Miles]

I have a workaround for the mysterious inability to delete records
from one particular table not notably different from many others.
This does not explain the problem, but at least enables me to move
on...  Whether all of the following steps are necessary I can't say.

Instead of loading the 9.3 DB with a dump from 8.4, I did a two
step dump-reload through an intermediate system with 9.1, first
clearing the 9.3 DB of any residues from the previous load.  This
included eliminating all references to users, which in turn 
required using a REASSIGN for template1 to postgres.


Retained the superuser status for deleting the rows.  This did not
work previously (I can recreate the problem by altering the role to
mere CREATEROLE CREATEDB) and with the user in question being the
owner of the table.

This makes me a bit nervous, as I don't understand why, but at least
it seems to work.

-Frank


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cannot delete some records [9.3]

[Frank Miles]

I'm in the process of moving from a server running postgresql-8.4 
(Debian-oldstable)
to a newer machine running postgresql-9.3.  The dumpall-restore process seemed 
to
go perfectly.  In running my self-test script, I discovered that one of the 
tables
couldn't be cleared of some unit-test entries.  The table schema is {\d 
credmisc}:

Table public.credmisc
  Column  |   Type   |Modifiers 
--+--+--

 cm_id| integer  | not null default 
nextval('credmisc_cm_id_seq'::regclass)
 crtype   | character(1) | not null
 ref_id   | integer  | not null
 raw_amt  | double precision | not null
 resolved | boolean  | not null default false
 dtwhen   | date | not null default ('now'::text)::date
 explan   | text | not null
Indexes:
credmisc_pkey PRIMARY KEY, btree (cm_id)
Check constraints:
credmisc_crtype_check CHECK (crtype = 'b'::bpchar OR crtype = 'p'::bpchar)
credmisc_raw_amt_check CHECK (raw_amt = 0.02::double precision)
Referenced by:
TABLE credtrans CONSTRAINT credtrans_cm_id_fkey FOREIGN KEY (cm_id) 
REFERENCES credmisc(cm_id)
Triggers:
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE PROCEDURE 
trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE PROCEDURE 
trigonupdtcredmisc()

And this is all owned by: {\dp credmisc}

Access privileges
 Schema |   Name   | Type  | Access privileges | Column access privileges 
+--+---+---+--

 public | credmisc | table | fpm=ardxt/fpm+|
|  |   | bioeng=r/fpm  |

Yet when I try to delete some records:
delete from credmisc  where cm_id  -100 and ref_id  0;
what I get back is:
ERROR:  permission denied for relation credmisc
CONTEXT:  SQL statement SELECT 1 FROM ONLY public.credmisc x WHERE 
cm_id OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

Neither dropping the trig_credmisc_updt trigger nor performing the 'delete'
operation as user 'postgres' changes anything.  There's nothing different in
the logs.  It works perfectly fine in 8.4.  And most of the other dozens of
tables don't have this problem.  Selecting the data looks fine.

Anyone have a clue as to what I'm missing?  TIA!

-Frank

{p.s. yes, cm_id won't normally be negative... some negative values
were inserted as part of the unit testing, which avoids confusion
with existing positive value.  That shouldn't be a problem, right?}



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cannot delete some records [9.3]

[Frank Miles]

On Thu, 5 Dec 2013, Andy Colson wrote:


On 12/5/2013 4:05 PM, Frank Miles wrote:


[snip]

 Table public.credmisc
   Column  |   Type   |Modifiers

--+--+--

  cm_id| integer  | not null default
nextval('credmisc_cm_id_seq'::regclass)
  crtype   | character(1) | not null
  ref_id   | integer  | not null
  raw_amt  | double precision | not null
  resolved | boolean  | not null default false
  dtwhen   | date | not null default ('now'::text)::date
  explan   | text | not null
Indexes:
 credmisc_pkey PRIMARY KEY, btree (cm_id)
Check constraints:
 credmisc_crtype_check CHECK (crtype = 'b'::bpchar OR crtype =
'p'::bpchar)
 credmisc_raw_amt_check CHECK (raw_amt = 0.02::double precision)
Referenced by:
 TABLE credtrans CONSTRAINT credtrans_cm_id_fkey FOREIGN KEY
(cm_id) REFERENCES credmisc(cm_id)
Triggers:
 trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
 trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()

And this is all owned by: {\dp credmisc}

 Access privileges
  Schema |   Name   | Type  | Access privileges | Column access
privileges
+--+---+---+--
  public | credmisc | table | fpm=ardxt/fpm+|
 |  |   | bioeng=r/fpm  |

Yet when I try to delete some records:
 delete from credmisc  where cm_id  -100 and ref_id  0;
what I get back is:
 ERROR:  permission denied for relation credmisc
 CONTEXT:  SQL statement SELECT 1 FROM ONLY public.credmisc x
WHERE cm_id OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

Neither dropping the trig_credmisc_updt trigger nor performing the
'delete'
operation as user 'postgres' changes anything.  There's nothing
different in
the logs.  It works perfectly fine in 8.4.  And most of the other dozens of
tables don't have this problem.  Selecting the data looks fine.

Anyone have a clue as to what I'm missing?  TIA!

 -Frank

{p.s. yes, cm_id won't normally be negative... some negative values
were inserted as part of the unit testing, which avoids confusion
with existing positive value.  That shouldn't be a problem, right?}





When you drop trig_credmisc_updt, you still get the error like:

 ERROR:  permission denied for relation credmisc
 CONTEXT:  SQL statement SELECT 1 FROM ONLY public.credmisc x
WHERE cm_id OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x


I assume that select statement is comming from function trigonupdtcredmisc(), 
right?


-Andy


I can't see how - there's nothing in the trigger like that, and I still
get the same message even when the trigger is dropped.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cannot delete some records [9.3]

[Frank Miles]

On Thu, 5 Dec 2013, Andy Colson wrote:


On 12/5/2013 4:05 PM, Frank Miles wrote:


  The table schema is {\d
credmisc}:
And this is all owned by: {\dp credmisc}

You have a table credmisc, in schema credmisc, owned by credmisc?
It could be a path problem.  Maybe trigger should be:


Sorry for the perhaps overly compact way that I was describing
how I recovered the schema (by executing \d credmisc) and ownership
(\dp credmisc).  It's owned by 'fpm'.

trig_credmisc_updt BEFORE UPDATE ON credmisc.credmisc FOR EACH ROW EXECUTE 
PROCEDURE credmisc.trigonupdtcredmisc()



 trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
 trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()





 Access privileges
  Schema |   Name   | Type  | Access privileges | Column access
privileges
+--+---+---+--
  public | credmisc | table | fpm=ardxt/fpm+|
 |  |   | bioeng=r/fpm  |




Could we see the permissions on the functions too?

-Andy


As a trigger, can it be 'owned'?  And since the problem occurs even
when the trigger is dropped, it seems ultimately not involved.

Thanks for trying, though!
   -Frank


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How can I run a PostgreSQL database outside /var/run/postgresql?

[Frank Church]

How can I run a PostgreSQL database independently of the normal packaged
based configuration?

I just want to start up postgres or pg_ctl process using a different port,
pointing to a different data directory and get it running, with permissions
etc working okay.


I don't want it to depend on the existing package page structure in
/etc/postgresql and /var/run/postgresql. The only thing required is
database compatiblity with the existing package based installations, which
I may be able to install if they are absent

The main points are -

**

** 1. Which data and configurations should be copied, ie the files and
directories should be copied from /etc/postgresql/xxx and
/var/run/postgresql

2. What executables are required, whether they can be extracted from
existing packages ie rpm/deb directly or after they have been installations
to their locations.

3. My preference will be to use existing package based installations or
install them if possible in case of dependencies which are not present in
my files, with my executables as there second option.

Thanks

voipfc

Re: [GENERAL] .pgpass being ignored

[Frank Broniewski]

 However, when I call createdb, it fails:

  $ createdb -h 192.168.1.4 -U postgres --no-password JobSearch
  createdb: could not connect to database postgres:
 fe_sendauth: no
 password supplied



Hi,

isn't your --no-password switch preventing this? I never use this switch 
and my .pgpass is used by shell scripts and other programs ...



Frank


--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best practice on inherited tables

[Frank Lanitz]

Am 17.05.2013 21:21, schrieb Alfonso Afonso:
 Hi Frank
 
 Although you are thinking in OOP, the SQL is itself one definition
 model that you should not ignore and, IMHO, try to follow the
 normalization statements.
 
 You can build a robust and normalized schema (table primarylocation ,
 table secondlocation that have a idprimarylocation, etc.) and later
 you could build your OOP software translating this to the proper
 classes (in Java you could use a DAO-POJO class or hibernate-jpa, for
 example).
 
 With this solution you can obtain all the benefits of DBRMS besides a
 OOP robust software :)

I was really thinking about this way as it's tradition relational model
and of course kind of a rock stable solution. But I have the fear that
it will end up in a real mess of joins at database layer in the end so I
thought to make usage of such a feature if available ;)

Cheers,
Frank



signature.asc
Description: OpenPGP digital signature

[GENERAL] Best practice on inherited tables

[Frank Lanitz]

Hi folkes,

I'm looking for a nice way to build this scenario:
I've got a lot of locations with some special types. For example I've
got workplaces, places like real laboratories and virtual places like
maybe parcel service. For each of the different types I need to store
some common attributes as well as some special ones. Having OOP in mind
I came to the point of inherit tables. so I've create something like
that (just a minimal example):

CREATE TABLE locations(
id SERIAL PRIMARY KEY,
name varchar(50)
);
CREATE TABLE workplaces(
workers integer
) INHERITS (locations);

But now I got stuck with the primary key thing. As described in the
documentation it is not supported. And now I'm looking for the best way
on having at table workplaces also the unique constraint from locations
etc. so e.g. I can do something like that:

INSERT INTO workplaces (name, workers) VALUES ('My Place', 5);

having the incrementation and the uniqueness. I was thinking off
creating a number of triggers doing this for me but wondering whether
there might be a better way.

Cheers,
Frank

BTW: Using Postgres 9.2 and up


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Update

[Frank Lanitz]

Am 11.04.2013 10:29, schrieb jpui:
 Hi,
 I'm running a server using postgres 8.3 and i was adviced to update it...
 what i have to do  in order to update it and don't stop the service? 

8.3 is out of support so you will need to at a very minimum 8.4. This
cannot be done without restarting. Please check for HowTo for upgrading
postgres.

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] HwTo Foreign tables anybody?

[Frank Lanitz]

Hi folks,

I'm looking for a HowTo of Foreign Tables feature. We are thinking of
connecting two postgres databases via this way and I wanted to try
before I do say yes or no ;) However, I didn't find any good HowTo on
via §search_engine.

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Use, Set Catalog and JDBC questions

[Frank Cavaliero]

Hi,

I have an application that requires to connect to each database available 
in PostgreSQL.  I have the following questions:


1.  Is there a USE DATABASE command or something of the sort (similar to 
MySQL) that allows you to quickly connect to a database without having to 
reconnect using the username,password and database again ?   In Java, we 
are using set catalog to do this in MySQL.

2.  Based on #1 above, would the latest JDBC driver support the ability to 
create this type of connection?


Thanks,
Frank
Database Administrator

Re: [GENERAL] Use, Set Catalog and JDBC questions

[Frank Cavaliero]

Hi Adrian,

Thanks for the response.   The situation is more like the following:

Using the JDBC driver, I connect to database TEST1 and immediately, 
without having to pass username credentials again, I want to use database 
TEST2.   In MySQL, you can simply run:  use TEST2.  Wondering if 
PostgreSQL has something similar.

Thanks,
Frank









From:
Adrian Klaver adrian.kla...@gmail.com
To:
Frank Cavaliero/Boston/IBM@IBMUS
Cc:
pgsql-general@postgresql.org, pgsql-ad...@postgresql.org
Date:
02/25/2013 01:48 PM
Subject:
Re: [GENERAL] Use, Set Catalog and JDBC questions



On 02/25/2013 10:22 AM, Frank Cavaliero wrote:
 Hi,

 I have an application that requires to connect to each database
 available in PostgreSQL.  I have the following questions:


 1.  Is there a USE DATABASE command or something of the sort (similar to
 MySQL) that allows you to quickly connect to a database without having
 to reconnect using the username,password and database again ?   In Java,
 we are using set catalog to do this in MySQL.

 2.  Based on #1 above, would the latest JDBC driver support the ability
 to create this type of connection?

Not sure if this will do what want?:
http://jdbc.postgresql.org/documentation/91/datasource.html#ds-intro


 Thanks,
 Frank

 /Database Administrator/



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

Re: [GENERAL] Use, Set Catalog and JDBC questions

[Frank Cavaliero]

Hi John,

Thanks for the response.   I will look into that as an option.

Thanks,
Frank



 



From:
John R Pierce pie...@hogranch.com
To:
pgsql-general@postgresql.org
Date:
02/25/2013 01:33 PM
Subject:
Re: [GENERAL] Use, Set Catalog and JDBC questions
Sent by:
pgsql-general-ow...@postgresql.org



On 2/25/2013 10:22 AM, Frank Cavaliero wrote:
1.  Is there a USE DATABASE command or something of the sort (similar to 
MySQL) that allows you to quickly connect to a database without having to 
reconnect using the username,password and database again ?   In Java, we 
are using set catalog to do this in MySQL. 

no, there isn't.you have to connect to the database. mysql's 
databases are in many ways equivalent to postgres'  'schemas' within a 
single database (you can move between schemas with SET SEARCH_PATH=...).

-- 
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] Use, Set Catalog and JDBC questions

[Frank Cavaliero]

Hi Adrian,

Thanks a lot!I will certainly look into the multiple datasources as an 
option.


-Frank



 



From:
Adrian Klaver adrian.kla...@gmail.com
To:
Frank Cavaliero/Boston/IBM@IBMUS
Cc:
pgsql-ad...@postgresql.org, pgsql-general@postgresql.org
Date:
02/25/2013 02:16 PM
Subject:
Re: [GENERAL] Use, Set Catalog and JDBC questions



On 02/25/2013 10:57 AM, Frank Cavaliero wrote:
 Hi Adrian,

 Thanks for the response.   The situation is more like the following:

 Using the JDBC driver, I connect to database TEST1 and immediately,
 without having to pass username credentials again, I want to use
 database TEST2.   In MySQL, you can simply run:  use TEST2.  Wondering
 if PostgreSQL has something similar.

You can do it in the psql client like this, though that will not help 
with JDBC:

aklaver@ford:~$ psql -d test -U postgres
psql (9.0.5)
Type help for help.

test=# \c production
You are now connected to database production.
production=#


I do not use the JDBC driver much, but from what I read in the link I 
sent you, you can set up a non-pooling DataSource to which you can add 
predefined datasources and then switch as needed.


 Thanks,
 Frank



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

[GENERAL] How to store version number of database layout

[Frank Lanitz]

Hi folks,

It's more like a question of best practice:
How do you managing different version of database layout for e.g.
software releases?
We are planing to do an application based on postgres and want to store
some version number of database layout to ensure correct upgrade-scripts
can be applied in case of an upgrade. Is there any build in for?

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_shadow and pgcrypto

[Frank Cavaliero]

Hi all,

I'm currently performing a hash check for password verification.I'm 
generating an md5  hash or checking for plain text in pg_shadow.  However, 
outside of these two out-of-the-box options, what if someone is using 
pg-crypto or any other PAM ?  How can I differentiate between say md5 and 
pgcrypto ?  I see the md5 is prefixed in hash in pg_shadow.  I was 
wondering what other prefixes may exist, say for pgcrypto.  If you have an 
examples of what a pgcrypto or any other PAM hash would look like (or what 
they would at least begin with), that would be great.

Thanks,
Frank




Frank Cavaliero
Database Administrator
IBM Infosphere Guardium

Re: [GENERAL] PG under OpenVZ?

[Frank Lanitz]

Am 2012-11-13 14:53, schrieb François Beausoleil:

Hi!

I've found an old thread on OpenVZ:

(2008): 
http://archives.postgresql.org/pgsql-performance/2008-03/msg00076.php


And a more recent question that scared me a bit:

(2011): 
http://serverfault.com/questions/281783/running-mongodb-with-openvz


On the PostgreSQL general mailing list, I've only found 54 results
when searching for OpenVZ. I'm wondering if OpenVZ is simply
unpopular, or not used at all for PG. What experiences do you have
with OpenVZ? Any performance problems?

We're buying bare metal to run our clusters on, and the supplier is
late delivering the machines. They suggested lending us a machine and
run PostgreSQL under OpenVZ. When the real hardware is ready, we'd
migrate the VZ over to the new physical servers. Thoughts on this?

I have no experience with OpenVZ itself, so if you have general
comments about it's stability and/or performance, even unrelated to
PostgreSQL, I'd appreciate.


Running a small PG-Server for private purposes on openVZ. Cannot 
complain so far.


Cheers,
Frank



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Failed Login Attempts parameter

[Frank Cavaliero]

Hi,

I've been searching the web and reviewing documentation, but I cannot find 
any reference to whether or not a parameter, for example, 
failed_login_attempts, exists in PostgreSQL that determines the number of 
attempts a user can make before being locked.  In addition, if such a 
parameter or similar setup exists, is there also some database object that 
provides a current count of the failed login attempts?

Thanks,
Frank





Frank Cavaliero
Database Administrator
IBM Infosphere Guardium
IBM Software Group, Information Management
978-899-3635 - Direct
For Technical Services Support Please Call 978-899-9195.
 
This communication is intended only for the use of the individual or 
entity named as the addressee. It may contain information which is 
privileged and/or confidential under applicable law.  If you are not the 
intended recipient or such recipient's employee or agent, you are hereby 
notified that any dissemination, copy or disclosure of this communication 
is strictly prohibited.  If you have received this communication in error, 
please immediately notify us at 978-899-9195 or notify the sender by reply 
e-mail and expunge this communication without making any copies.  Thank 
you for your cooperation.
 

Re: [GENERAL] Memory issue on FreeBSD

[Frank Broniewski]

FYI 
http://freebsd.1045724.n5.nabble.com/Postgresql-related-memory-question-td5759467.html



Am 2012-11-07 10:28, schrieb Achilleas Mantzios:

On Τετ 07 Νοε 2012 09:42:47 Frank Broniewski wrote:

Hey, this is really cool. I directly tried the script and there's a line
from the output that caught my eye:

   mem_gap_vm:  +   8812892160 (   8404MB) [ 26%] Memory gap: UNKNOWN

is this the shared buffers? I guess so, but I want to confirm my guess ...


Hmm, that would be ideal, (from an understanding perspective) but at least in 
my system (FreeBSD-8.3), no.

psql -q -t -c show shared_buffers | grep -v -e '^$' | awk '{print $1}'
3840MB

SYSTEM MEMORY INFORMATION:
mem_gap_vm:  +996843520 (950MB) [  5%] Memory gap: UNKNOWN

$mem_gap_vm  = $mem_all - ($mem_wire + $mem_active + $mem_inactive + $mem_cache 
+ $mem_free);

mem_all is some rounded and more rationalized version less than hw.physmem : $mem_all = 
$sysctl-{vm.stats.vm.v_page_count} * $sysctl-{hw.pagesize};

Anyway, this is not so postgresql related at the moment. The correct thing to 
do (since you run production servers on FreeBSD) is to post to the relevant
FreeBSD list and/or forum. freebsd-questi...@freebsd.org and 
freebsd-sta...@freebsd.org would be a good start.
Also the forums : http://forums.freebsd.org/forumdisplay.php?f=3
Only after gathering substantial info from there, would it make sense to come 
back here and maybe ask more questions.
And since we are observing different percentages of gaps (mine is 5%, yours is 
26%), i think maybe you should look into it on the FreeBSD camp.

Please drop the link to the relevant thread there, if you decide to do so.

I would like to follow this.

Thanx!



Frank

Am 2012-11-07 09:26, schrieb Achilleas Mantzios:

Vick,
fantastic script, thanx! FreeBSD sysctl system is awesome!

On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote:



On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:

and this is after a few hours of running:

Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free




For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with 
Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7.  The data 
+ indexes are about 240GB on disk.  This server only runs postgres aside from 
the basic system processes.


Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free
Swap: 4096M Total, 272K Used, 4096M Free


I agree with the conclusion that the shared memory segments are confusing the output of 
top.  There are no memory leaks, and FreeBSD doesn't lose any memory.


There are some scripts floating around that read values from sysctl vm.stats.vm 
and format them nicely to tell you how much memory is used up and free.  Try 
the one referenced here: 
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/





-
Achilleas Mantzios
IT DEPT






-
Achilleas Mantzios
IT DEPT




--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memory issue on FreeBSD

[Frank Broniewski]

Hey, this is really cool. I directly tried the script and there's a line 
from the output that caught my eye:


 mem_gap_vm:  +   8812892160 (   8404MB) [ 26%] Memory gap: UNKNOWN

is this the shared buffers? I guess so, but I want to confirm my guess ...

Frank

Am 2012-11-07 09:26, schrieb Achilleas Mantzios:

Vick,
fantastic script, thanx! FreeBSD sysctl system is awesome!

On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote:



On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:

and this is after a few hours of running:

Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free




For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with 
Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7.  The data 
+ indexes are about 240GB on disk.  This server only runs postgres aside from 
the basic system processes.


Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free
Swap: 4096M Total, 272K Used, 4096M Free


I agree with the conclusion that the shared memory segments are confusing the output of 
top.  There are no memory leaks, and FreeBSD doesn't lose any memory.


There are some scripts floating around that read values from sysctl vm.stats.vm 
and format them nicely to tell you how much memory is used up and free.  Try 
the one referenced here: 
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/





-
Achilleas Mantzios
IT DEPT




--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Memory issue on FreeBSD

[Frank Broniewski]

Hi,

I am running a PostgreSQL server on FreeBSD. The system has 32GB memory. 
Usually I use top to examine the memory usage of the system. After a 
while, a part, approximately 5GB, vanish from top, so that the memory 
rounds up to 27GB.  After restarting PostgreSQL, I have all 32GB again 
available, but then it's already slightly decreasing. It's a standalone 
database server. It has an OpenStreetMap world database running with 
353GB data (with indices).


Some system information:
# uname -r
9.0-RELEASE-p3
# pg_ctl --version
pg_ctl (PostgreSQL) 9.1.6

# cat /boot/loader.conf
...
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.semumr=200
vm.pmap.shpgperproc=400
vm.pmap.pv_entry_max=50331648
...

# cat /pgdata/data/postgresql.conf
...
default_statistics_target = 50 # pgtune wizard 2012-04-04
maintenance_work_mem = 4GB # pgtune wizard 2012-04-04
constraint_exclusion = on # pgtune wizard 2012-04-04
checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04
effective_cache_size = 24GB # pgtune wizard 2012-04-04
work_mem = 768MB # pgtune wizard 2012-04-04
wal_buffers = 16MB # pgtune wizard 2012-04-04
checkpoint_segments = 60 # 20
shared_buffers = 8GB # pgtune wizard 2012-04-04
max_connections = 100
synchronous_commit = off


So any help finding out why my system looses some RAM is greatly 
appreciated :-) If more information is needed I will gladly provide it.


Frank



--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memory issue on FreeBSD

[Frank Broniewski]

Hi,

thank you for your feedback. I had a look at those commands and their 
output, especially in conjunction with the SEGSZ value from icps  -am


Here's an example output:
# ipcs -am
Shared Memory:
T   ID  KEY MODEOWNERGROUPCREATOR 
CGROUP NATTCHSEGSZ CPID LPID ATIME 
DTIMECTIME
m   262144  5432001 --rw--- pgsqlpgsqlpgsqlpgsql 
 12   88139939844551245512 13:49:28 
14:31:29 13:49:28


but frankly this tells me nothing. I can tell that the value SEGSZ is 
right from the start 8813993984 and it doesn't change anymore. The only 
value that changes is the NATTCH value, I observed a range from 8 to 36 
there. I agree that the SEGSZ value matches the 8GB shared buffer, but 
how can I make the connection of my 5GB missing in top? I wonder if this 
might be the maintenance_work_mem, which is set to 4GB?


Many thanks,

Frank

Am 2012-11-05 12:14, schrieb Achilleas Mantzios:


ipcs in FreeBSD is a little ... tricky.

ipcs -M
ipcs -m
ipcs -am

could be your friends

On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote:

Hi,

I am running a PostgreSQL server on FreeBSD. The system has 32GB memory.
Usually I use top to examine the memory usage of the system. After a
while, a part, approximately 5GB, vanish from top, so that the memory
rounds up to 27GB.  After restarting PostgreSQL, I have all 32GB again
available, but then it's already slightly decreasing. It's a standalone
database server. It has an OpenStreetMap world database running with
353GB data (with indices).

Some system information:
# uname -r
9.0-RELEASE-p3
# pg_ctl --version
pg_ctl (PostgreSQL) 9.1.6

# cat /boot/loader.conf
...
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.semumr=200
vm.pmap.shpgperproc=400
vm.pmap.pv_entry_max=50331648
...

# cat /pgdata/data/postgresql.conf
...
default_statistics_target = 50 # pgtune wizard 2012-04-04
maintenance_work_mem = 4GB # pgtune wizard 2012-04-04
constraint_exclusion = on # pgtune wizard 2012-04-04
checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04
effective_cache_size = 24GB # pgtune wizard 2012-04-04
work_mem = 768MB # pgtune wizard 2012-04-04
wal_buffers = 16MB # pgtune wizard 2012-04-04
checkpoint_segments = 60 # 20
shared_buffers = 8GB # pgtune wizard 2012-04-04
max_connections = 100
synchronous_commit = off


So any help finding out why my system looses some RAM is greatly
appreciated :-) If more information is needed I will gladly provide it.

Frank





-
Achilleas Mantzios
IT DEPT





--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memory issue on FreeBSD

[Frank Broniewski]

Hi,

I just add the different memory values together (minus the buffers). 
Usually this sums up (+/-) to the installed memory size, at least on my 
other machines. I found a thread similar to my problem here [1], but no 
solution. I don't mind top showing false values, but if there's a larger 
problem behind this, then I really want to solve it.


Top is really just an indicator for this issue, it's also visible in my 
munin stats [2]


Below is a output _without_ postgresql running:
Mem: 59M Active, 17G Inact, 3953M Wired, 1325M Cache, 3283M Buf, 8663M Free
Swap: 4096M Total, 828K Used, 4095M Free


and this is after a few hours of running:

Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free

some memory related sysctl values:
hw.realmem: 34879832064
hw.physmem: 34322804736
hw.usermem: 30161108992

# sysctl vm.vmtotal
vm.vmtotal:
System wide totals computed every five seconds: (values in kilobytes)
===
Processes:  (RUNQ: 1 Disk Wait: 0 Page Wait: 0 Sleep: 70)
Virtual Memory: (Total: 1084659688K Active: 10400940K)
Real Memory:(Total: 1616176K Active: 1349052K)
Shared Virtual Memory:  (Total: 60840K Active: 14132K)
Shared Real Memory: (Total: 11644K Active: 8388K)
Free Memory Pages:  7263972K


[1] 
http://lists.freebsd.org/pipermail/freebsd-stable/2011-January/061247.html
[2] 
http://www.gis-hosting.lu/monitor/munin/metrico/bilbo.metrico/memory.html



Am 2012-11-05 15:21, schrieb Achilleas Mantzios:

How do you measure that smth is missing from top? What values do you add?
I am currently running 8.3 but we shouldn't be so far apart top-wise.
What is the reading under SIZE and RES in top for all postgresql processes?
Take note that shared mem should be recorded for each and every postmaster 
running.

On Δευ 05 Νοε 2012 14:36:44 Frank Broniewski wrote:

Hi,

thank you for your feedback. I had a look at those commands and their
output, especially in conjunction with the SEGSZ value from icps  -am

Here's an example output:
# ipcs -am
Shared Memory:
T   ID  KEY MODEOWNERGROUPCREATOR
CGROUP NATTCHSEGSZ CPID LPID ATIME
DTIMECTIME
m   262144  5432001 --rw--- pgsqlpgsqlpgsqlpgsql
   12   88139939844551245512 13:49:28
14:31:29 13:49:28

but frankly this tells me nothing. I can tell that the value SEGSZ is
right from the start 8813993984 and it doesn't change anymore. The only
value that changes is the NATTCH value, I observed a range from 8 to 36
there. I agree that the SEGSZ value matches the 8GB shared buffer, but
how can I make the connection of my 5GB missing in top? I wonder if this
might be the maintenance_work_mem, which is set to 4GB?

Many thanks,

Frank

Am 2012-11-05 12:14, schrieb Achilleas Mantzios:


ipcs in FreeBSD is a little ... tricky.

ipcs -M
ipcs -m
ipcs -am

could be your friends

On Δευ 05 Νοε 2012 11:22:46 Frank Broniewski wrote:

Hi,

I am running a PostgreSQL server on FreeBSD. The system has 32GB memory.
Usually I use top to examine the memory usage of the system. After a
while, a part, approximately 5GB, vanish from top, so that the memory
rounds up to 27GB.  After restarting PostgreSQL, I have all 32GB again
available, but then it's already slightly decreasing. It's a standalone
database server. It has an OpenStreetMap world database running with
353GB data (with indices).

Some system information:
# uname -r
9.0-RELEASE-p3
# pg_ctl --version
pg_ctl (PostgreSQL) 9.1.6

# cat /boot/loader.conf
...
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.semumr=200
vm.pmap.shpgperproc=400
vm.pmap.pv_entry_max=50331648
...

# cat /pgdata/data/postgresql.conf
...
default_statistics_target = 50 # pgtune wizard 2012-04-04
maintenance_work_mem = 4GB # pgtune wizard 2012-04-04
constraint_exclusion = on # pgtune wizard 2012-04-04
checkpoint_completion_target = 0.9 # pgtune wizard 2012-04-04
effective_cache_size = 24GB # pgtune wizard 2012-04-04
work_mem = 768MB # pgtune wizard 2012-04-04
wal_buffers = 16MB # pgtune wizard 2012-04-04
checkpoint_segments = 60 # 20
shared_buffers = 8GB # pgtune wizard 2012-04-04
max_connections = 100
synchronous_commit = off


So any help finding out why my system looses some RAM is greatly
appreciated :-) If more information is needed I will gladly provide it.

Frank





-
Achilleas Mantzios
IT DEPT







-
Achilleas Mantzios
IT DEPT





--
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Too much clients connected to the PostgreSQL Database

[Frank Lanitz]

Am 30.10.2012 02:06, schrieb rodr...@paripassu.com.br:
 BTW, 200 seems alwfully high unless a *really* high end machine. Â You
 may have fewer timeouts if you avoid swamping the server with a
 thundering herd of requests.

I was maintaining a setup which had  1000 connections on a not very
high-end server (12GB of Ram). It was just most of the connections were
idling most the time. Tomcat with a high number of consistent
connections for some reasons and end user stand alone clients which are
establishing a database connection on startup and keeping them until
shutdown.

Cheers,
Frank



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Help estimating database and WAL size

[Frank Lanitz]

Am 2012-10-15 23:13, schrieb John R Pierce:

On 10/15/12 2:03 PM, Daniel Serodio (lists) wrote:

John R Pierce wrote:

On 10/08/12 1:39 PM, Daniel Serodio (lists) wrote:

3) Estimate the size of the transaction log
** We've got no idea how to estimate this, need advice **


postgres doesn't have a 'transaction log', it has the WAL 
(Write-Ahead Logs).  These are typically 16MB each.  on databases 
with a really heavy write load, I might bump the checkpoint_segments 
as high as 60, which seems to result in about 120 of them being 
created, 2GB total.  these files get reused, unless you are archiving 
them to implement a continuous realtime backup system (which enables 
PITR, Point in Time Recovery)
Thanks, I was using the term transaction log as a synonym for WAL. 
We're planning on enabling PITR; how can we calculate the WAL size and 
the WAL archive size in this case?



its based on how much data you're writing to the database.   Wheen
you write tuples (rows) to the database, they are stored in 8K
pages/blocks which are written to the current WAL file as they are
committed, when that WAL file fills up, or the checkpoint_timeout is
reached (the default is 30 seconds, I believe) , the WAL file is
written to the archive.

To be able to utilize PITR, you need a complete base backup of the
file system, and /all/ the archived WAL files since that base backup
was taken.


In huge number of cases you will also write these files to some kind of 
network storage via e.g. CIFS or NFS so you have access to them via your 
warm-standby-machines. I want to say: this is taken some storage but can 
be reviewed kind of independent from database itself.


Cheers,
Frank





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL force create table / ignore constraints?

[Frank Lanitz]

On Wed, 3 Oct 2012 08:12:25 -0700 (PDT)
hartrc rha...@mt.gov wrote:

 Version Postgresql 9.1.6
 OS: SLES 11 64 bit
 
 Background:
 Our developers create database schema in development environment using
 PGAdmin (often using the GUI to generate the DDL). 
 We always deploy to production using a script, a single .sql file
 which we execute via psql command line. This allows us to generate an
 output with any errors and have good view of deployment history over
 time. 
 
 Issue
 The issue we have is that developers generate the .sql script mainly
 by copying and pasting from PGAdmin's SQL pane. The issue we have is
 then the order of the object creation is important otherwise creation
 of tables and fail when there is a foreign key constraint on another
 table that does not exist (but is created later in the script). This
 is not a big deal in a schema with 3 or 4 tables but when there are 20
 + it is time consuming task to reorder all the create statements.
 
 Can anyone recommend a way of dealing with this? My only other
 thought has been pg_dump although i would prefer if the developers
 could generate the scripts themselves.

What about using pg_dump --schema-only when creating the files? (Or
are you talking about icremental changes?)

Cheers, 
Frank

-- 
Frank Lanitz fr...@frank.uvena.de


pgpJnRr67CUNQ.pgp
Description: PGP signature

Re: [GENERAL] Odd query result

[Frank Lanitz]

On Mon, 27 Aug 2012 10:55:43 +0200
Maximilian Tyrtania li...@contactking.de wrote:

 Hello from Berlin,
 
 I can't quite make sense of this (running PG 9.0.3):
 
 psql (9.0.3)
 Type help for help.
 
 FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from
 firmen
 where
 (firmen.bezeichnung='Microsoft Deutschland GmbH'); _rowid  |
 f_firmen_iskunde |bezeichnung 
 --+--+
   1214700 | f| Microsoft Deutschland GmbH
  15779700 | t| Microsoft Deutschland GmbH
166300 | t| Microsoft Deutschland GmbH
 (3 rows)
 
 FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from 
 FAKDB-# firmen where 
 FAKDB-# (firmen.bezeichnung='Microsoft Deutschland GmbH') and 
 FAKDB-# (f_firmen_isKunde(firmen)=true) and firmen._rowid=15779700 ;
   _rowid  | f_firmen_iskunde |bezeichnung 
 --+--+
  15779700 | t| Microsoft Deutschland GmbH
 (1 row)
 
 Fine. But this record won't be found if I omit the last condition.
 
 FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from
 firmen
 where
 (firmen.bezeichnung='Microsoft Deutschland GmbH') and
 (f_firmen_isKunde(firmen)=true); _rowid | f_firmen_iskunde |
 bezeichnung 
 +--+
  166300 | t| Microsoft Deutschland GmbH
 (1 row)
 
 
 What might be up there?

How is f_firmen_isKunde() defined? 

Cheers, 
Frank
-- 
Frank Lanitz fr...@frank.uvena.de


pgp1GbDwLQBZT.pgp
Description: PGP signature

Re: [GENERAL] Result from Having count

[Frank Lanitz]

Am 23.08.2012 09:52, schrieb Condor:
 Hello ppl,
 
 I try to make query and see how many ids have more then one row.
 
 few records is:
 
 ids | val | some
  a  | 1   | x
  a  | 1   | v
  b  | 1   | x
  b  | 2   | c
 
 
 I focus on ids and val with:
 
 SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
 COUNT(ids)  1;
 
 and result is:
 
 ids | val
  a  |   1
 
 Well in this condition pgsql shold not return me positive result because
 on documentation I read having count work on group clause,
 and when I group these two records based on ids = 'a' they become to one
 row and my condition is if the result after grouping is greeter then 1.
 
 I use postgresql 9.1.4 x64
 
 
 Any one can tell me what I miss ?


Not sure I understand you correct, but maybe count() is working for you.
Maybe you would need some primary key for good values.

cheers,
Frank



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Result from Having count

[Frank Lanitz]

Am 23.08.2012 10:45, schrieb Condor:
 On , Frank Lanitz wrote:
 Am 23.08.2012 09:52, schrieb Condor:
 Hello ppl,

 I try to make query and see how many ids have more then one row.

 few records is:

 ids | val | some
  a  | 1   | x
  a  | 1   | v
  b  | 1   | x
  b  | 2   | c


 I focus on ids and val with:

 SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
 COUNT(ids)  1;

 and result is:

 ids | val
  a  |   1

 Well in this condition pgsql shold not return me positive result because
 on documentation I read having count work on group clause,
 and when I group these two records based on ids = 'a' they become to one
 row and my condition is if the result after grouping is greeter then 1.

 I use postgresql 9.1.4 x64


 Any one can tell me what I miss ?


 Not sure I understand you correct, but maybe count() is working for you.
 Maybe you would need some primary key for good values.

 cheers,
 Frank
 
 
 Sorry for my email,
 after some thinking I understand my error and change query to:
 
 SELECT COUNT(DISTINCT val), ids FROM table WHERE ids = 'a' GROUP BY ids
 HAVING COUNT(DISTINCT val)  1;
 
 and it's work.

At least I was wrong in understanding your request. ;) But glad, you
found a solution.

Cheers,
Frank



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best practice non privilege postgres-user

[Frank Lanitz]

On Fri, 17 Aug 2012 08:53:05 -0400
Moshe Jacobson mo...@neadwerx.com wrote:

 I do not know of anything that can't be done from within psql.
 We use non-privileged user roles in postgres for day-to-day
 operations. When I need to modify the schema, I become postgres (you
 can do \c - postgres) and do what I need to do, then revert back to
 my regular user.

It's not only about the things that can be done from within psql. At
least originally. Some of our currently workflows are basing on real
shell access. 

Cheers, 
Frank
-- 
Frank Lanitz fr...@frank.uvena.de


pgpBsGObDQVNO.pgp
Description: PGP signature

[GENERAL] Best practice non privilege postgres-user

[Frank Lanitz]

Hi folks,

I'm looking for some kind of best practice for a non-privilege postgres
user. As not all operations can be done within psql you might need
access to postgres- on command line from time to time. Currently this is
done via root-privvileges and »su - postgres« directly on database
server - which is might not the best idea. Therefor our goal is to limit
access to a little number of people on the first hand and don't
necessary give them root-privileges on the databse server. We
experimented a bit with sudo but had issues with some of the
environmental variables. So my question is: do you have any best
practice how to manage this? Is there any golden rule for this?

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to don't update sequence on rollback of a transaction

[Frank Lanitz]

Hi,

Thanks very much for the detailed answer. I totally missed the issue
with concurrent transactions.

Am 03.08.2012 02:00, schrieb Craig Ringer:

 It's interesting that you read the documentation and still got bitten by
 this. I'll have to think about writing a patch to add some
 cross-references and make the tx exception of sequences more obvious.

This would be great. I just read the transaction documentation and had
only a short look onto sequence documentation part. I totally missed the
important window at the end.

 The general idea with sequences is that they produce numbers that can be
 meaningfully compared for equality and for greater/less-than, but *not*
 for distance from each other. Because they're exempt from transactional
 rollback you shouldn't use them when you need a gap-less sequence of
 numbers.
 
 It's usually a sign of an application design problem when you need a
 gapless sequence. Try to work out a way to do what you need when there
 can be gaps. Sometimes it's genuinely necessary to have gapless
 sequences though - for example, when generating cheque or invoice numbers.

Yes. I understood now ;)

 Gap-less sequences are often implemented using a counter table and
 UPDATE ... RETURNING, eg:
 
 CREATE TABLE invoice_number (
 last_invoice_number integer primary key
 );
 
 -- PostgreSQL specific hack you can use to make
 -- really sure only one row ever exists
 CREATE UNIQUE INDEX there_can_be_only_one
 ON invoice_number( (1) );
 
 -- Start the sequence so the first returned value is 1
 INSERT INTO invoice_number(last_invoice_number) VALUES (0);
 
 -- To get a number; PostgreSQL specific but cleaner.
 UPDATE invoice_number
 SET last_invoice_number = last_invoice_number + 1
 RETURNING last_invoice_number;
 
 
 Note that the `UPDATE ... RETURNING` will serialize all transactions.
 Transaction n+1 can't complete the UPDATE ... RETURNING statement until
 transaction `n' commits or rolls back. If you are using gap-less
 sequences you should try to keep your transactions short and do as
 little else in them as possible

Thanks for the detailed idea how to do it correct. I'm not thinking
about invoice number handling but something I also don't want to have gaps.

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to don't update sequence on rollback of a transaction

[Frank Lanitz]

Am 02.08.2012 17:15, schrieb Andrew Hastie:
 Hi Frank,
 
 I believe this is by design. See the bottom of the documentation on
 sequences where it states ;-
 
 *Important:* To avoid blocking concurrent transactions that obtain
 numbers from the same sequence, a |nextval| operation is never rolled
 back; that is, once a value has been fetched it is considered used, even
 if the transaction that did the |nextval| later aborts. This means that
 aborted transactions might leave unused holes in the sequence of
 assigned values. |setval| operations are never rolled back, either.
 
 http://www.postgresql.org/docs/9.1/static/functions-sequence.html
 
 If you really want to reset the sequence, I think you would have to call
 SELECT SETVAL(.) at the point you request the roll-back.

Yepp. Somehow I missed that part of documentation. I don't think setval
will do the trick I want to perform, but Craig's idea looks very well.

Thanks for feedback!

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to don't update sequence on rollback of a transaction

[Frank Lanitz]

Hi folks,

I did a test with transactions and wondered about an behavior I didn't
expected. At http://pastebin.geany.org/bYQNo/raw/ I posted a complete
backlog for.

To make it short: I created a table with a serial and started a
transactions. After this I was inserting values into the table but did a
rollback. However. The sequence of the serial filed has been incremented
by 1 on each insert (which is fine), but wasn't reset after rollback of
transaction.

Documentation stats:
If, partway through the transaction, we decide we do not wantto commit
(perhaps we just noticed that Alice's balance went   negative), we can
issue the command ROLLBACK instead of COMMIT, and all our updates so far
will be canceled.

My understanding of all was that it includes sequences. Obviously, I'm
wrong... but how to do it right?

Cheers,
Frank

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is there a way to recover deleted records if database has not been vacuumed?

[Frank Church]

I am using Django to develop an app and I think I must have done a syncdb
(which deletes all records) without realizing it.

I have not vacuumed that database and I have also made a copy of the data
directory.

Is there some way to recover the deleted records?

-- 
Frank Church

===
http://devblog.brahmancreations.com

[GENERAL] pg_database_size differs from df -s

[Frank Lanitz]

Hi folks,

I've got an issue I'm not sure I might have a misunderstanding. When
calling

select sum(pg_database_size(datid)) as total_size from pg_stat_database

the result is much bigger than running a df -s over the postgres folder
- Its about factor 5 to 10 depending on database.

My understanding was, pg_database_size is the database size on disc. Am
I misunderstanding the docu here?

Cheers,
Frank

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_database_size differs from df -s

[Frank Lanitz]

Am 06.06.2012 17:49, schrieb Tom Lane:
 Frank Lanitz fr...@frank.uvena.de writes:
 I've got an issue I'm not sure I might have a misunderstanding. When
 calling
 
 select sum(pg_database_size(datid)) as total_size from pg_stat_database
 
 the result is much bigger than running a df -s over the postgres folder
 - Its about factor 5 to 10 depending on database.
 
 Did you mean du -s?

Yepp, sure. Was to confused about the two numbers. ;)

 My understanding was, pg_database_size is the database size on disc. Am
 I misunderstanding the docu here?
 
 For me, pg_database_size gives numbers that match up fairly well with
 what du says.  I would not expect an exact match, since du probably
 knows about filesystem overhead (such as metadata) whereas
 pg_database_size does not.  Something's fishy if it's off by any large
 factor, though.  Perhaps you have some tables in a nondefault
 tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Cheers,
Frank

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_database_size differs from df -s

[Frank Lanitz]

On Wed, 6 Jun 2012 20:31:36 +0200
Alban Hertroys haram...@gmail.com wrote:

 On 6 Jun 2012, at 16:33, Frank Lanitz wrote:
 
  the result is much bigger than running a df -s over the postgres
  folder
  - Its about factor 5 to 10 depending on database.
 
 
 Is your du reporting sizes in Bytes or blocks or ...?

Should be byte as its a linux. 

cheers, 
Frank
-- 
Frank Lanitz fr...@frank.uvena.de


pgpoJNH3d0L7h.pgp
Description: PGP signature

Re: [GENERAL] significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1

[Frank Ch. Eigler]

Tom Lane t...@sss.pgh.pa.us writes:

 [...]  Can you correlate the performance hit with any specific part
 of autovacuum?  In particular, I'm wondering if it matters whether
 vacuum is cleaning tables or indexes [...]

In case it helps, this systemtap run will report on the top few
time-sampled call graphs of post* processes:

# debuginfo-install -y postgresql
# stap -V[...Fedora16...]
Systemtap translator/driver (version 1.7/0.153 non-git sources)
# cat post-prof.stp
probe timer.profile { 
  if (substr(execname(),0,4) != post) next
  bt[sprint_ustack(ubacktrace())]  1
}
probe end {
  foreach ([s] in bt- limit 50) { println(\n,@count(bt[s]),:,s) }
}
global bt[2]

# stap post-prof.stp -v -d /usr/bin/postgres -d /usr/bin/postmaster --ldd 
--all-modules  --suppress-handler-errors
[wait awhile during workload]
^C

1390:index_getnext+0x1f9 [postgres]
IndexNext+0x56 [postgres]
ExecScan+0x14e [postgres]
ExecProcNode+0x228 [postgres]
ExecLimit+0xb8 [postgres]
ExecProcNode+0xd8 [postgres]
standard_ExecutorRun+0x14a [postgres]
PortalRunSelect+0x287 [postgres]
PortalRun+0x248 [postgres]
PostgresMain+0x754 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

935:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
get_relation_info+0x5e2 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]


721:__send+0x22 [libc-2.14.90.so]
internal_flush+0x3b [postgres]
pq_flush+0x22 [postgres]
ReadyForQuery+0x29 [postgres]
PostgresMain+0x49e [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

431:recv+0x22 [libc-2.14.90.so]
secure_read+0x1c6 [postgres]
pq_recvbuf+0x5f [postgres]
pq_getbyte+0x15 [postgres]
PostgresMain+0x4bf [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]

380:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
estimate_rel_size+0x7c [postgres]
get_relation_info+0x121 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
[...]

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Variables inside plpythonu

[Frank Lanitz]

Hi folks, 

I did check the documentation but seem to didn't found the best way to
use plpythonu with a variable inside a query. 
Let's say I want to run this query 
SELECT id FROM some_table WHERE date=date_from_function_call
How a CREATE FUNCTION stateent have to look like? 
I already figured out that the python code should look similar to 

plan = plpy.prepare(SELECT id FROM some_table WHERE date= 
return = plpy.execure(plan)

But somehow a last piece is missing. 

Can anybody help?

Cheers, 
Frank
-- 
Frank Lanitz fr...@frank.uvena.de


pgprKWy6SHnPI.pgp
Description: PGP signature

Re: [GENERAL] Lock out PostgreSQL users for maintenance

[Frank Lanitz]

On Sat, 12 May 2012 06:29:54 +0200
Alexander Farber alexander.far...@gmail.com wrote:

 Or should I edit pg_hba.conf and restart the process?

At least this is what we are doing. We are having a normal pg_hba.conf
and a pg_hba.conf for maintenance and switching on demand. Maybe not
the best solution, but its working ;) 

Cheers, 
Frank 
-- 
Frank Lanitz fr...@frank.uvena.de


pgpKHQ4kaTSLV.pgp
Description: PGP signature

Re: [GENERAL] Variables inside plpythonu

[Frank Lanitz]

On Sat, 12 May 2012 07:11:08 -0700
Adrian Klaver adrian.kla...@gmail.com wrote:

 Something like this?:
 
 create or replace function date_test(some_date date) returns void as
 $Body$
 date_plan = plpy.prepare(select id_fld from date_test where date_fld
 = $1, [date]) date_rs = plpy.execute(date_plan,[some_date])
 plpy.notice(date_rs[0][id_fld])
 $Body$
 language plpythonu;

Yes. Gave me the missing piece. Thanks a lot!

Cheers, 
Frank
-- 
Frank Lanitz fr...@frank.uvena.de


pgpKZuZqB0PRy.pgp
Description: PGP signature

[GENERAL] Is it possible to call other functions inside plpythonu?

[Frank Lanitz]

Hi folks,

Just looking for a nice server side solution to implement some
fundamental logic for an application. plpythonu looks in this tmers very
well as I'm liking the syntax of Python. However, an very old blog post
at [1] made me unsure whether really to use it. Is it still (or has it
ever been) an issue that plpythonu is having a lot of overhead and not
able to make use of other functions? Didn't found anything on docu for
9.1 about that.

Cheers,
Frank

[1] http://spyced.blogspot.de/2005/04/plpython-intro.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] configuring RAID10 for data in Amazon EC2 cloud?

[Frank Lanitz]

On Tue, 27 Mar 2012 11:25:53 -0400
Welty, Richard rwe...@ltionline.com wrote:

 does anyone have any tips on this? Linux Software Raid doesn't seem
 to be doing a very good job here, but i may well have missed
 something.
 
 i did a fairly naive setup using linux software raid on an amazon
 linux instance, 10 volumes (8G each), (WAL on a separate EBS volume)
 with the following setup:
 

You might want to check with Amazon here. 

Cheers, 
Frank 

-- 
Frank Lanitz fr...@frank.uvena.de


pgpmHnneAclhe.pgp
Description: PGP signature

[GENERAL] Values inside rolvaliduntil of pg_authid

[Frank Lanitz]

Hi folks,

I'm currently doing some checks for users. During this I've found inside
pg_authid.rolvaliduntil a couple of values I wasn't able to figure out
via documentation, whether they are valid Maybe you can help me out
helping whether these are valid dates and what is postgres interpreting
them:

- infinity (I assume it's treaded as NULL inside this column -
unlimited password)
- 1970-01-01 00:00:00+01 (UNIX timestamp 0. I have no f. bloody idea here)


(I'm running 8.4 here)

cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 64 Bit XIDs - Transaction IDs

[Frank Lanitz]

Am 23.03.2012 06:45, schrieb Gerhard Wiesinger:
 With a database admin of a commercial database system I've discussed
 that they have to provide and they also achieve 2^31 transactions per
 SECOND!

Just corious: What is causing this many transactions?

Cheers,
Frank

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 64 Bit XIDs - Transaction IDs

[Frank Lanitz]

Am 23.03.2012 14:23, schrieb Adrian Klaver:
  I would say either they got the numbers wrong or someone is pulling
 your leg. That rate is not going to happen.

Maybe twitter or facebook all in all...

Cheers,
Frank

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] POSTGRESQL Newbie

[Frank Lanitz]

Am 21.03.2012 12:35, schrieb Marti Raudsepp:
 On Wed, Mar 21, 2012 at 11:10, Vincent Veyron vv.li...@wanadoo.fr wrote:
 However, I once read that the real reason is that mysql was available
 when ISPs came of existence, circa 1995. It lacked important features of
 an RDBMS (you can google the details), but it was enough to satisfy the
 needs of php scripts for instance.

 First to market, in short.
 
 Let's not forget that PostgreSQL sucked, too, back then.
 
 PostgreSQL's maintenance was absolutely horriffic. And if you got it
 wrong, it would bog down all your hardware resources. MySQL lacked
 many features, but it just worked without maintenance.
 
 E.g. VACUUM/ANALYZE needed to be ran manually and it used to take an
 *exclusive* lock on tables, for longish periods, preventing any
 queries! Failure to vacuum would cause the files to bloat without
 limit and slow down your queries gradually. In the worst case, you hit
 XID wraparound and the database would shut down entirely.
 
 Even still in 8.3 (which was newest until 2009) with autovacuum, if
 you got max_fsm_pages tuned wrong, vacuum would basically stop
 functioning and your tables would bloat.

Yepp.. Remmembering back when I started to get in contact with LAMP
mysql just worked. Wasn't fast and didn't had a lot of fancy features
but it just worked in default config for day2day stuff.

Cheers,
Frank


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general