[GENERAL] PHP-Shop with PostgreSQL

[Michelle Konzack]

Good evening *,

before I restart coding things myself here a/some question/s:

Long time ago (6-8 years) I was  searching  for   a  simple   PHP  based
OnlineShop Software which use PostgreSQL without breaking  the  head  of
the owner.

It seems that unfortunately all   this  shop  softwares  continue  using
crappy MySQL and its proprietary functions which are NOT portable to any
other SQL databases.

Now I run a bunch of PostgreSQL databases (since 16 years)  including  a
cluster of 28 TByte and I am definitively not willing to switch to MySQL

Which should be understandable by anyone on this list!  ;-)


However, I need a more or less simple PHP and PostgreSQL based  shopping
cart with 3 levels of categories...  PayPal, Shiping labels, etc.

Also I have the need to adapt the Shop-Language (I need  more  then  EN,
DE, FR, EE, RU,...)  POOTLE based integration would be perfect!

Also the Item-Describtions in the shop should  be  possibel  in  several
languages.  (I have done this before in a  table  with  one  column  per
Language and per Item one row.  Exportable as POT files and import of PO
files)

Since I run my apache2 with a bunch of hosts, several installations (one
per Apache2 host) must be possibel.


Can someone recommend me a shop system which met my requirements?


...or is someone willing to create a new project with me together?

The shop should fullfill European (EU) requirements
and of course languages plus being Open Source Software.


Thanks in avance

-- 
Michelle KonzackITSystems
GNU/Linux Developer 0033-6-61925193


-- 
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] PHP-Shop with PostgreSQL

[John DeSoi]

> On Nov 8, 2016, at 8:46 AM, Michelle Konzack  wrote:
> 
> Can someone recommend me a shop system which met my requirements?

Drupal with Ubercart? Probably does not qualify as "simple" but should be able 
to do everything you listed.

https://www.drupal.org/project/ubercart

John DeSoi, Ph.D.



-- 
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] Fwd: Creating multiple instances of postresql on Windows environment

[Adrian Klaver]

On 11/07/2016 07:27 PM, kaustubh kelkar wrote:


Hi ,

I am a PostgreSQL user who wants to create multiple instances of
PostgreSQL database server. I am using PostgreSQL 9.4 and above. 

I tried to create more than 2 instances on Linux environment in which I
was successful. But, for windows environment, I tried with the help of
pgAdmin4 and with the help of commands (initdb and some more commands.)
In both cases , there is some issue related to the ports. Please find
the attached screenshot for the reference.


To run more then one instance of Postgres on one machine each needs its 
own port. Have you done that?


The screenshot indicates a different problem, namely pgAdmin(?) cannot 
find a running server on port 5434 on the localhost. There can be 
several reasons for this:


1) The server is not running, eg the start up script failed.
Solution: Verify the server has actually started

2) The server is not running on localhost.
Solution: Verify where it is hosted.

3) It is running on localhost but not on port 5434.
	Solution: Verify that the port variable in postgresql.conf for that 
instance is set to 5434 and that the server was restarted to see the change.


3) It is running and is using port 5434, but a firewall rule is blocking 
access.
	Solution: Check whether you have a firewall running and whether it is 
blocking port 5434.




__ __

Please help me to resolve the issue.


__ __








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

[GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[otar shavadze]

I have table with 500 000 rows, I have int[] column "my_array" in this
table, this array column contains minimum 1 and maximum 5 different values.

I have GIN index on my_array column:

* "CREATE INDEX idx ON table_name USING GIN(my_array gin__int_ops)"*

Then I use this query: "*SELECT * FROM  table_name WHERE my_array @>
'{3}'::integer[]  ORDER BY id LIMIT 50"*

Execution time of this query is approximately 500-1000 ms. Then if I drop
gin index "*idx*", query works extremely fast, less than 20 ms.

But, if I search value, which does not exists at all, for example no one
array not contains number "77" and I search: * "WHERE my_array @>
'{77}'::integer[]" *, then using gin index is much better and fast, (less
than 20 ms), but without index, query takes 500-1000 ms.


So, what to do? For values which does not in any one rows, using index is
much better, but for values,  which are at least in several rows, using
 index, slows down performance.

Can somehow make, that searching was always fast (when value exist in array
and when not)

Re: [GENERAL] Fwd: Creating multiple instances of postresql on Windows environment

[Howard News]

On 08/11/2016 03:27, kaustubh kelkar wrote:


Hi ,

I am a PostgreSQL user who wants to create multiple instances of 
PostgreSQL database server. I am using PostgreSQL 9.4 and above.


