Peter Mogensen | 19 Dec 09:13 2014
Picon

BDR consensus algorithm

Hi,

Can anyone provide a pointer a description of the bidirectional 
replication global sequences consensus algorithm?

The documentation says:
"BDR uses a voting/election system to assign blocks from global 
sequences. The details are in the source code."
https://wiki.postgresql.org/wiki/BDR_Global_Sequences#How_global_sequences_are_managed_internally

But I can't find anything in the source remotely resembling an algorithm 
like PAXOS or RAFT.

kind regards,
Peter Mogensen

--

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

M Tarkeshwar Rao | 19 Dec 06:00 2014
Picon

can you please share sample Postgres config file to enable max logging with syslog support?

Hello friends,

 

can you please share sample Postgres config file to enable max logging with syslog support?

 

Actually we are facing some issues. We need to enable maximum logging.

 

Regards

Tarkeshwar

 

Patrick Krecker | 18 Dec 23:13 2014

Re: Combining two queries

On Thu, Dec 18, 2014 at 1:57 PM, Robert DiFalco
<robert.difalco <at> gmail.com> wrote:
> Thanks! So how would I combine them so that I would get a single row with
> the mutual friend count and isFriends for a given pair of users? I can't
> figure out how to modify what you've posted so that it gives the results
> like the compound query I quoted in my original post.
>
> On Thu, Dec 18, 2014 at 1:31 PM, Patrick Krecker <patrick <at> judicata.com>
> wrote:
>>
>> On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco
>> <robert.difalco <at> gmail.com> wrote:
>> > I have a table called friends with a user_id and a friend_id (both of
>> > these
>> > relate to an id in a users table).
>> >
>> > For each friend relationship there are two rows. There are currently
>> > ONLY
>> > reciprocal relationships. So if user ids 1 and 2 are friends there will
>> > be
>> > two rows (1,2) and (2,1).
>> >
>> > For 2 arbitrary ids, I need a query to get two pieced of data:
>> >    * Are the two users friends?
>> >    * How many friends do the two users have in common.
>> >
>> > Is there a way to do this with one query? Currently I've only been able
>> > to
>> > figure out how to do it with two.
>> >
>> > SELECT
>> >   EXISTS(
>> >     SELECT 1
>> >     FROM friends
>> >     WHERE user_id = 166324 AND friend_id = 166325) AS friends,
>> >   (SELECT COUNT(1)
>> >     FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_id
>> >     WHERE f1.user_id = 166324 AND f2.user_id = 166325) AS mutual;
>> >
>> > I'm wondering if there is a better way to do this using only one query.
>> > I've
>> > tried a couple of GROUP BY approaches but they haven't worked.
>>
>> Assuming the friendships are not repeated (that is, if 1 is friends
>> with 3, then the tuple (1, 3) appears only once), you can find just
>> the mutual friends by using this one:
>>
>> (This would be for users 1 and 2):
>>
>> SELECT friend_id FROM friends WHERE user_id IN (1, 2) GROUP BY
>> friend_id HAVING count(friend_id) > 1;
>>
>> You can additionally test if 1 and 2 are friends by doing:
>>
>> SELECT friend_id FROM friends WHERE user_id IN (1, 2) OR (user_id = 1
>> AND friend_id = 2) GROUP BY friend_id HAVING (count(friend_id) > 1 OR
>> friend_id = 1);
>>
>> If 1 appears in the list, then 1 and 2 are friends. Any other rows are
>> the mutual friends.

(adding back psql-general)

Well it would not be a single SELECT statement anymore :)

There are probably other ways of doing this, I just came up with this
one off the top of my head:

SELECT is_user, c FROM (
    SELECT friend_id = USER1 AS is_user, count(friend_id) OVER
(partition BY friend_id = USER1) AS c FROM (
        SELECT friend_id FROM friends WHERE user_id IN (USER1, USER2)
OR (user_id = USER1 AND friend_id = USER2) GROUP BY friend_id HAVING
(count(friend_id) > USER1 OR friend_id = USER1)
        ) AS t1)
    AS t2 GROUP BY is_user, c;

