Re: [GENERAL] Segmentation fault with core dump

[Inoue, Hiroshi]

(2013/06/12 0:03), Tom Lane wrote:
> Hiroshi Inoue  writes:
>> It's also better to fix the crash at backend side.
> 
> Yeah, definitely.  Do we have a self-contained test case for this?

Unfortunately no. I'm testing with a modified psqlodbc driver.
The simplest way may be as follows.

1. Implement an API function like PQexecPortal(portal name, count)
   which sends only an execute message.

2. Call the APIs as follows.

   // Connect to a database.
   conn = PQ();

   //
   // Not begin a transaction for simplicity.
   //
   // The following operation defines a holdable portal whose
   // portal name = cursor_name.
   PQexec(conn, "declare cursor_name cursor with hold for select ");

   // At this point the holdable cursor becomes a held portal whose
   // resowner is reset to NULL. The following function call would
   // crash the backend.
   PQexecPortal(conn, cursor_name, 1);



-- 
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] Really poor gist index performance with tstzrange types

[Sergey Konoplev]

On Tue, Jun 11, 2013 at 5:13 PM, Tom Lane  wrote:
> Joe Van Dyk  writes:
>> Am I doing something silly? Or is the row-estimation for gist indexes not
>> even close in this case?
>
> 9.2 didn't have any logic for estimating range << conditions.  I see
> reasonable estimates for this case in HEAD, though, presumably thanks
> to work by Alexander Korotkov.

I just wanted to add that rewriting the << condition the way shown
below might partially solve the problem.

where
tstzrange(now(), now(), '[]') < duration and
not tstzrange(now(), now(), '[]') && duration

And here is the result.

[local]:5432 grayhemp@grayhemp=#
explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') << duration;
 QUERY PLAN

 Aggregate  (cost=2720.17..2720.18 rows=1 width=0) (actual
time=109.161..109.163 rows=1 loops=1)
   ->  Seq Scan on f  (cost=0.00..2636.84 rows=1 width=0) (actual
time=109.148..109.148 rows=0 loops=1)
 Filter: (tstzrange(now(), now(), '[]'::text) << duration)
 Rows Removed by Filter: 10
 Total runtime: 109.210 ms
(5 rows)

Time: 109.837 ms
[local]:5432 grayhemp@grayhemp=#
explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') < duration
and not tstzrange(now(), now(), '[]') && duration;
 QUERY
PLAN

 Aggregate  (cost=2646.39..2646.40 rows=1 width=0) (actual
time=0.042..0.043 rows=1 loops=1)
   ->  Bitmap Heap Scan on f  (cost=926.55..2563.48 rows=33164
width=0) (actual time=0.035..0.035 rows=0 loops=1)
 Recheck Cond: (tstzrange(now(), now(), '[]'::text) < duration)
 Filter: (NOT (tstzrange(now(), now(), '[]'::text) && duration))
 ->  Bitmap Index Scan on f_duration_idx1  (cost=0.00..918.26
rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1)
   Index Cond: (tstzrange(now(), now(), '[]'::text) < duration)
 Total runtime: 0.098 ms
(7 rows)

Time: 0.801 ms

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.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] Really poor gist index performance with tstzrange types

[Tom Lane]

Joe Van Dyk  writes:
> Am I doing something silly? Or is the row-estimation for gist indexes not
> even close in this case?

9.2 didn't have any logic for estimating range << conditions.  I see
reasonable estimates for this case in HEAD, though, presumably thanks
to work by Alexander Korotkov.

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

[GENERAL] Really poor gist index performance with tstzrange types

[Joe Van Dyk]

Am I doing something silly? Or is the row-estimation for gist indexes not
even close in this case?

https://gist.github.com/joevandyk/503cc3d836ee5d101224/raw/c6fc53b2da06849d3d04effbd1c147fc36124245/gistfile1.txtor
code below:

-- This is not running inside a transaction.

drop table if exists f;

create table f (duration tstzrange);