I tried to create more than 2 instances on Linux environment in which 
I was successful. But, for windows environment, I tried with the help 
of pgAdmin4 and with the help of commands (initdb and some more 
commands.) In both cases , there is some issue related to the ports. 
Please find the attached screenshot for the reference.


Please help me to resolve the issue.




Hi, I am not sure of your requirements but you can alternatively run a 
single database cluster with multiple databases. So run the cluster on a 
single port as in the vanilla windows installation, and then use "CREATE 
DATABASE dbname;"  for each database you need.


Ignore this if it is not what you are asking :)

Re: [GENERAL] PHP-Shop with PostgreSQL

[Joshua D. Drake]

Hello,

Drupal + Ubercart should service your needs.

Sincerely,

JD
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] Must I create an index for child table if master table have one?

[David G. Johnston]

On Tue, Nov 8, 2016 at 11:08 AM, Edmundo Robles 
wrote:

> Hi!
>
>  i have a  simple question,  if the master table have an index, must  i
> have create  the same index in a child  table?
> what is  the best practice  for  indexes  in table partition ?
>

Indexes are self-contained within the physical table on which they are
defined.  The index on the master contains no rows that are present in the
child table.

David J.​

[GENERAL] Must I create an index for child table if master table have one?

[Edmundo Robles]

Hi!

 i have a  simple question,  if the master table have an index, must  i
have create  the same index in a child  table?
what is  the best practice  for  indexes  in table partition ?

Re: [GENERAL] Must I create an index for child table if master table have one?

[Melvin Davidson]

On Tue, Nov 8, 2016 at 1:33 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Nov 8, 2016 at 11:08 AM, Edmundo Robles 
> wrote:
>
>> Hi!
>>
>>  i have a  simple question,  if the master table have an index, must  i
>> have create  the same index in a child  table?
>> what is  the best practice  for  indexes  in table partition ?
>>
>
> Indexes are self-contained within the physical table on which they are
> defined.  The index on the master contains no rows that are present in the
> child table.
>
> David J.​
>
>
>Indexes are self-contained within the physical table on which they are
defined
*IOW, yes. *

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Must I create an index for child table if master table have one?

[Melvin Davidson]

*To clarify, if you are talking about partitioning, then you almost
certainly want to create a similar index on the child table.*
*If you are referring to a Foreign Key Constraint, then it is also good
idea to make that index, but not necessary.*

On Tue, Nov 8, 2016 at 1:46 PM, Melvin Davidson 
wrote:

>
>
> On Tue, Nov 8, 2016 at 1:33 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Nov 8, 2016 at 11:08 AM, Edmundo Robles 
>> wrote:
>>
>>> Hi!
>>>
>>>  i have a  simple question,  if the master table have an index, must  i
>>> have create  the same index in a child  table?
>>> what is  the best practice  for  indexes  in table partition ?
>>>
>>
>> Indexes are self-contained within the physical table on which they are
>> defined.  The index on the master contains no rows that are present in the
>> child table.
>>
>> David J.​
>>
>>
> >Indexes are self-contained within the physical table on which they are
> defined
> *IOW, yes. *
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[Oleg Bartunov]

On Tue, Nov 8, 2016 at 8:43 PM, otar shavadze  wrote:

> I have table with 500 000 rows, I have int[] column "my_array" in this
> table, this array column contains minimum 1 and maximum 5 different values.
>

you didn't show us postgres version.


>
> I have GIN index on my_array column:
>
> * "CREATE INDEX idx ON table_name USING GIN(my_array gin__int_ops)"*
>
> Then I use this query: "*SELECT * FROM  table_name WHERE my_array @>
> '{3}'::integer[]  ORDER BY id LIMIT 50"*
>
> Execution time of this query is approximately 500-1000 ms. Then if I drop
> gin index "*idx*", query works extremely fast, less than 20 ms.
>

explain analyze would help us to see the problem.



>
> But, if I search value, which does not exists at all, for example no one
> array not contains number "77" and I search: * "WHERE my_array @>
> '{77}'::integer[]" *, then using gin index is much better and fast, (less
> than 20 ms), but without index, query takes 500-1000 ms.
>
>
> So, what to do? For values which does not in any one rows, using index is
> much better, but for values,  which are at least in several rows, using
>  index, slows down performance.
>
> Can somehow make, that searching was always fast (when value exist in
> array and when not)
>
>
>

[GENERAL] Best practices to manage custom statistics

[Moreno Andreo]

Hi,
I'm checking if there's a best way to obtain stastistics based on 
my database tables


