Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 11:49 PM, Chris Travers wrote: > For pre-9.0, just explicitly create, run, and drop a pl/pgsql function. > Much easier than a shell script. +1, good point. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone:

Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 11:13 PM, Sergey Konoplev wrote: > On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi > wrote: > > Is there a way, I can say create a rule for all the tables in an schema? > > This will avoid writing complicated functions. > > You can use DO block if your postgres version i

Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi wrote: > Is there a way, I can say create a rule for all the tables in an schema? > This will avoid writing complicated functions. You can use DO block if your postgres version is >=9.0. DO $$ DECLARE _tablename text BEGIN FOR SELECT I

[GENERAL] Rule for all the tables in a schema

Hi, I am in the process of creating a rule that provides upsert functionality for all the tables which has primary key.The issue here is we have some 50 to 60 odd tables and have to write a functions that iterates through all these tables , create rules for each of this table, so that the

[GENERAL] Contents of data/base/ and no corresponding entry in pg_database

Hi, (We're running postgres 9.2.X) from reading http://www.postgresql.org/docs/9.2/static/storage-file-layout.html the directories under $PG_DATADIR/data/base should correspond to an actual database. I've however found a few directories in $PG_DATADIR/data/base where select datname from pg_databa

Re: [ODBC] [GENERAL] ODBC constructs

From: Dev Kumkar [mailto:devdas.kum...@gmail.com] Sent: Tuesday, May 21, 2013 7:33 PM To: Dann Corbit Cc: John R Pierce; pgsql-general@postgresql.org; pgsql-o...@postgresql.org Subject: Re: [ODBC] [GENERAL] ODBC constructs [snip] Thanks for the info. Its Red Hat Enterprise Linux Server release 5.5.

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On Tue, 21 May 2013 11:40:55 +1000, Toby Corkindale wrote: >>> While it is important to let the SSD know about space that can be >>> reclaimed, I gather the operation does not perform well. I *think* >>> current advice is to leave 'discard' off the mount options, and instead >>> run a nightly cron

Re: [ODBC] [GENERAL] ODBC constructs

On Wed, May 22, 2013 at 4:10 PM, Devrim GÜNDÜZ wrote: > > Yes, or, as mentioned before, you can simply download the RPM directly > from the repo. > Thanks Devrim! Installed postgres-92 server from postgresql92-server-9.2.4-1PGDG.rhel5.x86_64.rpm, actually links which John (Thanks!) mentioned wer

Re: [ODBC] [GENERAL] ODBC constructs

[snip] You want me clean some stuff. Because I installed 'unixODBC-2.3.0'? >> I guess that if you did a successful make install of unixODBC-2.3.0 it will work as your ODBC driver manager. On the other hand, it is easier and more trouble free to use the standardized package installer for your dis

Re: [GENERAL] Table Partitioning

So I worked around most of my errors.  I removed the bigserial and used two of the columns as the primary key.  I am now getting the following hibernate exception back: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1 This appears to be caused by the

Re: [GENERAL] Table Partitioning

Raghavendra, I am doing my inserts via Java JPA statements embedded in my Data Access Layer.  I can share them if you would like to see them.   Regards, Richard From: Raghavendra To: Richard Onorato Cc: "pgsql-general@postgresql.org" Sent: Wednesday, May

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

On Wed, May 22, 2013 at 7:41 AM, wrote: > PostgreSQL 9.1.6 on linux > >From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN against the index on sars_run_id to pull out the 3297 rows (again, it think, th

Re: [GENERAL] [pgeu-general] Replication failover

BTW, pgeu-general is not for technical questions, so moving to pgsql-general. (I didn't notice the mailing list this came from until after replying). On 22.05.2013 18:22, Heikki Linnakangas wrote: On 22.05.2013 10:23, TJ wrote: I am looking to migrate my databases from one set of hardware to

Re: [ODBC] [GENERAL] ODBC constructs

On Wed, May 22, 2013 at 11:44 AM, John R Pierce wrote: > why the heck are you not installing unixODBC-libs from RPMs ?!? > > yum install unixODBC64 unixODBC64-libs unixODBC64-devel > > should do it, unless you're on Red Hat Enterprise without a subscription, > then you can do it the hard way.

Re: [GENERAL] Very simple select, using index for ordering, but not for selecting. How to make it faster?

On 05/22/2013 02:38 PM, Antonio Goméz Soto wrote: Limit (cost=0.00..14799.28 rows=1000 width=58) -> Index Scan Backward using history_created_index on history (cost=0.00..12201987.90 rows=824499 width=58) Filter: ((lookup = 'trunk'::text) AND (lookupid = 248)) It's not using history_lookup_l

[GENERAL] Very simple select, using index for ordering, but not for selecting. How to make it faster?

Hi, I have the following simple query on a simple table: system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; system=# \d history Table "public.history" Column | Type |

Re: [GENERAL] pg_upgrade -u

On May 21, 2013, at 2:41 PM, Bruce Momjian wrote: > On Wed, May 8, 2013 at 08:52:40PM -0400, Bruce Momjian wrote: >> On Wed, May 8, 2013 at 05:05:05PM -0400, Ray Stell wrote: >>> A minor detail in 9.2.4, but I noticed that the pg_upgrade flag for >>> superuser, -u, does not get carried to a -U

[GENERAL] data file corruption

Hi All, We are facing one strange problem about data file corruptions. We have many postgres databases. At some point, one simple query on one database started crashing back-end. The query is select count(*), col1 from tab1 group by col1; After using pg_filedump (http://pgfoundry.org/projects

Re: [GENERAL] Strange locking problem

Solution: The inserts of the foreign key to tb_entity were blocking the updates to those rows of tb_entity. I solved the problem by making the foreign key constraints deferrable and deferring checking on them till the end of the transaction. On Tue, May 21, 2013 at 3:24 PM, Moshe Jacobson wrote

Re: [ODBC] [GENERAL] ODBC constructs

On 5/22/2013 11:24 AM, Dev Kumkar wrote: Started first with looking more into building psqlODBC today why the heck are you not installing unixODBC-libs from RPMs ?!? yum install unixODBC64 unixODBC64-libs unixODBC64-devel should do it, unless you're on Red Hat Enterprise without a subsc

Re: [GENERAL] Ambiguous order by?

Okay, so why does wrapping the order by in a function fix it? (or not doing a join, or doing an implicit join) Cody Cutrer On Wed, May 22, 2013 at 11:36 AM, Tom Lane wrote: > Cody Cutrer writes: > > create table test1 (id integer, sortable_name varchar); > > create table test2 (id integer, te

Re: [GENERAL] Ambiguous order by?

Cody Cutrer writes: > create table test1 (id integer, sortable_name varchar); > create table test2 (id integer, test1_id integer); > select test1.sortable_name, sortable_name from test1 inner join test2 > on test1.id=test1_id order by sortable_name; > ERROR: ORDER BY "sortable_name" is ambiguous

Re: [GENERAL] DECLARING THE CURSOR WITH HOLD

Maybe refcursors ?? All what you can do with cursors is described in http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html Regards Pavel 2013/5/22 Sajeev Mayandi : > Is there a work around to declare the cursor with hold? > > Thanks, > > Sajeev > > On 5/22/13 10:19 AM, "Pavel Stehule"

Re: [GENERAL] DECLARING THE CURSOR WITH HOLD

Is there a work around to declare the cursor with hold? Thanks, Sajeev On 5/22/13 10:19 AM, "Pavel Stehule" wrote: >Hello > >2013/5/22 Sajeev Mayandi : >> >> >> Hi, >> >> I am trying to declare a cursor with hold along with NO SCROLL option. >> I >> am getting syntax error. Just wondering if

Re: [GENERAL] DECLARING THE CURSOR WITH HOLD

Hello 2013/5/22 Sajeev Mayandi : > > > Hi, > > I am trying to declare a cursor with hold along with NO SCROLL option. I > am getting syntax error. Just wondering if CURSOR WITH HOLD option > supported. My code snip is. > > DECLARE noncontainer NO SCROLL CURSOR WITH HOLD FOR SELECT * from > phost

[GENERAL] DECLARING THE CURSOR WITH HOLD

Hi, I am trying to declare a cursor with hold along with NO SCROLL option. I am getting syntax error. Just wondering if CURSOR WITH HOLD option supported. My code snip is. DECLARE noncontainer NO SCROLL CURSOR WITH HOLD FOR SELECT * from phostmapping; NOTE: The code is in plpgsql and postg

Re: [GENERAL] VACUUM FULL freezes

RDNikeAir wrote: > Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last > message i have in messages.* is from a few days ago. So there are no recent > entries that i can look at. Well, it is clear then that the first thing you need to do is fix your logging. -- Álvaro H

[GENERAL] Ambiguous order by?

I'm not sure if this is a bug, or something I'm not understanding. When I have a column referenced in the select both fully qualified and not fully qualified, an explicit inner join, and that column in the order by (unqualified, and not in a function call), it complains that is ambiguous: create t

Re: [GENERAL] VACUUM FULL freezes

Interestingly enough all the pgsql.log.* files are blank (0 KB) and the last message i have in messages.* is from a few days ago. So there are no recent entries that i can look at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477p5756484.ht

Re: [GENERAL] VACUUM FULL freezes

On Wed, May 22, 2013 at 8:49 AM, RDNikeAir wrote: > I have a database that is on a RAID5 machine that is almost out of memory > (277GB of 330GB used). I have deleted some data and run the VACUUM FULL > command, but after a few hours gave me the error message "Server closed the > connection unexpe

[GENERAL] VACUUM FULL freezes

I have a database that is on a RAID5 machine that is almost out of memory (277GB of 330GB used). I have deleted some data and run the VACUUM FULL command, but after a few hours gave me the error message "Server closed the connection unexpectedly. This probably means the server terminated abnormall

Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptable latency.

PostgreSQL 9.1.6 on linux Original Message Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency. From: Jaime Casanova Date: Tue, May 21, 2013 2:59 pm To: Freddie Burgess Cc: psql performance list

Re: [GENERAL] Slow query and using wrong index, how to fix? Probably naive question..

Leif, of course. This performs much better (far below one second). Thanks! Antonio Op 22-05-13 11:28, Leif Gunnar Erlandsen schreef: > You might want to try with UNION and then sort the result of this query. > > The index history_lookup_lookupid_creator_index wont be used when you are > havin

Re: [GENERAL] Interrupt WAL recovery

you can use pg_xlog_replay_pause(),pg_xlog_replay_resume() to control the wal replay. ref doc:http://www.postgresql.org/docs/9.1/static/functions-admin.html 2013/5/22 Raghavendra > > On Wed, May 22, 2013 at 4:15 PM, Raghavendra < > raghavendra@enterprisedb.com> wrote: > >> On Wed, May 22, 2

Re: [GENERAL] Interrupt WAL recovery

On Wed, May 22, 2013 at 4:15 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Wed, May 22, 2013 at 3:53 PM, Fabio Rueda Carrascosa < > avances...@gmail.com> wrote: > >> Can I stop the server and modify recovery.conf with this param and >> restart it again without problems? >> >> > I

Re: [GENERAL] Interrupt WAL recovery

On Wed, May 22, 2013 at 3:53 PM, Fabio Rueda Carrascosa < avances...@gmail.com> wrote: > Can I stop the server and modify recovery.conf with this param and restart > it again without problems? > > It should work. (Sorry I never tried this, like stopping in middle of the recovery and restarting wit

Re: [GENERAL] Interrupt WAL recovery

Can I stop the server and modify recovery.conf with this param and restart it again without problems? Thanks a lot. 2013/5/22 Raghavendra > On Wed, May 22, 2013 at 3:38 PM, Fabio Rueda Carrascosa < > avances...@gmail.com> wrote: > >> hello , I'm restoring a 7 days ago full backup + wal files.

Re: [GENERAL] Interrupt WAL recovery

On Wed, May 22, 2013 at 3:38 PM, Fabio Rueda Carrascosa < avances...@gmail.com> wrote: > hello , I'm restoring a 7 days ago full backup + wal files. I have to > recover more than 6k wal files and I have no time, I would accept to work > with a ~ 3 days ago snapshot, can I stop the recovery proces

[GENERAL] Interrupt WAL recovery

hello , I'm restoring a 7 days ago full backup + wal files. I have to recover more than 6k wal files and I have no time, I would accept to work with a ~ 3 days ago snapshot, can I stop the recovery process and start the server?, if yes, how? My recovery.conf file only has this line : restore_com

Re: [GENERAL] Slow query and using wrong index, how to fix? Probably naive question..

You might want to try with UNION and then sort the result of this query. The index history_lookup_lookupid_creator_index wont be used when you are having an "OR" in your WHERE statement. select history.id, history.created, creator, contact, history.type, lookup, lookupid, value from history

Re: [GENERAL] Slow query and using wrong index, how to fix? Probably naive question..

Antonio Goméz Soto wrote: > I am using postgresql 8.1 (CentOS5). I have the following table: > > system # \d history >Table "public.history" > Column | Type | Modifiers > --+--+-

[GENERAL] Slow query and using wrong index, how to fix? Probably naive question..

Hi, I am using postgresql 8.1 (CentOS5). I have the following table: system # \d history Table "public.history" Column | Type | Modifiers --+--+--

Re: [GENERAL] Table Partitioning

On Wed, May 22, 2013 at 6:54 AM, Richard Onorato wrote: > Were you able to get it to insert with the bigserial being used on the > table? > Yes. > Every time I go to do an insert into one of the inherited tables I am now > getting the following exception: > org.hibernate.HibernateException: The