Re: [GENERAL] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries

Rob Sargent wrote: > On 04/09/2013 02:29 PM, Giovanni Martina wrote: >> I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 >> running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu >> server 12.04.02 LTS. >> >> I have dumped one of our databases from our windows ser

Re: [GENERAL] What is pg backend using memory for?

hubert depesz lubaczewski wrote: > So, I checked a backend on Linux, and found such thing: > 2ba63c797000-2ba63fa68000 rw-p 2ba63c797000 00:00 0 > Size: 52036 kB > Rss: 51336 kB > Shared_Clean: 0 kB > Shared_Dirty: 0 kB > Private_Clean:0 kB > Private

Re: [GENERAL] Backup advice

Thanks everybody for your valuable comments. I can specify how many versions of the files should be kept on Tivoli. The database will regularly get new data and there is a continuous process of data cleaning. It is a database mainly for research purposes and a few researchers are using it. I wil

Re: [GENERAL] Backup advice

Hi Johann, On Wed, 10 Apr 2013 09:58:05 +0200, Johann Spies wrote: I can specify how many versions of the files should be kept on Tivoli. Another option you can evaluate is the usage of backup catalogues, retention policies and archiving of Barman (www.pgbarman.org). We use it in some cont

Re: [GENERAL] What is pg backend using memory for?

On Wed, Apr 10, 2013 at 07:36:59AM +, Albe Laurenz wrote: > What libraries are loaded in this backend (lsof)? > Maybe it's something non-PostgreSQL that's hogging the memory. I don't have this particular backend anymore, and I don't have lsof. But in smaps there are libraries listed, so: Stil

[GENERAL] Characters

Hi there I am using PG 9.2 and 'pg_database' shows 'en_US.UTF-8'. I have a text 'Piqué' but this is shown in my application as 'Piqu?' so I was wondering where the problem is? I am using Devart's 'PostgreSQL Data Access Components' to access PG from FreePascal. In my FreePascal code I do use An

Re: [GENERAL] optimizer's cost formulas

Hi Sebastien, > The tool to tweak the query planner parameters mentioned in the article > sounds very useful. Can we download it somewhere, either as binary or > source code ? It is currently not publicly available, because it contains some specifics (no secrets) of the experiments I did, the mos

[GENERAL] how to find which tables required indexes in postgresql

hi,, please suggest if there is any way which i can find which tables need indexes in postgresql.

Re: [GENERAL] how to find which tables required indexes in postgresql

Sent from my iPad On 10-Apr-2013, at 17:01, Zahid Quadri wrote: > hi,, > > please suggest if there is any way which i can find which tables need indexes > in postgresql. > > > > If the table under consideration has lots of data,and queries on it are very slow,you could consider addin

Re: [GENERAL] how to find which tables required indexes in postgresql

Zahid Quadri, 10.04.2013 13:31: hi,, please suggest if there is any way which i can find which tables need indexes in postgresql. Tables don't need indexes. Queries do. You will need to show us the queries in question (e.g. those that are slow) in order to decide which index is helpful. Th

[GENERAL] How to convert US date format to European date format ?

Hello ppl, I have a database where the previous owner use US date format in date fields: 2009-02-18 Is there a way how to convert the fields in European format 18-02-2009. I mean existing date in records. What's will be happened if I change format in postgresql.conf ? Cheers, Hristo S.

Re: [GENERAL] How to convert US date format to European date format ?

Condor, 10.04.2013 15:03: Hello ppl, I have a database where the previous owner use US date format in date fields: 2009-02-18 Is there a way how to convert the fields in European format 18-02-2009. I mean existing date in records. What's will be happened if I change format in postgresql.conf ?

Re: [GENERAL] How to convert US date format to European date format ?

On 04/10/2013 06:03 AM, Condor wrote: Hello ppl, I have a database where the previous owner use US date format in date fields: 2009-02-18 Is there a way how to convert the fields in European format 18-02-2009. I mean existing date in records. What's will be happened if I change format in postg