Here's the scenario.
First of all, technical details:
- Postgresql 9.1, Ubuntu 12 on a 4 core, 32 GB machine with 600 GB disk 
migrating to Postgresql 9.5.3, Debian 8 on a 8-core, 52 GB machine with 
2 TB disk.
- 350 databases, 350 users, every user connects to his own database and 
his teammates' (max 10 in total) so each user can connect to max 10 
databases at a time



My application needs to achieve a certain number of statistics (how many 
records are in a certain state, how many are in another state) to send 
back to user.
This is obtained, at the moment, with a select count(*) from . (that 
involves 4 joins on 4 tables) to be run run every 20 secs from each 
client connected to the cluster (ATM about 650 clients configured, about 
200 concurrent) to each database it has rights to connect.


I noticed that in some cases, especially when working with not-so-small 
datasets (200k rows x 95 cols), and sometines returning not-so-small 
datasets (10k rows) the query performs not so well, but the worst thing 
is that it raises overall server load (I/O) and bandwidth usage. While 
bandwidth is not a problem (I have spikes at 20 Mbps while "normal" 
traffic speed is at about 3Mbps, but I have 1 Gbps available), server 
load *is* a main problem, because in high-access periods 
(summer/holidays) I see my server load go up to 22-25 on a 4-core 
machine, and users call complaining for timeouts and slowness.


Even if I'm migrating to a better instance, I'm still trying to 
"normalize" this feature.
I can start looking at indices (I'm not quite sure that those fields in 
WHERE clause are all indexed), but I don't think it would boost its 
performance.


I thought about having a table, say, 'tbl_counters', like this

CREATE TABLE tbl_counters{
uuid coduser,
int counter1,
int counter2,

int counterx
};
updated by trigger (when a value in a table is INSERTed/UPDATEd/DELETEd 
it fires a function that increments/decrements values for counter x at 
user y).
Just to avoid eventual trigger skipping its update, one time a day (say 
at 2 am) a cron performs the above "monster query" for every database 
and adjusts, if necessary, counter values for each user reflecting real 
values.


In your experience, would this approach help me lower server load?
Are there any other approach I can try?

If more details are needed, just ask.

Thanks in advance and sorry for the long message (but I had to explain 
such a complex thing)

Moreno.-



--
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 practices to manage custom statistics

[Adrian Klaver]

On 11/08/2016 12:13 PM, Moreno Andreo wrote:

Hi,
I'm checking if there's a best way to obtain stastistics based on my
database tables

Here's the scenario.
First of all, technical details:
- Postgresql 9.1, Ubuntu 12 on a 4 core, 32 GB machine with 600 GB disk
migrating to Postgresql 9.5.3, Debian 8 on a 8-core, 52 GB machine with
2 TB disk.
- 350 databases, 350 users, every user connects to his own database and
his teammates' (max 10 in total) so each user can connect to max 10
databases at a time


My application needs to achieve a certain number of statistics (how many
records are in a certain state, how many are in another state) to send
back to user.
This is obtained, at the moment, with a select count(*) from . (that
involves 4 joins on 4 tables) to be run run every 20 secs from each
client connected to the cluster (ATM about 650 clients configured, about
200 concurrent) to each database it has rights to connect.

I noticed that in some cases, especially when working with not-so-small
datasets (200k rows x 95 cols), and sometines returning not-so-small
datasets (10k rows) the query performs not so well, but the worst thing
is that it raises overall server load (I/O) and bandwidth usage. While
bandwidth is not a problem (I have spikes at 20 Mbps while "normal"
traffic speed is at about 3Mbps, but I have 1 Gbps available), server
load *is* a main problem, because in high-access periods
(summer/holidays) I see my server load go up to 22-25 on a 4-core
machine, and users call complaining for timeouts and slowness.

Even if I'm migrating to a better instance, I'm still trying to
"normalize" this feature.
I can start looking at indices (I'm not quite sure that those fields in
WHERE clause are all indexed), but I don't think it would boost its
performance.

I thought about having a table, say, 'tbl_counters', like this

CREATE TABLE tbl_counters{
uuid coduser,
int counter1,
int counter2,

int counterx
};
updated by trigger (when a value in a table is INSERTed/UPDATEd/DELETEd
it fires a function that increments/decrements values for counter x at
user y).
Just to avoid eventual trigger skipping its update, one time a day (say
at 2 am) a cron performs the above "monster query" for every database
and adjusts, if necessary, counter values for each user reflecting real
values.

In your experience, would this approach help me lower server load?
Are there any other approach I can try?


Instead of pushing why not pull. In other words do the users really 
check/need the statistics every 20 secs? Given that you say exact is not 
important over the course of day, why not create a mechanism for the 
user to poll the database when they need the information.




