Re: [GENERAL] Check constraints and function volatility categories

On 02/01/2016 12:36 PM, David G. Johnston wrote: On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster >wrote: On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston > wrote: On

[GENERAL] Check constraints and function volatility categories

Hello, I'm discovering that I need to write quite a few functions for use strictly w/ check constraints and I'm wondering if declaring the volatility category for said functions will affect their behavior when invoked by PostgreSQL's check constraint mechanism. Essentially what I'm trying to

Re: [GENERAL] strange sql behavior

Came a little late to the thread, see many comments inline below: On Mon, 1 Feb 2016 13:16:13 -0600 Yu Nie wrote: > Thanks a lot for your reply. I ran the query you suggested and here are > the results > > Large table:

Re: [GENERAL] Check constraints and function volatility categories

On 02/01/2016 11:17 AM, Dane Foster wrote: Hello, I'm discovering that I need to write quite a few functions for use strictly w/ check constraints and I'm wondering if declaring the volatility category for said functions will affect their behavior when invoked by PostgreSQL's check constraint

Re: [GENERAL] strange sql behavior

And were _your_ queries run on the same day at the same time within a few seconds of each other? Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone Original message From: Yu Nie Date: 2/1/2016 15:01 (GMT-05:00) To: melvin6925

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster wrote: > > On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver > > wrote: >> >>> On 02/01/2016 11:17 AM, Dane

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster wrote: > > On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver > wrote: > >> On 02/01/2016 11:17 AM, Dane Foster wrote: >> >>> Hello, >>> >>> I'm discovering that I need to write quite a few functions for use

Re: [GENERAL] strange sql behavior

Melvin, Please see attached for the requests results. I ran two queries (each with a different taxiid that is next to each other) for each table. Note that for the large table one is much faster than the other because the shared cache was used for the second query. This does not work however

Re: [GENERAL] Check constraints and function volatility categories

On 02/01/2016 12:52 PM, Dane Foster wrote: On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver > wrote: As an example of where this leads see: http://www.postgresql.org/message-id/7224.1452275...@sss.pgh.pa.us ​Thanks for the

Re: [GENERAL] strange sql behavior

Hi Melvin, Thanks a lot for your reply. I ran the query you suggested and here are the results Large table: "public";"data2011_01";784236885;0;0;"";"";"";"2016-01-19 17:31:08-06";156847423 Small table: "public";"data2013_01w";300786444;0;0;"";"";"2016-02-01 08:57:24-06";"2016-02-01

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver wrote: > On 02/01/2016 12:52 PM, Dane Foster wrote: > >> On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver > > wrote: >> >> > >> As an example of where this

Re: [GENERAL] strange sql behavior

One thing to look at is the last time both tables were vacuumed/analyzed. SELECT n.nspname, s.relname, c.reltuples::bigint, n_tup_ins, n_tup_upd, n_tup_del, date_trunc('second', last_vacuum) as last_vacuum, date_trunc('second', last_autovacuum) as

Re: [GENERAL] strange sql behavior

Thanks Bill.Also, it's very important to include the headers with the queries! Marco,There is no top secret information that is requested, so please do not edit the output. Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone Original message From: Bill Moran

Re: [GENERAL] strange sql behavior

Thanks, Bill and Melvin! Just some quick note/answers before I absorb all the information provided by Bill. 1. I don't expect many users running queries against the tables, especially for the small table - since I just created it this morning, and nobody know about it except myself. 2. The

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver wrote: > On 02/01/2016 11:17 AM, Dane Foster wrote: > >> Hello, >> >> I'm discovering that I need to write quite a few functions for use >> strictly w/ check constraints and I'm wondering if declaring the >> volatility

Re: [GENERAL] strange sql behavior

On 02/01/2016 10:35 AM, Yu Nie wrote: Hi there, Recently I am working with a large amount of taxis GIS data and had encountered some weird performance issues. I am hoping someone in this community can help me figure it out. The taxi data were loaded in 5 minute block into a table. I have two

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver wrote: > On 02/01/2016 12:36 PM, David G. Johnston wrote: > >> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster > >wrote: >> >> >> On Mon, Feb 1, 2016 at 2:56 PM, David G.

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver wrote: > On 02/01/2016 11:17 AM, Dane Foster wrote: > >> Hello, >> >> I'm discovering that I need to write quite a few functions for use >> strictly w/ check constraints and I'm wondering if declaring the >> volatility

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver > wrote: > >> On 02/01/2016 11:17 AM, Dane Foster wrote: >> >>> Hello, >>> >>> I'm discovering that I need to write quite a few