Re: [GENERAL] how to find which tables required indexes in postgresql

Hello, 2013/4/10 Thomas Kellerer > Zahid Quadri, 10.04.2013 13:31: > > hi,, >> >> please suggest if there is any way which i can find which tables need >> indexes in postgresql. >> > You have some possibilities: - the log file (slow queries) - statistics with old information (see the ANALYZE

Re: [GENERAL] Characters

Hello, 2013/4/10 P. Broennimann > Hi there > > I am using PG 9.2 and 'pg_database' shows 'en_US.UTF-8'. > > I have a text 'Piqué' but this is shown in my application as 'Piqu?' so I > was wondering where the problem is? > What is the result for these commands? SHOW client_encoding; and SHOW

Re: [GENERAL] how to find which tables required indexes in postgresql

On Wed, Apr 10, 2013 at 9:19 AM, JotaComm wrote: > Hello, > > > 2013/4/10 Thomas Kellerer > >> Zahid Quadri, 10.04.2013 13:31: >> >> hi,, >>> >>> please suggest if there is any way which i can find which tables need >>> indexes in postgresql. >>> >> > You have some possibilities: > > - the log

Re: [GENERAL] how to create materialized view in postgresql 8.3

Le lundi 08 avril 2013 à 09:36 -0700, Mike Christensen a écrit : > This is the number one requested feature on Uservoice: > > > http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views > > I find this rather surprising, considering the fact that a properly tun

Re: [GENERAL] how to create materialized view in postgresql 8.3

On Wed, Apr 10, 2013 at 10:33 PM, Vincent Veyron wrote: > I find this rather surprising, considering the fact that a properly > tuned database will return queries over millions of rows and multiple > joins in milliseconds, given the proper hardware. > > I can see how a datawharehouse with terrabyt

Re: [GENERAL] how to find which tables required indexes in postgresql

On Wed, Apr 10, 2013 at 10:19 PM, JotaComm wrote: > Hello, > > > 2013/4/10 Thomas Kellerer > >> Zahid Quadri, 10.04.2013 13:31: >> >> hi,, >>> >>> please suggest if there is any way which i can find which tables need >>> indexes in postgresql. >>> >> > You have some possibilities: > > - the log

Re: [GENERAL] Characters

On 04/10/2013 02:53 AM, P. Broennimann wrote: Hi there I am using PG 9.2 and 'pg_database' shows 'en_US.UTF-8'. I have a text 'Piqué' but this is shown in my application as 'Piqu?' so I was wondering where the problem is? I am using Devart's 'PostgreSQL Data Access Components' to access PG fro

Re: [GENERAL] how to find which tables required indexes in postgresql

On 04/10/2013 06:31 AM, Zahid Quadri wrote: please suggest if there is any way which i can find which tables need indexes in postgresql. If you have 8.4 or newer, you can look in pg_stat_statements for queries that use a lot of time. Check the where clauses for columns or transforms that can

Re: [GENERAL] Characters

Hi Adrian Thanks a lot! After spending the day checking all sorts of things you saved my day :) -> I just added the unit 'cwstring' and now it works. Thanks & good day Cheers, Peter -- Peter Broennimann Untergasse 11 a CH-8476 Unterstammheim Switzerland

Re: [GENERAL] how to create materialized view in postgresql 8.3

On 10/04/13 23:33, Vincent Veyron wrote: > Le lundi 08 avril 2013 à 09:36 -0700, Mike Christensen a écrit : >> This is the number one requested feature on Uservoice: >> >> >> http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views >> >> > > I find this rather sur

[GENERAL] Statistics query

I'm seeking ideas on the best way to craft the following query. I've stripped everything down to the bare essentials and simplified it below. Input data has a timestamp (actually an int received from the system in the form of a Unix epoch), a unit identifier and a status: event_time | unit_i