If more details are needed, just ask.

Thanks in advance and sorry for the long message (but I had to explain
such a complex thing)
Moreno.-






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

[GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[otar shavadze]

I increased rows limit from 50 to 500, because now, difference visible much
better, so query is:

explain analyze *SELECT * FROM table_name WHERE my_array @>
'{x}'::integer[] ORDER BY id desc LIMIT 500*


with GIN index:

"Limit  (cost=107.83..109.08 rows=500 width=905) (actual
time=978.256..978.293 rows=500 loops=1)"
"  ->  Sort  (cost=107.83..109.16 rows=533 width=905) (actual
time=978.254..978.272 rows=500 loops=1)"
"Sort Key: id DESC"
"Sort Method: top-N heapsort  Memory: 589kB"
"->  Bitmap Heap Scan on table_name  (cost=23.93..83.69 rows=533
width=905) (actual time=50.612..917.422 rows=90049 loops=1)"
"  Recheck Cond: (my_array @> '{8}'::integer[])"
"  Heap Blocks: exact=46525"
"  ->  Bitmap Index Scan on idx  (cost=0.00..23.80 rows=533
width=0) (actual time=35.054..35.054 rows=90052 loops=1)"
"Index Cond: (my_array @> '{8}'::integer[])"
"Planning time: 0.202 ms"
"Execution time: 978.718 ms"


Without index:

"Limit  (cost=7723.12..7724.37 rows=500 width=122) (actual
time=184.041..184.102 rows=500 loops=1)"
"  ->  Sort  (cost=7723.12..7724.45 rows=534 width=122) (actual
time=184.039..184.052 rows=500 loops=1)"
"Sort Key: id DESC"
"Sort Method: top-N heapsort  Memory: 157kB"
"->  Seq Scan on table_name (cost=0.00..7698.93 rows=534 width=122)
(actual time=0.020..176.079 rows=84006 loops=1)"
"  Filter: (my_array @> '{14}'::integer[])"
"  Rows Removed by Filter: 450230"
"Planning time: 0.165 ms"
"Execution time: 184.155 ms"


Postgres version: 9.5; OS: Windows 7; RAM: 8GB

In picture is some config current values.


p.s. In "pg_stats" really many values (long lists in "most_common_vals",
"most_common_freqs") and in another columns
Which one columns should I show you? All?

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

[GENERAL] Running on Docker, AWS with Data Stored on EBS

[Ryan Mahoney]

Hi All,


TL;TR: Can a new PostgreSQL process, running on a different server instance 
effectively resume operations by reading the same data directory location as 
another PostgreSQL process that is no longer running?


- - -


I have an application that is deployed to AWS as a docker container.  Although 
it is a single application, the container is running multiple services via 
supervisor.  One of those services is PostrgeSQL.


Currently I dump and reload the database between deployments, as each 
deployment completely destroys the server instance and creates a new one.


It is a small application without much data or usage load.


I'd like to store my data on a separate EBS volume.  Then, I was thinking that 
each time I perform a new deployment, I would attach the new server instance to 
the original EBS volume.


At any given moment, there would only ever be one PostgreSQL process attached 
to the data directory... but at any given time that would be a different 
process running on a different server.


I know that I can just test this out and see what happens, but I am concerned 
that even if it does appear to work at first, that it might still lead to some 
data corruption down the line if it is an incorrect strategy.


I have also considered using Amazon's hosted PostgreSQL service. While that 
would certainly work, I don't want to pay for an extra service, I'd like to use 
the most recent PostgreSQL version and I think my application will be faster if 
the data is served from the same instance.


If my application was larger, all of this would be moot because I'd run a 
dedicated PostgreSQL instance or just use RDS... but it isn't so I'd rather 
save money :)


Thanks in advance for your help,

Ryan

Re: [GENERAL] Running on Docker, AWS with Data Stored on EBS

[David G. Johnston]

On Tue, Nov 8, 2016 at 12:48 PM, Ryan Mahoney 
wrote:

> Hi All,
>
> TL;TR: Can a new PostgreSQL process, running on a different server
> instance effectively resume operations by reading the same data directory
> location as another PostgreSQL process that is no longer running?
>
In short - yes.

Avoiding concurrent access and ensuring that the various PostgreSQL
binaries involved are all running at minimum the same major (9.6.x) version
of PostgreSQL, and ideally the same configuration, is what matters.

David J.

[GENERAL] resolution order for foreign key actions?

[Karl Czajkowski]

Hi,