insert into f
  select tstzrange(now() - '1 month'::interval, now() - '1
sec'::interval) from generate_series(1, 10);

create index on f using gist(duration);
analyze f;

select count(*) from f where tstzrange(now(), now(), '[]') << duration;
-- returns 0

explain analyze
select count(*) from f where tstzrange(now(), now(), '[]') << duration;

 Aggregate  (cost=2720.36..2720.37 rows=1 width=0) (actual
time=55.374..55.374 rows=1 loops=1)
   ->  Seq Scan on f  (cost=0.00..2637.02 rows=4 width=0) (actual
time=55.369..55.369 rows=0 loops=1)
 Filter: (tstzrange(now(), now(), '[]'::text) << duration)
 Rows Removed by Filter: 10
 Total runtime: 55.407 ms

[GENERAL] PostgreSQL Presentation at SELF 2013

[Don Parris]

Hi all,

I am a member of the Charlotte Linux User Group and did a presentation on
PostgreSQL at the SouthEast LinuxFest this past weekend.  Our LUG table was
right next to the PostgreSQL table this year, and I am guessing some of you
will find the presentation of some interest.  My discussion focused on
using the ltree module instead of adjacency lists, nested sets and path
enumeration to create and run queries against hierarchical category trees.

Here is a link to my slides (*.odp) on the ltree module:
http://dcparris.net/2013/06/11/self-2013-ltree-slides/

and to my general take on the SouthEast LinuxFest.
http://dcparris.net/2013/06/11/southeast-linuxfest-what-you-missed/

Anyway, here's hoping you'll enjoy this and find it useful as the few
attendees who sat in did.  I hope it helps in some small way.

Regards,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE

[GENERAL] casting tsrange to tstzrange doesn't seem to work?

[Joe Van Dyk]

# select tsrange(null)::tstzrange;
ERROR:  cannot cast type tsrange to tstzrange
LINE 1: select tsrange(null)::tstzrange;

Is this expected?

select null::timestamp::timestamptz;
works fine.

Re: [GENERAL] INSERT RETURNING with values other than inserted ones.

[David Johnston]

Aleksandr Furmanov wrote
> Thanks,
> However I am not just replicating data from 'a' to 'b', I provided
> simplified example, in reality table 'b' keeps data which are going to be
> merged into 'a', some rows will be updated, some added. There is some
> other work has to be done on 'b' before merging into 'a' and that work
> relies on 'id' from a.

Three options:

1) CREATE TABLE a (id serial, name text, source_id_from_table_b integer);
and during INSERT provide which record on B caused the record on A to be
created.

2) Determine what can be used as a true primary key on both A and B; forgo
the use of SERIAL

3) Do whatever it is you need to do one record at a time within a pl/pgsql
function and capture the ID from B and the post-insert ID from A into local
variables to generate a temporary mapping for doing your other work.

I'd suggest #1; simply move the mapping column from "B" to "A" and perform
the linking during the insert but your simplified example provide no context
to evaluate if this will actually meet your needs.  The main concern is that
this basically models a 1-to-1(optional) relationship between A and B; such
that every record in A must exist in B (assuming you disallow NULL) but B
can have records that do not exist in A.  Whether multiple records in A can
share the same "source" record in B is undefined by can be made explicit by
the choice of INDEX that you create (UNIQUE or NORMAL).

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/INSERT-RETURNING-with-values-other-than-inserted-ones-tp5758695p5758793.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Segmentation fault with core dump

[Tom Lane]

Hiroshi Inoue  writes:
> It's also better to fix the crash at backend side.

Yeah, definitely.  Do we have a self-contained test case for this?

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] Segmentation fault with core dump

[Hiroshi Inoue]

Hi,

(2013/05/09 1:39), Joshua Berry wrote:

| I'm using PG 9.1.9 with a client application using various versions of
the
| pgsqlODBC driver on Windows. Cursors are used heavily, as well as some
pretty
| heavy trigger queries on db writes which update several materialized
views.
|
| The server has 48GB RAM installed, PG is configured for 12GB shared
buffers,
| 8MB max_stack_depth, 32MB temp_buffers, and 2MB work_mem. Most of the
other
| settings are defaults.
|
| The server will seg fault from every few days to up to two weeks. Each
time
| one of the postgres server processes seg faults, the server gets
terminated by
| signal 11, restarts in recovery for up to 30 seconds, after which time it
| accepts connections as if nothing ever happened. Unfortunately all the
open
| cursors and connections are lost, so the client apps are left in a bad
state.
|
| Seg faults have also occurred with PG 8.4. ... I migrated the database
to a
| server running PG9.1 with the hopes that the problem would disappear,
but it
| has not. So now I'm starting to debug.
|
| # uname -a
| Linux [hostname] 2.6.32-358.2.1.el6.x86_64 #1 SMP Tue Mar 12 14:18:09
CDT 2013
| x86_64 x86_64 x86_64 GNU/Linux
| # cat /etc/redhat-release
| Scientific Linux release 6.3 (Carbon)
|
| # psql -U jberry
| psql (9.1.9)
| Type "help" for help.
|
| jberry=# select version();
|version
|
---
|  PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7
|  20120313 (Red Hat 4.4.7-3), 64-bit
| (1 row)