Re: [GENERAL] Statistics query

On Wed, Apr 10, 2013 at 12:30 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > I'm seeking ideas on the best way to craft the following query. I've > stripped everything down to the bare essentials and simplified it below. > > Input data has a timestamp (actually an int received from

Re: [GENERAL] Statistics query

On 04/10/2013 10:31 AM, Chris Curvey wrote: On Wed, Apr 10, 2013 at 12:30 PM, Steve Crawford > wrote: I'm seeking ideas on the best way to craft the following query. I've stripped everything down to the bare essentials and simplified it below

Re: [GENERAL] How to convert US date format to European date format ?

On 4/10/2013 6:15 AM, Thomas Kellerer wrote: psql (one of the possible client applications) uses the "datestyle" parameter to decide on how to format a date column when displaying it. If you change the "datestyle" parameter in postgresql.conf, it will influence the way psql displays the date v

Re: [GENERAL] How to convert US date format to European date format ?

John R Pierce wrote on 10.04.2013 21:28: On 4/10/2013 6:15 AM, Thomas Kellerer wrote: psql (one of the possible client applications) uses the "datestyle" parameter to decide on how to format a date column when displaying it. If you change the "datestyle" parameter in postgresql.conf, it will in

[GENERAL] Call for Sponsors: German-speaking PostgreSQL Conference 2013

The German-speaking PostgreSQL Conference 2013 is an excellent way for businesses to reach users and developers, or to give something back to the community. If you are interested in becoming a sponsor, we have put together several options for you: http://2013.pgconf.de/cfs The offers for Go

[GENERAL] Announcement: German-speaking PostgreSQL Conference 2013

PGConf.DE 2013 is the sequel of the highly successful German-speaking PostgreSQL Conference 2011. We maintain the proven concept: November 8th, 2013, the Rhineland Industrial Museum in Oberhausen. http://2013.pgconf.de/ The call for papers will open in a few days. Registration for the confe

Re: [GENERAL] SOLVED Statistics query

On 04/10/2013 10:51 AM, Steve Crawford wrote: ... Given a point in time I would like to: 1. Identify all distinct unit_ids with an entry that exists in the preceding hour then 2. Count both the total events and sum the status=1 events for the most recent 50 events for each

Re: [GENERAL] How to convert US date format to European date format ?

On 4/10/2013 12:35 PM, Thomas Kellerer wrote: But the *display* is done by the client. And if Postgres (the server) did the conversion, I would not be able to see a different date formatting in e.g. a JDBC based tool. So I guess psql is reading that database/server setting. psql is letting po

[GENERAL]

Wed, 10 Apr 2013 13:30:52 -0700 (PDT) Wed, 10 Apr 2013 13:30:52 -0700 (PDT) X-Newsgroups: pgsql.general Date: Wed, 10 Apr 2013 13:30:52 -0700 (PDT) Complaints-To: groups-ab...@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=76.87.68.198; posting-account=96NFGAo

[GENERAL] Segmentation fault with core dump

Hi Group, 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 12

Re: [GENERAL] Segmentation fault with core dump

Joshua Berry escribió: > gdb /usr/pgsql-9.1/bin/postmaster -c core.17356 > [...loading/reading symbols...] > Core was generated by `postgres: [username] [databasename] > [client_ipaddress](1500) SELECT '. > Program terminated with signal 11, Segmentation fault. > #0 ResourceOwnerEnla

Re: [GENERAL] Segmentation fault with core dump

Hi, On 2013-04-10 16:34:40 -0500, Joshua Berry wrote: > Below are the relevant details. I'm not terribly savvy with gdb, so please > let me know what else I could/should examine from the core dump, as well as > anything else about the system/configuration. > Kind Regards, > -Joshua > > #NB: som

Re: [GENERAL] Segmentation fault with core dump

Hi Andres! On Wed, Apr 10, 2013 at 4:49 PM, Andres Freund wrote: > Could you show the output of 'bt full'? > Program terminated with signal 11, Segmentation fault. #0 ResourceOwnerEnlargeCatCacheRefs (owner=0x0) at resowner.c:605 605 if (owner->ncatrefs < owner->maxcatrefs) (gdb) bt

Re: [GENERAL] Segmentation fault with core dump

On 2013-04-10 16:53:12 -0500, Joshua Berry wrote: > #7 0x00638c78 in PortalRun (portal=0x2aa9360, count=10, > isTopLevel=1 '\001', dest=0x2a50c40, altdest=0x2a50c40, > completionTag=0x7fffd193d0f0 "") at pquery.c:787 > save_exception_stack = 0x7fffd193cfe0 > save_co

Re: [GENERAL] Segmentation fault with core dump

> Ok, so while we have a valid resource owner up to here, portal->resonwer > is NULL. > > Could you 'up' until youre in this frame and then do 'print *portal'? > #7 0x00638c78 in PortalRun (portal=0x2aa9360, count=10, isTopLevel=1 '\001', dest=0x2a50c40, altdest=0x2a50c40, complet

Re: [GENERAL] Segmentation fault with core dump

Andres Freund writes: > Ok, so while we have a valid resource owner up to here, portal->resonwer > is NULL. Yeah, that's what I'm guessing. Given the exposed reference to a cursor WITH HOLD, it seems likely that the reason the portal has no resowner is that PreCommit_Portals() got rid of it when

Re: [GENERAL] Segmentation fault with core dump

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

Re: [GENERAL] Segmentation fault with core dump

On 2013-04-10 17:31:09 -0500, Joshua Berry wrote: > > Ok, so while we have a valid resource owner up to here, portal->resonwer > > is NULL. > > > > Could you 'up' until youre in this frame and then do 'print *portal'? > > > > #7 0x00638c78 in PortalRun (portal=0x2aa9360, count=10, > i

Re: [GENERAL] Segmentation fault with core dump

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

Re: [GENERAL] Segmentation fault with core dump

Andres Freund writes: > Tom: It looks to me like printtup_prepare_info won't normally be called > in an held cursor. But if some concurrent DDL changed the number of > columns in typeinfo vs thaose in the the receiver that could explain the > issue and why its not seen all the time, right? It loo

Re: [GENERAL] Segmentation fault with core dump

> Ok, I might be seeing whats going on here. Could you go to 'printtup' > and print *myState, *myState->attrinfo, *typpeinfo? > #4 0x004593c4 in printtup (slot=0x2d14618, self=0x2a50c40) at printtup.c:297 297 printtup_prepare_info(myState, typeinfo, natts); (gdb) p

Re: [GENERAL] Segmentation fault with core dump

On 2013-04-10 18:25:24 -0500, Joshua Berry wrote: > > Ok, I might be seeing whats going on here. Could you go to 'printtup' > > and print *myState, *myState->attrinfo, *typpeinfo? > > > > #4 0x004593c4 in printtup (slot=0x2d14618, self=0x2a50c40) > at printtup.c:297 > 297

Re: [GENERAL] Segmentation fault with core dump

Andres Freund writes: > On 2013-04-10 19:06:12 -0400, Tom Lane wrote: >> 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 e

Re: [GENERAL] Segmentation fault with core dump

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

Re: [GENERAL] Segmentation fault with core dump

Hm. Make that a > print *(DR_printtup *) self > print *((DR_printtup *) self)->attrinfo > print *slot->tts_tupleDescriptor (gdb) print *(DR_printtup *) self $2 = {pub = {receiveSlot = 0x459390 , rStartup = 0x459550 , rShutdown = 0x458a20 , rDestroy = 0x458a10 , mydest = DestRemoteExec

Re: [GENERAL] Segmentation fault with core dump

On 2013-04-10 19:29:06 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2013-04-10 19:06:12 -0400, Tom Lane wrote: > >> 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