Is there a formal definition for the order in which constraint actions
(i.e. the ON DELETE or ON UPDATE rules) are applied when there are
multiple overlapping/relevant constraints?

I have struggled to find an answer in the manual, but my experiments
suggest that they are interpreted in the order in which the
constraints were defined and the first rule in this order is applied
while subsequent rules are ignored.  This can be very confusing if one
rule says CASCADE and another NO ACTION, and you need to understand
this order of definition to know whether a delete will cascade or
raise an error.

Is there a definitive way to introspect the informatation_schema or
pg_catalog to determine which behaviors will effectively apply to a
given "DELETE FROM ..." or "UPDATE ..." statement?

Thanks,


Karl



-- 
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] resolution order for foreign key actions?

[Adrian Klaver]

On 11/08/2016 12:08 PM, Karl Czajkowski wrote:

Hi,

Is there a formal definition for the order in which constraint actions
(i.e. the ON DELETE or ON UPDATE rules) are applied when there are
multiple overlapping/relevant constraints?

I have struggled to find an answer in the manual, but my experiments
suggest that they are interpreted in the order in which the
constraints were defined and the first rule in this order is applied
while subsequent rules are ignored.  This can be very confusing if one
rule says CASCADE and another NO ACTION, and you need to understand
this order of definition to know whether a delete will cascade or
raise an error.


Can you provide an example?



Is there a definitive way to introspect the informatation_schema or
pg_catalog to determine which behaviors will effectively apply to a
given "DELETE FROM ..." or "UPDATE ..." statement?


Just to clear you are talking about FK constraints, correct?

AFAIK they are just a form of trigger and the rules they follow can be 
found here:


https://www.postgresql.org/docs/9.5/static/sql-createtrigger.html

"If multiple triggers of the same kind are defined for the same event, 
they will be fired in alphabetical order by name."


There is more, so I would read through the whole thing.




Thanks,


Karl






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

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[Jeff Janes]

On Tue, Nov 8, 2016 at 9:43 AM, otar shavadze  wrote:

> I have table with 500 000 rows, I have int[] column "my_array" in this
> table, this array column contains minimum 1 and maximum 5 different values.
>
> I have GIN index on my_array column:
>
> * "CREATE INDEX idx ON table_name USING GIN(my_array gin__int_ops)"*
>
> Then I use this query: "*SELECT * FROM  table_name WHERE my_array @>
> '{3}'::integer[]  ORDER BY id LIMIT 50"*
>
> Execution time of this query is approximately 500-1000 ms. Then if I drop
> gin index "*idx*", query works extremely fast, less than 20 ms.
>

Please post the information requested here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

Also, can you show,

select * from pg_stats where tablename ='table_name' and attname='my_array'
\x\g\x

Cheers,

Jeff

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[Tom Lane]

otar shavadze  writes:
> "  ->  Bitmap Index Scan on idx  (cost=0.00..23.80 rows=533
> width=0) (actual time=35.054..35.054 rows=90052 loops=1)"
> "Index Cond: (my_array @> '{8}'::integer[])"

Seems like your problem here is that the planner has no idea about the
selectivity of this condition --- if it did, I think it would have
made the right choice, because it would have made a much higher estimate
for the cost of the indexscan.

AFAICT, Postgres 9.5 does make a reasonably correct guess when given
up-to-date stats.  I speculate that you need to ANALYZE this table.
If there are a lot of distinct possible values in the arrays, increasing
the statistics target for the column might be needed.

regards, tom lane


-- 
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] which work memory parameter is used for what?

[Andreas Joseph Krogh]

På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce <
pie...@hogranch.com >:
On 11/8/2016 2:34 PM, Hector Yuen wrote:
 > I am confused on which are the parameters for different queries. I am
 > trying to run VACUUM on a big table, and it is easier for me to set
 > the work memory for the specific session instead of tuning it in
 > postgresql.conf.
 >
 > I noticed that if I do:
 >
 > set work_mem='1GB';
 >
 > it doesn't help VACUUM, I have to do:
 >
 > set maintenance_work_mem='1GB';
 >
 > to accelerate the operation. I could notice that by running VACUUM
 > VERBOSE and see that the table was scanned less times an the operation
 > finished a lot faster.
 >
 > My question is, for which operations does work_mem matter and for
 > which ones does maintenance_work_mem do? I am specially interested in
 > operations like ANALYZE and VACUUM, I believe ANALYZE depends on
 > work_mem and VACUUM on maintenance_work_mem.
 >
 > Can you confirm my understanding?
 >

 https://www.postgresql.org/docs/current/static/runtime-config-resource.html


 maintenance_work_mem is used by vacuum and create index operations
 (including implicit index creation such as add foreign key).
 