I've had another postmaster segfault on my production server. It appears
to be the same failure as the last one nearly a month ago, but I wanted
to post the gdb bt details in case it helps shed light on the issue.
Please let me know if anyone would like to drill into the dumped core
with greater detail. Both the OS and PG versions remain unchanged.

Kind Regards,
-Joshua


On Fri, Apr 12, 2013 at 6:12 AM, Andres Freund mailto:and...@2ndquadrant.com>> wrote:

On 2013-04-10 19:06:12 -0400, Tom Lane wrote:
 > I wrote:
 > > (Wanders away wondering just how much the regression tests exercise
 > > holdable cursors.)
 >
 > And the answer is they're not testing this code path at all,
because if
 > you do
 >   DECLARE c CURSOR WITH HOLD FOR ...
 >   FETCH ALL FROM c;
 > then the second query executes with a portal (and resource owner)
 > created to execute the FETCH command, not directly on the held
portal.
 >
 > After a little bit of thought I'm not sure it's even possible to
 > reproduce this problem with libpq, because it doesn't expose any
way to
 > issue a bare protocol Execute command against a pre-existing portal.
 > (I had thought psqlOBC went through libpq, but maybe it's playing
some
 > games here.)
 >
 > Anyway, I'm thinking the appropriate fix might be like this
 >
 > - CurrentResourceOwner = portal->resowner;
 > + if (portal->resowner)
 > + CurrentResourceOwner = portal->resowner;
 >
 > in several places in pquery.c; that is, keep using
 > TopTransactionResourceOwner if the portal doesn't have its own.
 >
 > A more general but probably much more invasive solution would be
to fake
 > up an intermediate portal when pulling data from a held portal, to
 > more closely approximate the explicit-FETCH case.

We could also allocate a new resowner for the duration of that
transaction. That would get reassigned to the transactions resowner in
PreCommit_Portals (after a slight change there).
That actually seems simple enough?


I made some changes to multi thread handling of psqlodbc driver.
It's also better to fix the crash at backend side.

I made 2 patches.
The 1st one temporarily changes CurrentResourceOwner to 
CurTransactionResourceOwner during catalog cache handling.

The 2nd one allocates a new resource owner for held portals.
Both fix the crash in my test case.

regards,
Hiroshi Inoue
diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index e87e675..258a4c2 100644
--- a/src/backend/access/common/printtup.c
+++ b/src/backend/access/common/printtup.c
@@ -238,6 +238,7 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
 {
 	int16	   *formats = myState->portal->formats;
 	int			i;
+	bool	resownerIsNull	= false;
 
 	/* get rid of any old data */
 	if (myState->myinfo)
@@ -252,6 +253,15 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
 	myState->myinfo = (PrinttupAttrInfo *)
 		palloc0(numAttrs * sizeof(PrinttupAttrInfo));
 
+	if (NULL == CurrentResourceOwner)
+	{
+		/*
+		 *	This occurs when processing execute messages
+		 *	for holdable cursors after commit.
+		 */
+		resownerIsNull = true;
+		CurrentResourceOwner = CurTr

Re: [GENERAL] databse version

[Philipp Kraus]

On 2013-06-11 00:42:59 +0200, John R Pierce said:


On 6/10/2013 12:52 AM, Philipp Kraus wrote:
I'm creating a database and I have got a table with a "version" field. 
Can I update on structure changes (DDL) like create / update table 
increment this field automatically?

I would like to create a versionizing for my database which counts the changes.
IMHO I need a trigger, which is run if a DDL statement is fired on the database


so if someone runs a script that does a half dozen create/alters, you 
want to bump your version that many times?


we handle our version control by not allowing anyone to make direct 
changes, instead all changes need to be made with .sql scripts, of 
which we maintain two sets, one to create a new schema version x.y, and 
the other to update x.y-1 to x.y.   and yes, part of these scripts 
stores the version in a configuration table of (key,value) pairs, like 
('version','x.y')


You're right. The database versionizing seems to be a problem, but I 
would like run a automatism for versionizing eg the commit hash on git. 
I have got a SQL script with the create calls
(alter will be create in the future). So our buildsystem get the head 
of the git repo and builds the application, the database sql script is 
also stored in the repo. In my case I would dump the database on the 
developer server, check if the repo script and the dump script are 
equal and if not, I increment the version number in dem dump script and 
push it into the repo.


On the release process the version counting is depend on the prior 
release. I think about a number like ..


Phil




--
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] Calculate a quotient for a count of boolean values (true or false)

[Vik Fearing]

Whoops, forgot to keep it on the list.


On 06/11/2013 11:51 AM, Alexander Farber wrote:
> Hello!
>
> In a PostgreSQL 8.4.13 why doesn't this please
> deliver a floating value (a quotient between 0 and 1):

You should upgrade to 8.4.17.

>  select
> id,
> count(nullif(nice, false)) - count(nullif(nice, true)) /
> count(nice) as rating
> from pref_rep where nice is not null
> group by id
> ;

As Alban said, your math priorities were wrong to begin with.

If I understand correctly, you want "number of nice = true" divided by
"total where not null"?

select id, 1.0 * sum(case when nice then 1 end) / count(nice) as rating
from pref_rep
where nice is not null
group by id;

> more details here:
>
> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table
>

Please keep all the information on the list.





-- 
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] databse version

