Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

2016-12-30 Thread Amitabh Kant
On Fri, Dec 30, 2016 at 12:06 PM, ajmcello wrote: > Reducing worker mem shaved about 12 minutes off the query time.. Thanks > for the suggestion. I lowered it to 10MB instead of 100MB > > [SNIP] > > >>> [postgresql.conf] > >>> max_connections = 10 > >>>

Re: [GENERAL] Default column value [ANSWERED]

2016-12-30 Thread Adrian Klaver
On 12/30/2016 08:51 AM, Rich Shepard wrote: On Fri, 30 Dec 2016, David G. Johnston wrote: "The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN

Re: [GENERAL] Default column value

2016-12-30 Thread Joshua D. Drake
On 12/30/2016 06:46 AM, Adrian Klaver wrote: On 12/30/2016 06:38 AM, Rich Shepard wrote: test=> \d default_test Table "public.default_test" Column | Type| Modifiers +---+--- id | integer | fld_1 | character varying |

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread Karsten Hilbert
On Fri, Dec 30, 2016 at 10:23:44AM -0500, Stephen Frost wrote: > One area that isn't fully addressed with the PG auth model today is > partial access to a certain column. Consider a table where you want > users to have access to all of the rows and all of the columns *except* > for column X for

Re: [GENERAL] Default column value [ANSWERED]

2016-12-30 Thread Rich Shepard
On Fri, 30 Dec 2016, David G. Johnston wrote: "The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed." NULL == "UNKNOWN" David, I forgot

Re: [GENERAL] Default column value

2016-12-30 Thread David G. Johnston
On Fri, Dec 30, 2016 at 9:19 AM, Rich Shepard wrote: > On Fri, 30 Dec 2016, Adrian Klaver wrote: > > DEFAULT is what is the column is set to if the user does not specify a >> value. As shown above a user can supply a NULL value. To guard against >> that the NOT NULL

Re: [GENERAL] Default column value

2016-12-30 Thread Rich Shepard
On Fri, 30 Dec 2016, Adrian Klaver wrote: DEFAULT is what is the column is set to if the user does not specify a value. As shown above a user can supply a NULL value. To guard against that the NOT NULL constraint is required. One more case I'd appreciate being clarified: when the column's

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread Melvin Davidson
On Fri, Dec 30, 2016 at 10:23 AM, Stephen Frost wrote: > Greetings, > > * Guyren Howe (guy...@gmail.com) wrote: > > it occurs to me to wonder whether it is practical to use PG’s own roles > and security model in lieu of using an application-level one. > > The short answer is

Re: [GENERAL] Book or other resource on Postgres-local code?

2016-12-30 Thread Melvin Davidson
On Fri, Dec 30, 2016 at 2:50 AM, Pavel Stehule wrote: > > > 2016-12-30 8:04 GMT+01:00 Guyren Howe : > >> >> > On Dec 29, 2016, at 23:01 , Regina Obe wrote: >> > >> > >> >> As an aside from my last question about my LYDB effort: >> > >>

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread Stephen Frost
Greetings, * Guyren Howe (guy...@gmail.com) wrote: > it occurs to me to wonder whether it is practical to use PG’s own roles and > security model in lieu of using an application-level one. The short answer is yes. > It seems that the role system in PG is sufficient for most general purposes.

Re: [GENERAL] Default column value

2016-12-30 Thread Rich Shepard
On Fri, 30 Dec 2016, Adrian Klaver wrote: DEFAULT is what is the column is set to if the user does not specify a value. As shown above a user can supply a NULL value. To guard against that the NOT NULL constraint is required. Thanks, Adrian. This was not clear to me when I read the manual.

Re: [GENERAL] Default column value

2016-12-30 Thread Rich Shepard
On Fri, 30 Dec 2016, Tom Lane wrote: No, because you can explicitly insert a null. DEFAULT only controls what happens when you omit the column in an INSERT command. tom, Thanks for clarifying. I did not pick this up from reading the manual and knew that NULL could be an explicitly-defined

Re: [GENERAL] Default column value

2016-12-30 Thread Adrian Klaver
On 12/30/2016 06:38 AM, Rich Shepard wrote: > Reading the 9.6 docs suggests an answer to my question, but does not > explicitly answer it, so I ask here. > > If a column has a default value specified does this mean the column > cannot > contain a NULL value? In other words, is DEFAULT NOT