It should return 2 rows, one with is_user = t and one with is_user =
f. is_user = t will be present if the two users are friends, and will
always have c = 1. is_user = f will be present if there are mutual
friends, and c will be the number of mutual friends.

--

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

Robert DiFalco | 18 Dec 21:10 2014
Picon

Combining two queries

I have a table called friends with a user_id and a friend_id (both of these relate to an id in a users table).

For each friend relationship there are two rows. There are currently ONLY reciprocal relationships. So if user ids 1 and 2 are friends there will be two rows (1,2) and (2,1). 

For 2 arbitrary ids, I need a query to get two pieced of data:
   * Are the two users friends?
   * How many friends do the two users have in common.

Is there a way to do this with one query? Currently I've only been able to figure out how to do it with two.

SELECT 
  EXISTS(
    SELECT 1 
    FROM friends 
    WHERE user_id = 166324 AND friend_id = 166325) AS friends,
  (SELECT COUNT(1)
    FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_id
    WHERE f1.user_id = 166324 AND f2.user_id = 166325) AS mutual;

I'm wondering if there is a better way to do this using only one query. I've tried a couple of GROUP BY approaches but they haven't worked.
Cristian Iturrieta | 18 Dec 18:49 2014
Picon

Postgresql 9.2, Memoy cache usage.

Dear pgadmin-general, pgadmin-performance,

 

   I have the following query: What could be due to a single query sql one day run fast and another slow. The database has massive loads overnight and the next day serves datawarehouse. This causes the first query is slower by having to go find all the information to disk. With a new process is achieved load data from disk cache operating system that is able to improve response times. I have doubts as to refresh cache and as the LRU (least recently used) works. One way to optimize the query was generating "Common Table Expressions", here is my other question, where are CTE's stored?, Work_mem?, Buffer cache?, Temporary tablespace ?, etc ..

 

 

Deputy some parameter values postgresql :

 

max_connections = 40

checkpoint_completion_target = 0.9

random_page_cost=20

seq_page_cost = 20

default_statistics_target = 100 # pgtune wizard 2014-11-20

maintenance_work_mem = 1GB # pgtune wizard 2014-11-20

constraint_exclusion = on # pgtune wizard 2014-11-20

effective_cache_size = 30GB # pgtune wizard 2014-11-20

work_mem = 800MB # pgtune wizard 2014-11-20

wal_buffers = 32MB # pgtune wizard 2014-11-20

checkpoint_segments = 64 # pgtune wizard 2014-11-20

shared_buffers = 5GB # pgtune wizard 2014-11-20

 

 

Summary charging procedure cache operating system:

 

select distinct relfilenode from pg_buffercache > /tmp/cacheprecarga.lst

 

cat /tmp/cacheprecarga.lst | while read line; do find /var/lib/pgsql/9.2/data/base/ -name $line -print -exec cat '{}' > /dev/null \; ; done

 

 

The query in question is:

 

 

With CTE’s (Common table expressions) :

 

 

WITH PPTEMP AS (SELECT * FROM PROPRO WHERE PV_KEY = 3268), IDTEMP AS (SELECT * FROM INVDET  WHERE ID_INT9 IN (1)), CPTEMP AS (select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1)  SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK)) /SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0 END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CATPROD CP   WHERE LP.PV_KEY = PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA  ORDER BY  LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY;

 

 

prkey  | codretailer |  codprovider  |            productdescription            | last28dayssalesmc |          margin          |   stockmc    |      daysales

--------+-------------+---------------+------------------------------------------+-------------------+--------------------------+--------------+---------------------

 272309 | 578525      | 1000000232615 | DET POLVO OMO MATIC MULTIACCION 3.8KG    |      208949598.00 | -94.78519887547606157500 | 642572117.00 | 86.1069820100826420

.

.

 

Time: 142200.432 ms

 

Result :  2.30 minutes

 

Other query :

 

WITH PRTEMP AS (SELECT * FROM PRODUCTO  WHERE PR_ESTADO = 1 ), PPTEMP AS (SELECT * FROM PROPRO  WHERE PV_KEY = 3268) SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR ,VENTAMC VT ,LOCAL LO ,LOCPROV LP    WHERE PP.PR_KEY = PR.PR_KEY AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;

 