There is no such thing in PG.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] which work memory parameter is used for what?

[John R Pierce]

On 11/8/2016 2:34 PM, Hector Yuen wrote:
I am confused on which are the parameters for different queries. I am 
trying to run VACUUM on a big table, and it is easier for me to set 
the work memory for the specific session instead of tuning it in 
postgresql.conf.


I noticed that if I do:

set work_mem='1GB';

it doesn't help VACUUM, I have to do:

set maintenance_work_mem='1GB';

to accelerate the operation. I could notice that by running VACUUM 
VERBOSE and see that the table was scanned less times an the operation 
finished a lot faster.


My question is, for which operations does work_mem matter and for 
which ones does maintenance_work_mem do? I am specially interested in 
operations like ANALYZE and VACUUM, I believe ANALYZE depends on 
work_mem and VACUUM on maintenance_work_mem.


Can you confirm my understanding?



https://www.postgresql.org/docs/current/static/runtime-config-resource.html


maintenance_work_mem is used by vacuum and create index operations 
(including implicit index creation such as add foreign key).


work_mem is used by client queries, and can be allocated several times 
per query (for things like complex queries involving nested sorts), and 
if you hve 100s of connections, could be used a lot of times 
simulataneously, so 1GB kind of values are generally NOT a good idea.




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] which work memory parameter is used for what?

[Hector Yuen]

Hello,

I am confused on which are the parameters for different queries. I am
trying to run VACUUM on a big table, and it is easier for me to set the
work memory for the specific session instead of tuning it in
postgresql.conf.

I noticed that if I do:

set work_mem='1GB';

it doesn't help VACUUM, I have to do:

set maintenance_work_mem='1GB';

to accelerate the operation. I could notice that by running VACUUM VERBOSE
and see that the table was scanned less times an the operation finished a
lot faster.

My question is, for which operations does work_mem matter and for which
ones does maintenance_work_mem do? I am specially interested in operations
like ANALYZE and VACUUM, I believe ANALYZE depends on work_mem and VACUUM
on maintenance_work_mem.

Can you confirm my understanding?

Thanks

-- 
-h

Re: [GENERAL] resolution order for foreign key actions?

[Karl Czajkowski]

On Nov 08, David G. Johnston modulated:
...
> ON DELETE starts with the "one" side of a one-to-many relationship​. 
> When deleting the one row the question is what should be done with the
> many rows which refer to it.  If ALL of the many rows agree to be
> deleted then the one row in question can go away and no error is
> raised.  If ANY of the many rows refuse to die then the one row in
> question must remain in order to maintain referential integrity - thus
> an error will be raised.
> 

I think I had the same intuition going into this.

However, I am testing with an artificial scenario to focus on the
ordering/precedence behavior. I was even hoping PostgreSQL might raise
an error when I created apparently conflicting constraints, but
unfortunately  it does something much more confusing...

You might consider this to explore what happens if someone
accidentally redefines constraints with conflicting actions.  I just
redefine the same constraint with only varying constraint name and ON
DELETE clause.

Here, I have assigned constraint names to demonstrate that the rules
are NOT applied based on a lexicographic sort of constraint names but
rather on order of definition (perhaps there is another
internally-generated name that sorts in order of definition?):

==
ALTER TABLE refs ADD CONSTRAINT z FOREIGN KEY (t_id) REFERENCES targets 
(id) ON DELETE NO ACTION;
ALTER TABLE
ALTER TABLE refs ADD CONSTRAINT y FOREIGN KEY (t_id) REFERENCES targets 
(id) ON DELETE SET NULL;
ALTER TABLE
ALTER TABLE refs ADD CONSTRAINT x FOREIGN KEY (t_id) REFERENCES targets 
(id) ON DELETE CASCADE;
ALTER TABLE

This test will show that ON DELETE NO ACTION is in effect due to constraint 
z.

 Table "public.refs"
 Column |  Type   | Modifiers 
+-+---
 id | integer | not null default nextval('refs_id_seq'::regclass)
 t_id   | integer | not null
Indexes:
"refs_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"x" FOREIGN KEY (t_id) REFERENCES targets(id) ON DELETE CASCADE
"y" FOREIGN KEY (t_id) REFERENCES targets(id) ON DELETE SET NULL
"z" FOREIGN KEY (t_id) REFERENCES targets(id)

-
Content of refs table before deletion of target:
SELECT * FROM refs;
 id | t_id 
+--
  1 |1
  2 |2
  3 |3
(3 rows)