Re: [GENERAL] strange sql behavior

Hi Klaver, Thanks. 1. I don't see order by time makes a difference - in fact, the "analyze" seems to indicate the sorting is faster for the small table because it uses less memory. 2. No, the large table has not been clustered. Both tables were created exactly the same way, loading 5-minute

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 01, 2016 at 12:41:30PM -0800, Adrian Klaver wrote: >> While you've managed to fool the system by wrapping your query into a >> function you've violated the documented restrictions and so any breakage >> is on you - not the system. > > As an example of where this leads see: > >

[GENERAL] BDR error trying to replay a invalid statement

I noticed that the BDR replication continually trying to replay a ddl statement that has a syntax error. Is there anything that can be done to skip this statement or do I need to rebuild the replicated node? Here's what I see in the logs: t=2016-02-01 13:02:27 PST d= h= p=21795 a=LOCATION:

Re: [GENERAL] Check constraints and function volatility categories

On 02/01/2016 01:23 PM, David G. Johnston wrote: On Mon, Feb 1, 2016 at 2:11 PM, Adrian Klaver >wrote: On 02/01/2016 12:52 PM, Dane Foster wrote: On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver

Re: [GENERAL] Is it possible to select index values ?

On Mon, Feb 1, 2016 at 4:29 PM, Jonathan Vanasco wrote: > Is it possible to select index values ? > > I haven't found any documentation that says "No", but I haven't found > anything that says "Yes" either. > > The reason - I have a few function indexes that are working as

[GENERAL] Is it possible to select index values ?

Is it possible to select index values ? I haven't found any documentation that says "No", but I haven't found anything that says "Yes" either. The reason - I have a few function indexes that are working as partial indexes. I'd like to run some analytics on them (to determine uniqueness of

Re: [GENERAL] strange sql behavior

At this point I can see no possible explanation why this would occur. Just a thought though, is it possible that data2011_01 was clustered on the index at some point but data2013_01w has not been clustered? If you cluster data2013_01w on the index, does the performance change? On Mon, Feb 1,

Re: [GENERAL] pg_dump - ERROR - PostgreSQL 9.2

[SOLVED] Just to let you guys know.. I've solved this issue changing the max_standby_streaming_delay on my slave to "-1". Cheers Lucas Possamai - kinghost.co.nz - DigitalOcean

[GENERAL] pg_dump - ERROR - PostgreSQL 9.2

Hi all, *masterdb01 -> slave01 -> slave02 -> slave03* *testmaster01 (Full copy from masterdb01)* I'm trying to refresh my main DB, by running this command on my test server: ssh postgres@slave02 "/usr/pgsql-9.2/bin/pg_dump > --exclude-table-data='junk.*' --format=custom master_db_name" | >

Re: [GENERAL] PL/pgSQL debugger

Dane Foster wrote: > I googled "plpgsql debugger" and eventually ended up at > http://pgfoundry.org/projects/edb-debugger/, > where the latest release is almost 8 years old. I am aware that this is not > an EDB forum but given > that I sent them an e-mail at 9AM EST and I haven't received a

Re: [GENERAL] Is PRIMARY KEY the same as UNIQUE NOT NULL?

On 31 January 2016 at 19:53, David G. Johnston wrote: > A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows [snip] I would just remove the whole paragraph. A primary key does what it does, a unique constraint does what it does. I'm not really

Re: [GENERAL] BDR replication on Postgresql 9.5.0

On Mon, Feb 1, 2016 at 12:24 AM, John R Pierce wrote: > On 1/29/2016 3:00 AM, Kaushal Shriyan wrote: > >> >> Do i need to install any BDR specific package to enable it in postgresql >> 9.5 version. While reading >> http://bdr-project.org/docs/0.9.0/install-requirements.html

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:36 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster wrote: > >> >> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Mon, Feb 1, 2016 at 12:41

Re: [GENERAL] strange sql behavior

Hi Melvin, Many thanks for your help. I just deleted the small table,along with another large unused table (with about 1.2 billion rows) in the same database, It frees up about 100 Gb space on the disk. I am reloading the small table again and it will take a few more hours to complete. Once

[GENERAL] strange sql behavior

Hi there, Recently I am working with a large amount of taxis GIS data and had encountered some weird performance issues. I am hoping someone in this community can help me figure it out. The taxi data were loaded in 5 minute block into a table. I have two separate such tables, one stores a