Timing is on.

    units    |    montopc     |    montopv

-------------+----------------+----------------

 42592548.00 | 48920814088.00 | 50700171188.00

(1 row)

 

Time: 535717.964 ms

 

9 minutes aprox.

 

 

 

 

 

With temporary tables :

 

CREATE TEMP TABLE PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268;

CREATE TEMP TABLE IDTEMP AS SELECT * FROM INVDET  WHERE ID_INT9 IN (1);

CREATE TEMP TABLE CPTEMP AS select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1;

 

SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK)) /SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0 END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CPTEMP CP   WHERE LP.PV_KEY = PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA  ORDER BY  LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY;

 

Result :

 

Timing is on.

SELECT 3653

Time: 2147.027 ms

SELECT 4216057

Time: 134861.333 ms

SELECT 433

Time: 1474.354 ms

 prkey  | codretailer | codprovider |            productdescription            | last28dayssalesmc |          margin          |   stockmc    |      daysales

--------+-------------+-------------+------------------------------------------+-------------------+--------------------------+--------------+---------------------

   3781 | 10579       | 23935       | TE CEYLAN EMBLEM 100 UN                  |       82933398.00 | -75.90128158302451567400 | 230014798.00 | 77.6576686752905024

.

.

 (20 rows)

 

Time: 43738.024 ms

 

 

Total : 3 minutes aprox.

 

 

 

Other query :

 

CREATE TEMP TABLE PRTEMP AS SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1;

CREATE TEMP TABLE PPTEMP AS SELECT * FROM PROPRO   WHERE PV_KEY = 3268;

 

SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR ,VENTAMC VT ,LOCAL LO ,LOCPROV LP    WHERE PP.PR_KEY = PR.PR_KEY AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;

 

Timing is on.

SELECT 224119

Time: 782.938 ms

SELECT 3653

Time: 1974.144 ms

    units    |    montopc     |    montopv

-------------+----------------+----------------

 42592548.00 | 48920814088.00 | 50700171188.00

(1 row)

 

Time: 524025.660 ms

 

9 minutes aprox.

 

 

 

-----------------------------------------------------------------------------------------------------------

 

 

 

 

The larger tables are :

 

INVDET :  8565114

 

VENTAMC : 57714192

 

 

 

 

Execution plan :

 

 

dbcom=# CREATE TEMP TABLE PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268;

SELECT 3653

dbcom=# CREATE TEMP TABLE IDTEMP AS SELECT * FROM INVDET  WHERE ID_INT9 IN (1);

SELECT 4216057

dbcom=# CREATE TEMP TABLE CPTEMP AS select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1;

SELECT 433

dbcom=# explain SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK)) /SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0 END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CPTEMP CP   WHERE LP.PV_KEY = PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA  ORDER BY  LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY;

                                                              QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=2000131.06..2000131.11 rows=20 width=238)

   ->  Sort  (cost=2000131.06..2000803.75 rows=269077 width=238)

         Sort Key: (sum(id.id_dec14))

         ->  HashAggregate  (cost=1984898.71..1992971.02 rows=269077 width=238)

               ->  Hash Join  (cost=75057.66..1976153.71 rows=269077 width=238)

                     Hash Cond: ((id.pr_key = pp.pr_key) AND (id.lo_key = lo.lo_key))

                     ->  Seq Scan on idtemp id  (cost=0.00..1757879.62 rows=1221962 width=72)

                     ->  Hash  (cost=74505.39..74505.39 rows=36818 width=206)

                           ->  Hash Join  (cost=620.25..74505.39 rows=36818 width=206)

                                 Hash Cond: (lp.lo_key = lo.lo_key)

                                 ->  Nested Loop  (cost=321.44..73700.33 rows=36818 width=198)

                                       ->  Hash Join  (cost=321.44..35652.72 rows=44 width=198)

                                             Hash Cond: (pr.cp_key = cp.cp_key)

                                             ->  Nested Loop  (cost=0.00..35326.25 rows=1224 width=206)

                                                   ->  Seq Scan on pptemp pp  (cost=0.00..732.24 rows=1224 width=154)

                                                   ->  Index Scan using producto_pk on producto pr  (cost=0.00..28.25 rows=1 width=52)

                                                         Index Cond: (pr_key = pp.pr_key)

                                             ->  Hash  (cost=320.64..320.64 rows=64 width=8)

                                                   ->  Seq Scan on cptemp cp  (cost=0.00..320.64 rows=64 width=8)

                                       ->  Index Scan using locprov_pv_key_idx on locprov lp  (cost=0.00..856.34 rows=838 width=16)

                                             Index Cond: (pv_key = pp.pv_key)

                                 ->  Hash  (cost=288.36..288.36 rows=836 width=8)

                                       ->  Seq Scan on local lo  (cost=0.00..288.36 rows=836 width=8)