Re: [GENERAL] Default column value

2016-12-30 Thread Tom Lane
Rich Shepard writes: >If a column has a default value specified does this mean the column cannot > contain a NULL value? In other words, is DEFAULT NOT NULL > redundant? No, because you can explicitly insert a null. DEFAULT only controls what happens when you omit

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread bto...@computer.org
- Original Message - > From: "Alban Hertroys" > To: "Guyren Howe" > Cc: "pgsql-general" > Sent: Friday, December 30, 2016 6:23:27 AM > Subject: Re: [GENERAL] LYDB: Feasible to use PG roles instead of >

Re: [GENERAL] Default column value

2016-12-30 Thread Adrian Klaver
On 12/30/2016 06:38 AM, Rich Shepard wrote: Reading the 9.6 docs suggests an answer to my question, but does not explicitly answer it, so I ask here. If a column has a default value specified does this mean the column cannot contain a NULL value? In other words, is DEFAULT NOT NULL

[GENERAL] Default column value

2016-12-30 Thread Rich Shepard
Reading the 9.6 docs suggests an answer to my question, but does not explicitly answer it, so I ask here. If a column has a default value specified does this mean the column cannot contain a NULL value? In other words, is DEFAULT NOT NULL redundant? TIA, Rich -- Sent via pgsql-general

Re: [GENERAL] Performance PLV8 vs PLPGSQL

2016-12-30 Thread Michael Sheaver
The reason that you cannot use git for that is that it was never intended for live data and stuff that resides on a database. That said, I agree with you that all table DDLs and stored procedures should be kept under version control. And in fact I do keep them under VC on my database. How do I

Re: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Francisco Olarte
Job: On Fri, Dec 30, 2016 at 1:01 PM, Alban Hertroys wrote: >> On 30 Dec 2016, at 11:42, Job wrote: ... >> The index applied on the timestamp field is a btree("timestamp") ... >> select domain, sum(accessi) as c_count from TABLE where action='1'

R: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Job
Hi Alban, I was wrong: i have only one column: tsrarnge. Which index can i create to use this statement fastly: ... AND timestamp <@ '[2016-12-27 00:00:00, 2016-12-31 00:00:00)'::tsrange Thank you again! /F -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

R: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Job
Dear Alban, Regarding: >>... AND timestamp BETWEEN '2016-12-27 00:00:00'::timestamp with time zone AND >>'2016-12-30 23:59:59'::timestamp with time zone ... I think it is a very good approach, and i would like to try. My table has got two different field for "starting" and "ending" timestamp

Re: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Alban Hertroys
> On 30 Dec 2016, at 11:42, Job wrote: > >>> And, basically, if you need help with some queries you could try >>> posting them whole, even redacted, along the table defs, this way >>> perople can see the problem and not invent one based on a partial >>> description >

Re: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Charles Clavadetscher
Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Job > Sent: Freitag, 30. Dezember 2016 11:42 > To: Francisco Olarte > Cc: David G. Johnston ; >

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

2016-12-30 Thread Alban Hertroys
> On 30 Dec 2016, at 4:19, Guyren Howe wrote: > > Further to my attempts to enlighten application developers about what they > might better do in the database: > > https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb > > it occurs to me to wonder

R: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Job
>>And, basically, if you need help with some queries you could try >>posting them whole, even redacted, along the table defs, this way >>perople can see the problem and not invent one based on a partial >>description Thank you very much, very kind from you. The index applied on the timestamp

Re: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Francisco Olarte
On Fri, Dec 30, 2016 at 11:00 AM, Job wrote: > I tried to create a GIST/GIN index on a timestamp without time zone field > but it does not work. > Are there alternatives index types or timezone could speed query up? Remember a timestamp is just a real number ( a point

R: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Job
I tried to create a GIST/GIN index on a timestamp without time zone field but it does not work. Are there alternatives index types or timezone could speed query up? Thank you /F Da: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] per

R: [GENERAL] Special index for "like"-based query

2016-12-30 Thread Job
>>GIST​ >>https://www.postgresql.org/docs/9.6/static/pgtrgm.html >>​https://www.postgresql.org/docs/9.6/static/btree-gist.html I tried with a GIST-like index and queries improves a lot, thank you! Furthermore, this type of index is also suitable for a timestamp query, where we can mix date and