[Luca Ferrari]

On Mon, Jun 10, 2013 at 9:52 AM, Philipp Kraus
 wrote:
> Hello,
>
> I'm creating a database and I have got a table with a "version" field.

Not sure, but if the version field is something like the version row
indicator used by some frameworks (e.g., Hibernate), then you are
going to place the updated version into all your records, that does
not sound as versioning at all!

Luca


-- 
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] Calculate a quotient for a count of boolean values (true or false)

[Albe Laurenz]

Alexander Farber wrote:
> In a PostgreSQL 8.4.13 why doesn't this please
> deliver a floating value (a quotient between 0 and 1):
> 
>  select
> id,
> count(nullif(nice, false)) - count(nullif(nice, true)) /
> count(nice) as rating
> from pref_rep where nice is not null
> group by id
> ;
>id| rating
> -+
>  DE10072 | -1
>  DE10086 | 18
>  DE10087 |  1
>  DE10088 | -1
>  DE10095 |276
>  DE10097 | 37
>  DE10105 |  5

Because you are using integer division.

Cast one of the values to double precision, like this:

(count(nullif(nice, false)) - count(nullif(nice, true))) / count(nice)::float8

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] Calculate a quotient for a count of boolean values (true or false)

[Alexander Farber]

Hello,

On Tue, Jun 11, 2013 at 11:59 AM, Alban Hertroys  wrote:
> Because you're calculating a - (b/c) instead of (a-b)/c

> On 11 June 2013 11:51, Alexander Farber  wrote:
>> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table

right, sorry! But now I have -1,0,1 only:

 select
id,
(count(nullif(nice, false)) - count(nullif(nice, true))) /
count(nice) as rating
from pref_rep where nice is not null
group by id
;
   id| rating
-+
 DE10019 |  0
 DE10030 |  1
 DE10047 |  0
 DE10049 |  1
 DE10051 |  0
 DE10059 | -1
 DE10062 |  0
 DE10067 | -1
 DE10072 | -1


Regards
Alex


-- 
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] Calculate a quotient for a count of boolean values (true or false)

[Alban Hertroys]

Because you're calculating a - (b/c) instead of (a-b)/c


On 11 June 2013 11:51, Alexander Farber  wrote:

> Hello!
>
> In a PostgreSQL 8.4.13 why doesn't this please
> deliver a floating value (a quotient between 0 and 1):
>
>  select
> id,
> count(nullif(nice, false)) - count(nullif(nice, true)) /
> count(nice) as rating
> from pref_rep where nice is not null
> group by id
> ;
>id| rating
> -+
>  DE10072 | -1
>  DE10086 | 18
>  DE10087 |  1
>  DE10088 | -1
>  DE10095 |276
>  DE10097 | 37
>  DE10105 |  5
>
> I am trying to calculate a sum of ratings
> given by users to each other
> to draw a pie chart in a game, more details here:
>
>
> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table
>
> Thank you
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

[GENERAL] Calculate a quotient for a count of boolean values (true or false)

[Alexander Farber]

Hello!

In a PostgreSQL 8.4.13 why doesn't this please
deliver a floating value (a quotient between 0 and 1):

 select
id,
count(nullif(nice, false)) - count(nullif(nice, true)) /
count(nice) as rating
from pref_rep where nice is not null
group by id
;
   id| rating
-+
 DE10072 | -1
 DE10086 | 18
 DE10087 |  1
 DE10088 | -1
 DE10095 |276
 DE10097 | 37
 DE10105 |  5

I am trying to calculate a sum of ratings
given by users to each other
to draw a pie chart in a game, more details here:

http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table

Thank you


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