(23 rows)

 

 

 

 

 

WITH PPTEMP AS (SELECT * FROM PROPRO WHERE PV_KEY = 3268), IDTEMP AS (SELECT * FROM INVDET  WHERE ID_INT9 IN (1)), CPTEMP AS (select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1)  SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK)) /SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0 END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CATPROD CP   WHERE LP.PV_KEY = PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA  ORDER BY  LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY"

                                                             QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=1423557229.97..1423557230.02 rows=20 width=238)

   CTE pptemp

     ->  Index Scan using propro_pv_key_idx on propro  (cost=0.00..46293.87 rows=3407 width=46)

           Index Cond: (pv_key = 3268)

   CTE idtemp

     ->  Index Scan using invdet_id_int9_idx on invdet  (cost=0.00..3211054.04 rows=4165931 width=126)

           Index Cond: (id_int9 = 1)

   ->  Sort  (cost=1420299882.07..1420411941.57 rows=44823800 width=238)

         Sort Key: (sum(id.id_dec14))

         ->  GroupAggregate  (cost=1384421302.46..1419107136.86 rows=44823800 width=238)

               ->  Sort  (cost=1384421302.46..1386802811.06 rows=952603440 width=238)

                     Sort Key: pr.pr_key, pr.pr_codigo1, pp.pp_codprov, pr.pr_desc_larga

                     ->  Hash Join  (cost=2261805.19..14387957.07 rows=952603440 width=238)

                           Hash Cond: (pp.pr_key = pr.pr_key)

                           ->  Merge Join  (cost=2025051.21..3291356.72 rows=71136713 width=210)

                                 Merge Cond: ((pp.pr_key = id.pr_key) AND (lo.lo_key = id.lo_key))

                                 ->  Sort  (cost=1483684.16..1490821.85 rows=2855078 width=162)

                                       Sort Key: pp.pr_key, lp.lo_key

                                       ->  Hash Join  (cost=1096170.53..1177547.01 rows=2855078 width=162)

                                             Hash Cond: (lp.lo_key = lo.lo_key)

                                             ->  Hash Join  (cost=1095871.72..1137990.88 rows=2855078 width=154)

                                                   Hash Cond: (pp.pv_key = lp.pv_key)

                                                   ->  CTE Scan on pptemp pp  (cost=0.00..68.14 rows=3407 width=154)

                                                   ->  Hash  (cost=1004487.43..1004487.43 rows=7310743 width=16)

                                                         ->  Seq Scan on locprov lp  (cost=0.00..1004487.43 rows=7310743 width=16)

                                             ->  Hash  (cost=288.36..288.36 rows=836 width=8)

                                                   ->  Seq Scan on local lo  (cost=0.00..288.36 rows=836 width=8)

                                 ->  Sort  (cost=541367.06..551781.88 rows=4165931 width=72)

                                       Sort Key: id.pr_key, id.lo_key

                                       ->  CTE Scan on idtemp id  (cost=0.00..83318.62 rows=4165931 width=72)

                           ->  Hash  (cost=199238.80..199238.80 rows=3001214 width=44)

                                 ->  Merge Join  (cost=121139.01..199238.80 rows=3001214 width=44)

                                       Merge Cond: (cp.cp_key = pr.cp_key)

                                       ->  Index Only Scan using catprod_pk on catprod cp  (cost=0.00..32975.13 rows=44989 width=8)

                                       ->  Sort  (cost=121138.54..121698.84 rows=224119 width=52)

                                             Sort Key: pr.cp_key

                                             ->  Seq Scan on producto pr  (cost=0.00..101221.19 rows=224119 width=52)