-
Attempting to delete a target:
DELETE FROM targets WHERE name = 'foo1' RETURNING *;
ERROR:  update or delete on table "targets" violates foreign key constraint 
"z" on table "refs"
DETAIL:  Key (id)=(2) is still referenced from table "refs".


The attached BASH script will perform a sequence of tests defining the
constraints in different orders and showing the results.  The excerpt
above is from the first test scenario.

It accepts optional arguments which are passed to 'psql' and can run
with no arguments if you can talk to your default DB with 'psql'
absent of any arguments, i.e. with Unix domain socket authentication.
It only creates and destroys tables public.targets and public.refs...


Thanks,

Karl



fkr-test.sh
Description: Bourne shell script

-- 
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] Running on Docker, AWS with Data Stored on EBS

[Ryan Mahoney]

Thanks for your prompt response.


I'm so glad the use-case will work -- and sounds somewhat normative.


It also looks like the PostgreSQL memory footprint is quite small... so even 
using the smallest type of EC2 instance is viable (assuming the utilization and 
data size remain small).


With Appreciation,

Ryan



From: David G. Johnston 
Sent: Tuesday, November 8, 2016 3:19:02 PM
To: Ryan Mahoney
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Running on Docker, AWS with Data Stored on EBS

On Tue, Nov 8, 2016 at 12:48 PM, Ryan Mahoney 
> wrote:

Hi All,

TL;TR: Can a new PostgreSQL process, running on a different server instance 
effectively resume operations by reading the same data directory location as 
another PostgreSQL process that is no longer running?

In short - yes.

Avoiding concurrent access and ensuring that the various PostgreSQL binaries 
involved are all running at minimum the same major (9.6.x) version of 
PostgreSQL, and ideally the same configuration, is what matters.

David J.

Re: [GENERAL] resolution order for foreign key actions?

[David G. Johnston]

On Tue, Nov 8, 2016 at 1:20 PM, Adrian Klaver 
wrote:

> On 11/08/2016 12:08 PM, Karl Czajkowski wrote:
>
>> Hi,
>>
>> Is there a formal definition for the order in which constraint actions
>> (i.e. the ON DELETE or ON UPDATE rules) are applied when there are
>> multiple overlapping/relevant constraints?
>>
>> I have struggled to find an answer in the manual, but my experiments
>> suggest that they are interpreted in the order in which the
>> constraints were defined and the first rule in this order is applied
>> while subsequent rules are ignored.  This can be very confusing if one
>> rule says CASCADE and another NO ACTION, and you need to understand
>> this order of definition to know whether a delete will cascade or
>> raise an error.
>>
>
> Can you provide an example?
>
>
​Karl,​

​Yes, please, but...

ON DELETE starts with the "one" side of a one-to-many relationship​.  When
deleting the one row the question is what should be done with the many rows
which refer to it.  If ALL of the many rows agree to be deleted then the
one row in question can go away and no error is raised.  If ANY of the many
rows refuse to die then the one row in question must remain in order to
maintain referential integrity - thus an error will be raised.

​ANY/ALL logic generally shouldn't depend on the order in which the
triggers fire though I suppose you could possible setups a convoluted
series of FKs that would cause it to do so.  Its hard to imagine one so if
you have a ready example that would help.

Note, I'm going off of logic here - hopefully the SQL Standards Committee
hasn't gotten to deeply involved in this area :)

David J.​

Re: [GENERAL] PHP-Shop with PostgreSQL

[Raymond O'Donnell]

On 08/11/16 18:24, Joshua D. Drake wrote:

Hello,

Drupal + Ubercart should service your needs.


+1 to what the others said about Drupal + Ubercart: easy to set up, but 
very customisable too if you need to. There are a number of 
freely-available themes which are aimed at online shops and work very well.


Ray.




--
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] Running on Docker, AWS with Data Stored on EBS

[David G. Johnston]

On Tue, Nov 8, 2016 at 1:41 PM, Ryan Mahoney 
wrote:

> I'm so glad the use-case will work -- and sounds somewhat normative.
>
​The program and the data are distinct things - which is why you can
upgrade from say 9.5.1 to 9.5.3 by simply updating the program.  Heck, a
simple reboot of a typical server causes a new program instance to launch
that is unique from the one that was previously running.

The main concern is avoiding concurrency.  The program is designed to be
able to do that in a single-machine setup but if you go introducing other
"clone" machines there is a greater chance of breaking things.  The
software isn't really setup to do what you are thinking - its designed to
be a persistent server that would exist independent of any particular
instance of your application and to which your application would connect
over jdbc/libpq.  So its up to you to ensure that you set things up to
conform to its runtime expectations.

