Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Hello Rob, Thanks for your response. [RS] I really do not understand "why" you need the SQLSTATE code after executing a "BEGIN" so as to go into transaction state. AFAIK you can only retrieve the SQLSTATE error code when an error actually occurs. So, if your query statement was successful,

Re: [GENERAL] checkpoints anatomy

http://dba.stackexchange.com/questions/61822/what-happens-in-postgresql-checkpoint and the now classic : http://www.depesz.com/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-checkpoint_timeout-checkpoint_warning/ On 12/10/2015 04:39, Richardson Hinestroza wrote:

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Victor Blomqvist wrote: [race condition causes errors due to stale plans immediately after ALTER TABLE DROP] > Note that these errors most of the time only happens very briefly at the same > time as the ALTER is > run. When I did some experiments today the server in total had around 3k > req/s

Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Hello Daniel, Thanks for your response. Ahhh -- so after a successful query, the PHP program must keep executing pg_get_result($cnx) until a NULL result is obtained !! And ONLY THEN does transaction status transition from PGSQL_TRANSACTION_ACTIVE to PGSQL_TRANSACTION_INTRANS. OK -- makes

Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Steve Petrie, P.Eng. wrote: > And yes, I am using pg_send_query(...) to execute the BEGIN; command, so as > to get a result resource returned. (Please see my forthcoming emailed > response to Adrian Klaver, wherein I provide the PHP source code that Adrian > requests.) After

Re: [GENERAL] BDR workers exiting?

On 10/12/15 10:14 AM, Jim Nasby wrote: On 10/12/15 9:37 AM, Steve Pribyl wrote: I am loading up a 60G database into BDR database and these "ERRORS" are in my logs. Is not normal behavior or is something going bad. 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41

Re: [GENERAL] BDR workers exiting?

Yup, there is a disconnect on other side. This disconnect is preceded by this. ERROR,XX000,"invalid memory alloc request size 1073741824","slot ""bdr_16494_6204748238611542317_1_16494__"", output plugin ""bdr"", in the change callback, associated LSN 2/FD250E48""bdr

Re: [GENERAL] Merge join vs merge semi join against primary key

Hello, I'm seeing some inexplicable (to me) behavior in PostgreSQL 9.2. I checked the archives, but I still can't explain it. Apologies if I missed something. 1. When I join two tables with "WHERE id IN (...)" versus with an explicit join, and the join column for the inner table is a primary

Re: [GENERAL] Merge join vs merge semi join against primary key

From: Sean Rhea [mailto:sean.c.r...@gmail.com] Sent: Friday, October 09, 2015 4:30 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Merge join vs merge semi join against primary key It does the merge (not-semi) join: production=> explain

Re: [GENERAL] BDR workers exiting?