(37 rows)

 

 

 

 

dbcom=# CREATE TEMP TABLE PRTEMP AS SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1;

SELECT 224119

dbcom=# CREATE TEMP TABLE PPTEMP AS SELECT * FROM PROPRO   WHERE PV_KEY = 3268;

SELECT 3653

dbcom=# explain SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR ,VENTAMC VT ,LOCAL LO ,LOCPROV LP    WHERE PP.PR_KEY = PR.PR_KEY AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;

                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=12705609.96..12705609.97 rows=1 width=17)

   ->  Merge Join  (cost=6095609.99..10502328.76 rows=293770827 width=17)

         Merge Cond: (pr.pr_key = pp.pr_key)

         ->  Sort  (cost=101482.89..101557.13 rows=29694 width=8)

               Sort Key: pr.pr_key

               ->  Seq Scan on prtemp pr  (cost=0.00..99276.94 rows=29694 width=8)

         ->  Sort  (cost=5994127.10..5999073.75 rows=1978658 width=33)

               Sort Key: pp.pr_key

               ->  Merge Join  (cost=4421695.74..5787198.15 rows=1978658 width=33)

                     Merge Cond: ((lp.lo_key = vt.lo_key) AND (lp.pv_key = pp.pv_key))

                     ->  Index Only Scan using locprov_pk on locprov lp  (cost=0.00..1769448.68 rows=7310743 width=16)

                     ->  Sort  (cost=4419343.24..4424255.45 rows=1964886 width=57)

                           Sort Key: vt.lo_key, pp.pv_key

                           ->  Hash Join  (cost=298.81..4213953.57 rows=1964886 width=57)

                                 Hash Cond: (vt.lo_key = lo.lo_key)

                                 ->  Nested Loop  (cost=0.00..4186637.57 rows=1964886 width=49)

                                       ->  Seq Scan on pptemp pp  (cost=0.00..732.24 rows=1224 width=16)

                                       ->  Append  (cost=0.00..3401.41 rows=1845 width=33)

                                             ->  Seq Scan on ventamc vt  (cost=0.00..0.00 rows=1 width=64)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82) AND (pp.pr_key = pr_key))

                                             ->  Index Scan using ventamc_72_pr_key_idx on ventamc_72 vt  (cost=0.00..368.73 rows=166 width=32)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_73_pr_key_idx on ventamc_73 vt  (cost=0.00..362.39 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_74_pr_key_idx on ventamc_74 vt  (cost=0.00..371.32 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_75_pr_key_idx on ventamc_75 vt  (cost=0.00..366.24 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_76_pr_key_idx on ventamc_76 vt  (cost=0.00..365.37 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_77_pr_key_idx on ventamc_77 vt  (cost=0.00..363.91 rows=170 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_78_pr_key_idx on ventamc_78 vt  (cost=0.00..367.02 rows=170 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_79_pr_key_idx on ventamc_79 vt  (cost=0.00..359.19 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_80_pr_key_idx on ventamc_80 vt  (cost=0.00..353.77 rows=165 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_81_pr_key_idx on ventamc_81 vt  (cost=0.00..61.72 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_82_pr_key_idx on ventamc_82 vt  (cost=0.00..61.74 rows=165 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                 ->  Hash  (cost=288.36..288.36 rows=836 width=8)

                                       ->  Seq Scan on local lo  (cost=0.00..288.36 rows=836 width=8)

(55 rows)

 

 

 

dbcom=# explain WITH PRTEMP AS (SELECT * FROM PRODUCTO  WHERE PR_ESTADO = 1 ), PPTEMP AS (SELECT * FROM PROPRO  WHERE PV_KEY = 3268) SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR ,VENTAMC VT ,LOCAL LO ,LOCPROV LP    WHERE PP.PR_KEY = PR.PR_KEY AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;

                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=146624535.65..146624535.66 rows=1 width=17)

   CTE prtemp

     ->  Seq Scan on producto  (cost=0.00..101781.49 rows=224119 width=141)

           Filter: (pr_estado = 1)

   CTE pptemp

     ->  Index Scan using propro_pv_key_idx on propro  (cost=0.00..46293.87 rows=3407 width=46)

           Index Cond: (pv_key = 3268)

   ->  Merge Join  (cost=7610435.83..100188156.44 rows=6171773848 width=17)

         Merge Cond: (pr.pr_key = pp.pr_key)

         ->  Sort  (cost=24399.73..24960.03 rows=224119 width=8)

               Sort Key: pr.pr_key

               ->  CTE Scan on prtemp pr  (cost=0.00..4482.38 rows=224119 width=8)

         ->  Sort  (cost=7586036.10..7599805.07 rows=5507586 width=33)

               Sort Key: pp.pr_key

               ->  Hash Join  (cost=5466391.59..6969379.55 rows=5507586 width=33)

                     Hash Cond: (vt.lo_key = lo.lo_key)

                     ->  Merge Join  (cost=5466092.78..6893351.43 rows=5507586 width=49)

                           Merge Cond: ((lp.lo_key = vt.lo_key) AND (lp.pv_key = pp.pv_key))

                           ->  Index Only Scan using locprov_pk on locprov lp  (cost=0.00..1769448.68 rows=7310743 width=16)

                           ->  Sort  (cost=5463740.28..5477413.41 rows=5469253 width=49)

                                 Sort Key: vt.lo_key, pp.pv_key

                                 ->  Nested Loop  (cost=0.00..4851651.22 rows=5469253 width=49)

                                       ->  CTE Scan on pptemp pp  (cost=0.00..68.14 rows=3407 width=16)

                                       ->  Append  (cost=0.00..1405.55 rows=1845 width=33)

                                             ->  Seq Scan on ventamc vt  (cost=0.00..0.00 rows=1 width=64)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82) AND (pp.pr_key = pr_key))

                                             ->  Index Scan using ventamc_72_pr_key_idx on ventamc_72 vt  (cost=0.00..145.41 rows=166 width=32)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_73_pr_key_idx on ventamc_73 vt  (cost=0.00..143.07 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_74_pr_key_idx on ventamc_74 vt  (cost=0.00..146.39 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_75_pr_key_idx on ventamc_75 vt  (cost=0.00..144.52 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_76_pr_key_idx on ventamc_76 vt  (cost=0.00..144.19 rows=169 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_77_pr_key_idx on ventamc_77 vt  (cost=0.00..143.68 rows=170 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_78_pr_key_idx on ventamc_78 vt  (cost=0.00..144.82 rows=170 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_79_pr_key_idx on ventamc_79 vt  (cost=0.00..141.87 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_80_pr_key_idx on ventamc_80 vt  (cost=0.00..139.84 rows=165 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_81_pr_key_idx on ventamc_81 vt  (cost=0.00..55.84 rows=167 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                                             ->  Index Scan using ventamc_82_pr_key_idx on ventamc_82 vt  (cost=0.00..55.92 rows=165 width=33)

                                                   Index Cond: (pr_key = pp.pr_key)

                                                   Filter: ((pe_key >= 72) AND (pe_key <= 82))

                     ->  Hash  (cost=288.36..288.36 rows=836 width=8)

                           ->  Seq Scan on local lo  (cost=0.00..288.36 rows=836 width=8)

(61 rows)

 

 

 

 

 

 

regards

 

Thanks in advance

 

Cristian Iturrieta

Zuiderhoek, Jeroen | 17 Dec 13:47 2014
Picon

Building debug version with MSVC 2013 for 64bit: No _d postfix on dll and lib files...

Hello,

 

Some time ago, I posted the question below. Unfortunately I didn’t get any response.

Could it be that I should better post this question to one of the other pgsql lists, and then which one…?

 

Thanks,

 

Jeroen

 

From: Zuiderhoek, Jeroen [mailto:jeroen.zuiderhoek <at> hitt.nl]
Sent: Wednesday, November 19, 2014 1:26 PM
To: pgsql-general <at> postgresql.org
Subject: [GENERAL] Building debug version with MSVC 2013 for 64bit: No _d postfix on dll and lib files...

 

Hello,

 

I am trying to build postgresql (and to be more specific, the libraries libpq, libpgtypes and libecpg) with MSVC 2013 for using them in a 64bit application. I downloaded the source code (v9.3.5), unzipped the file and run ‘build DEBUG libpq’, as nicely described here: http://www.postgresql.org/docs/9.3/static/install-windows-full.html

 

All works well, and the build results (including dll and lib files) end up in Debug\libpq.

The problem is that these files don’t get the _d postfix. This means that the release and debug lib/dll files have the same name.

 

The application that uses these libraries shall be built for debug and release. For all other libs, we have *.lib/*.dll and *_d.lib/*_d.dll to differentiate between the debug and release version of the libs. This is all automatically handled by the build tooling we use. This now clashes with the postgresql libraries where the name is the same for debug/release.

 

Do you have any suggestion what I should do to get the _d postfix on the debug build version?

 

Note that building all our 3rd party libraries is handled by an automated process, so preferably, the solution should not involve manual editing etc.. I could add-in some automatic search/replace-like stuff to patch some files, if necessary.

 

Thanks a lot for your help!

 

Jeroen

 

 

Mike Blackwell | 18 Dec 19:16 2014
Picon

pl/pgsql trigger function - compare *most* columns in NEW vs. OLD

Is there a simple notation for comparing most columns in the new and old records in a pl/pgsql trigger function?  Something like

(new.b, new.c, new.d) = (old.b, old.c, old.d)

works to compare all the columns except 'a', but is fragile in that it needs to be updated any time a column is added to the table, and is rather messy looking when the actual table has many columns.

Is there a better approach to detecting a change in any column except a few specific ones?


Adrian Klaver | 18 Dec 02:23 2014

PGDATA

Just out of curiosity is there a reason PGDATA is not listed with the 
other environment variables here?:

http://www.postgresql.org/docs/9.3/interactive/libpq-envars.html

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Patrick Krecker | 17 Dec 23:50 2014

Strange error message when reference non-existent column foo."count"

I encountered this today and it was quite surprising:

select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

create table foo as (select generate_series(1,3));

As expected, the following fails:

select count from foo;
ERROR:  column "count" does not exist
LINE 1: select count from foo;
               ^
But if I change the syntax to something I thought was equivalent:

select foo."count" from foo;
 count 
-------
     3
(1 row)

It works! This was quite surprising to me. Is this expected behavior, that you can call an aggregate function without any parentheses (I can't find any other syntax that works for count() sans parentheses, and this behavior doesn't occur for any other aggregate)?
Vincent Veyron | 17 Dec 18:03 2014
Picon

Re: Storing Video's or vedio file in DB.

On Wed, 17 Dec 2014 16:51:10 +0530
VENKTESH GUTTEDAR <venkteshguttedar <at> gmail.com> wrote:

Hi Venktesh,

[you should not reply directly to me; hit reply all to post your messages to the list]

> 
>     can you help me in achieving this, i mean how to store it in a file
> system how to fetch it from there.

Nope. It's not postgresql related, it really depends on what tools you use to build your web site (in your
case Django which I know nothing about)

>     and storing in a file system and giving the path will be secure.?
> 
> 

That's part of the configuration of your web server, you'll have to study their documentation/lists

-- 
					Salutations, Vincent Veyron 

https://libremen.com/ 
Legal case, contract and insurance claim management software

--

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

Kiswono Prayogo | 17 Dec 14:59 2014
Picon

Feature Request: json_extend, jsonb_extend

It would be nice if PostgreSQL has *_extend (similar to $.extend from jQuery or _.extend from UnderscoreJs) for JSON/JSONB columns, so we could write it like this:

UPDATE tablename 
  SET jsoncolumn = json_extend(jsoncolumn, $2) 
  WHERE id = $1;

instead of this:

UPDATE tablename 
  SET data = (
    SELECT json_object_agg(key, value)::jsonb 
      FROM 
        ( SELECT * FROM jsonb_each( SELECT data FROM tablename WHERE id=$1 )
          UNION ALL
          SELECT * FROM jsonb_each( $2 ) 
        ) x1
  ) WHERE id=$1;

Gmane