David J.
​

Re: [GENERAL] ENABLE ROW LEVEL SECURITY cause huge produce of checkpoints

[Michael Paquier]

On Wed, Nov 2, 2016 at 12:09 AM,   wrote:
> we tried new feature RLS - tested on postgres 9.5.3 / CentOS6. When we turn
> on ENABLE RLS + FORCE RLS on normal workload cause huge produce checkpoints
> (about 30x or more), our disk partition for xlog was full and log shipping
> to replica maybe delayed removing old checkpoints. Have anybody same
> experiences after turn on RLS? Looks like more buffers set as dirty.  Yes,
> we can provide more space for xlog, but it will take much more space for
> xlog backups. We do not know if it's worth it. We had log_checkpoints ON and
> I send log as attachment (RLS Turn ON at 13:26).

Interesting, I don't recall RLS generating a burst in activity. The
first heavier checkpoints happen 20 minutes after enabling RLS and
those are triggered by time. Then things cool down and 1 hour later
comes the real deal with a set of checkpoints triggered by volume. It
is difficult though to draw a conclusion without more idea about your
load, the WAL record generated, etc.
-- 
Michael


-- 
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] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[Jeff Janes]

On Tue, Nov 8, 2016 at 12:27 PM, otar shavadze  wrote:

>
> p.s. In "pg_stats" really many values (long lists in "most_common_vals",
> "most_common_freqs") and in another columns
> Which one columns should I show you? All?
>

most_common_elems.  Is it empty, or is it not empty?  If not empty, does it
contain the specific values you used in your queries?

Cheers,

Jeff

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

[Tom Lane]

I wrote:
> Seems like your problem here is that the planner has no idea about the
> selectivity of this condition --- if it did, I think it would have
> made the right choice, because it would have made a much higher estimate
> for the cost of the indexscan.

> AFAICT, Postgres 9.5 does make a reasonably correct guess when given
> up-to-date stats.  I speculate that you need to ANALYZE this table.

Hmmm ... actually, I wonder if maybe '@>' here is the contrib/intarray
operator not the core operator?  The intarray operator didn't get plugged
into any real estimation logic until 9.6.

regards, tom lane


-- 
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] Linux equivalent library for "postgres.lib" from Windows

[Albe Laurenz]

John R Pierce wrote:
>> I am new to the product and in windows “postgres.lib” provides certain 
>> functions which we are
>> using in windows for creating extensions.
>> 
>> Now I am porting the project to Linux and there no straight library with 
>> this name in Linux
>> binaries packages.
>> 
>> Can someone please advise the equivalent library for postgres.lib in Linux?
> 
> I am not sure what this postgres.lib is, what are the functions you're using ?

With MSVC, you have to link with the mylibrary.lib file if you want to use
functions from the shared library mylibrary.dll.
This is not necessary on Linux, where references to a shared library are 
resolved
at load time.

So the answer to the original question is that there is no replacement
for postgres.lib on Linux because you don't need it to link with PostgreSQL.
It is enough to #include the required PostgreSQL headers during compilation.

Some information on how to link on Linux can be found here:
https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
But as others have remarked, using PGXS is much better than doing
it by hand.

Yours,
Laurenz Albe

-- 
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] Surviving connections after internet problem

[Albe Laurenz]

Durumdara wrote:
>> You can use pg_terminate_backend to kill a database session.
>>
>> Setting the keepalive options in postgresql.conf can make PostgreSQL
>> discover dead connections more quickly.
> 
> The server is licenced, so we can't access the conf file now.
> We will report this to the provider.

You cannot use ALTER SYSTEM from SQL either?

> For that moment could we set these parameters from clients after the 
> connection established?
> 
> For example:
> 
> set param bla = nnn?

Yes, that should work, e.g.:

SET tcp_keepalives_idle = 600;

Yours,
Laurenz Albe

-- 
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] Surviving connections after internet problem

[Durumdara]

Dear Laurenz!


2016-11-07 16:06 GMT+01:00 Albe Laurenz :

> Durumdara wrote:
> > Linux server, 9.4 PG, Windows clients far-far away.
> > May we must limit these parameters in clients after the starting of the
> connection?
>
> Don't bother about the clients, just see that the backends go away on the
> server.
>
> You can use pg_terminate_backend to kill a database session.
>
> Setting the keepalive options in postgresql.conf can make PostgreSQL
> discover dead connections more quickly.
>

The server is licenced, so we can't access the conf file now.
We will report this to the provider.

For that moment could we set these parameters from clients after the
connection established?

For example:

set param bla = nnn?

Thank you!

Regards
   dd