On 2015-10-12 14:37:07 +, Steve Pribyl wrote: > I am loading up a 60G database into BDR database and these "ERRORS" are in my > logs. Is not normal behavior or is something going bad. > > 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 >

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Hi, On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: > Typically I have user defined functions for all operations, and my table > and functions follow this pattern: > > CREATE TABLE users ( > id integer PRIMARY KEY, > name varchar NOT NULL, > to_be_removed integer NOT NULL > ); > >

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On 10/12/2015 05:29 AM, Andres Freund wrote: Hi, On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: Typically I have user defined functions for all operations, and my table and functions follow this pattern: CREATE TABLE users ( id integer PRIMARY KEY, name varchar NOT NULL,

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On 2015-10-12 08:07:54 +, Albe Laurenz wrote: > Victor Blomqvist wrote: > [race condition causes errors due to stale plans immediately after ALTER > TABLE DROP] > > Note that these errors most of the time only happens very briefly at the > > same time as the ALTER is > > run. When I did some

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On 2015-10-12 06:42:52 -0700, Adrian Klaver wrote: > >My guess is that the problem here is that table level locking prevents > >modification of the "users" type when the table is used, but there's no > >locking preventing the columns to be dropped while the function is > >used. So what happens is

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Andres Freund writes: > On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: >> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS >> $$ >> BEGIN >> RETURN QUERY SELECT * FROM users WHERE id = id_; >> END; >> $$ LANGUAGE plpgsql; > My guess is that the problem

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On 10/12/2015 06:53 AM, Tom Lane wrote: Andres Freund writes: On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END; $$ LANGUAGE plpgsql; My

[GENERAL] BDR workers exiting?

I am loading up a 60G database into BDR database and these "ERRORS" are in my logs. Is not normal behavior or is something going bad. 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 CDT,5/0,0,ERROR,XX000,"data stream ended","bdr

[GENERAL] Left Join with Limit 1

Hello, I am trying to extract ip addresses from golite by joining two tables as posted below. Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds. Is there a way to use a limit in the join?

Re: [GENERAL] Left Join with Limit 1

On 10/12/15 10:03 AM, Alex Magnum wrote: Is there a way to use a limit in the join? SELECT ... FROM table_a a LEFT JOIN ( SELECT ... FROM table_b WHERE ... LIMIT 1) b ON a.blah = b.blah -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data

Re: [GENERAL] Left Join with Limit 1

Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds. Is there a way to use a limit in the join? This sounds like the real issue is a missing/incorrect index, but if you're on 9.4+ you can

Re: [GENERAL] BDR workers exiting?

On 10/12/15 9:37 AM, Steve Pribyl wrote: I am loading up a 60G database into BDR database and these "ERRORS" are in my logs. Is not normal behavior or is something going bad. 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 CDT,5/0,0,ERROR,XX000,"data stream

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

On 10/6/15 12:18 PM, Olivier Dony wrote: We would happily skip the micro-transactions (as a perf workaround) if there was a way to detect this situation, but we couldn't find a way to do that in 9.3. <9.3 we used SELECT FOR UPDATE NOWAIT to guard similar cases. If there is any way I could help

[GENERAL] Constraint allowing value up to 2 times but no more than 2 times

Greeetings. I'm trying to come up with a way to allow one column to have the same value up to two times, but no more than two times. I attempted adding a constraint to check the count of the value in the field - count (trsqqq) <=2 but aggregate functions are not allowed in constraints. Is there

Re: [GENERAL] BDR workers exiting?

The process used to created this Start with clean db Create host A database with bdr Join host B with dbr Load database using psql < file.sql I was able to get it work if I do the following. Start with clean db Create host A database Load data on host A Join host A to bdr. Join host b to bdr.

Re: [GENERAL] Effecient time ranges in 9.4/9.5?

On 10/4/15 6:18 AM, Karsten Hilbert wrote: check whether both TZs are equal, if so ignore them else convert both operands to UTC, do "time - time", return result AT TIME ZONE UTC, document that UTC is returned, such

[GENERAL] Pattern match against array elements?

Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have a grouped query that, among other things, returns an array of values, like: SELECT lognum, array_agg(flightnum) as flightnums FROM logs GROUP BY lognum; Where

Re: [GENERAL] Constraint allowing value up to 2 times but no more than 2 times

On 10/12/15 12:04 PM, Dara Unglaube wrote: Greeetings. I'm trying to come up with a way to allow one column to have the same value up to two times, but no more than two times. I attempted adding a constraint to check the count of the value in the field - count (trsqqq) <=2 but aggregate

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster wrote: > Is there any way to do a pattern match against the elements of an array in > postgresql (9.4 if the version makes a difference)? I have a grouped query > that, among other things, returns an array of values, like:

Re: [GENERAL] Effecient time ranges in 9.4/9.5?

On Mon, Oct 12, 2015 at 12:14:10PM -0500, Jim Nasby wrote: > On 10/4/15 6:18 AM, Karsten Hilbert wrote: > > check whether both TZs are equal, > > if so > > ignore them > > else > > convert both operands to UTC, > > do "time - time", > > return result AT

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster wrote: > Is there any way to do a pattern match against the elements of an array in > postgresql (9.4 if the version makes a difference)? I have a grouped query > that, among other things, returns an array of values, like:

Re: [GENERAL] Pattern match against array elements?

Jeff Janes writes: > On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster > wrote: >> My first thought was to do something like this: >> >> SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs >> GROUP BY lognum) s1 WHERE '8%' like

Re: [GENERAL] Constraint allowing value up to 2 times but no more than 2 times

On 10/12/15 1:41 PM, Dara Unglaube wrote: I created a view with the column of interest and a column of the count. How do I do a check constraint on a view or do it all at once as a subquery? Could you provide an example of how to create? Please don't top-post. And do include the mailing list

Re: [GENERAL] Pattern match against array elements?

In the general case, that might work, however in my actually use case the inner SQL query (and underlying table structure) is rather more complicated, making it so I really want the WHERE clause in an outside query, as in my (non-functional) example. Just to clarify, the actual structure of the

Re: [GENERAL] Pattern match against array elements?

On Oct 12, 2015, at 10:39 AM, Tom Lane wrote: > > Jeff Janes writes: >> On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster >> wrote: >>> My first thought was to do something like this: >>> >>> SELECT * FROM (SELECT

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 11:39 AM, Tom Lane wrote: > Jeff Janes writes: > > On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster > > > wrote: > >> My first thought was to do something like this: > >> > >> SELECT * FROM (SELECT

Re: [GENERAL] Understanding "seq scans"

On Monday, October 12, 2015 2:52 PM, Lele Gaifax wrote: > I'm doing some experiments to find the better layout for > reimplementing an existing db (MySQL cough!) with PostgreSQL > 9.4+. > > I noticed a strange plan coming out from a simple query joining > two tables, both

[GENERAL] Understanding "seq scans"

Hi all, I'm doing some experiments to find the better layout for reimplementing an existing db (MySQL cough!) with PostgreSQL 9.4+. I noticed a strange plan coming out from a simple query joining two tables, both containing 10Mrecs (and both ANALYZEd): l10ntest=# \d master;

Re: [GENERAL] Understanding "seq scans"

On 10/12/2015 12:06 PM, Lele Gaifax wrote: Hi all, I'm doing some experiments to find the better layout for reimplementing an existing db (MySQL cough!) with PostgreSQL 9.4+. I noticed a strange plan coming out from a simple query joining two tables, both containing 10Mrecs (and both

Re: [GENERAL] Understanding "seq scans"

On Monday, October 12, 2015, Lele Gaifax wrote: > Adrian Klaver > writes: > > > Off hand I would say it is because of this --> count(m.num). Try > count(l.num) instead and see > > what happens. As your queries above show they are the

Re: [GENERAL] Merge join vs merge semi join against primary key

On 10 October 2015 at 08:52, Sean Rhea wrote: > > 1. When I join two tables with "WHERE id IN (...)" versus with an explicit > join, and the join column for the inner table is a primary key, I would > expect > the same behavior in both cases, but the optimizer is choosing

Re: [GENERAL] Pattern match against array elements?

---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS

Re: [GENERAL] Understanding "seq scans"

Adrian Klaver writes: > Off hand I would say it is because of this --> count(m.num). Try count(l.num) > instead and see > what happens. As your queries above show they are the same number. No, that's another thing I already tried tweaking and should have mentioned.

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Do you have some advice how to design my functions to work around this problem? If I understand your conversation correct the problem is returning the rowtype users from the function. If so, I can think of two workarounds (both quite inconvenient and complex): 1. Use RETURNS TABLE(...) together

Re: [GENERAL] BDR: no free replication state could be found

On 10 October 2015 at 02:53, Selim Tuvi wrote: > node: deliver_sing (the problem node): > > postgres=# SELECT * FROM pg_catalog.pg_replication_identifier; > riident | riname > -+ >1 |

Re: [GENERAL] Understanding "seq scans"

Lele Gaifax wrote: > Hi all, > > I'm doing some experiments to find the better layout for reimplementing > an existing db (MySQL cough!) with PostgreSQL 9.4+. > > I noticed a strange plan coming out from a simple query joining two tables, > both containing 10Mrecs (and both ANALYZEd): >

Re: [GENERAL] BDR workers exiting?

BDR is currently memory-limited for extremely large transactions. At a guess, I'd say one of your big tables is large enough that the logical decoding facility BDR uses can't keep track of the transaction properly. There's no hard limit, it depends on details of the transaction and a number of