[GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

mpting zero_damaged_pages cause any harm as the first step (other than the obvious destruction of any bad pages)? Is this the correct command if option #2 is chosen? Can it be executed against a running cluster? dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1 conv=notrunc Thanks, Bricklen

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

Hi Tom, On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane wrote: > bricklen writes: > > I get the following output for ctid, id, other_id, tstamp: > > > (690651,42) |318698967 | 347978007 | 2015-10-20 01:55:41.757+00 > > (690651,43) |318698968 | 347978008 | 20

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On Wed, Oct 21, 2015 at 12:52 PM, Tom Lane wrote: > bricklen writes: > > Yes, it is definitely a table. There was originally an index on that > table > > which threw the original error (about sibling mismatch). I dropped the > > index and attempted to recreate i

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

A follow-up question if I may, > bricklen writes: >> > Yes, it is definitely a table. There was originally an index on that >> table >> > which threw the original error (about sibling mismatch). I dropped the >> > index and attempted to recreate it, which

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

On Thu, Oct 22, 2015 at 9:15 AM, Tom Lane wrote: > bricklen writes: > > What would have happened to the WAL-shipping-only standby if the WALs > were > > all applied? Would it have it balked at applying a WAL containing bad > data > > from the master, or would

Re: [GENERAL] Re-sync slave server

On Mon, Jun 6, 2016 at 5:32 PM, Patrick B wrote: > My set up: > > > Master --> slave01 (streaming replication) --> slave02 (streaming > replication) > Master --> slave03 (wal_files 4 days old, not streaming replication) > > The wal_files are stored into each server. > But when the slave03 was dow

Re: [GENERAL] PostgreSQL GUI tools

On Sun, Jan 3, 2016 at 2:15 AM, subhan alimy wrote: > Is there any PostgreSQL GUI tools to pull the tables structure along with > the relationships? > Along with the other suggestions, I have personally used http://www.dbschema.com/. It works well, but I don't recall if there was a free version.

Re: [GENERAL] Submitting to this list

On Wed, Jan 6, 2016 at 9:15 AM, Melvin Davidson wrote: > > I would like to see the following added to the introduction to this list. > > "When submitting to this list, please include the full version of > PostgreSQL and the O/S you are using. > Also, if you are reportaing a problem, it is essenti

Re: [GENERAL] New Slave - timeline ERROR

On Fri, Jan 8, 2016 at 8:44 PM, drum.lu...@gmail.com wrote: Hi, I'm a bit too lazy to try suss out the exact reasons for your failure, but here is a reasonably thorough guide to set up replication: http://dba.stackexchange.com/a/53546/24393 A few tips: - Having the master ship WALs to the slaves

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 12:36 PM, drum.lu...@gmail.com wrote: > Do you have any other ideia? Do you know if --xlog it's the problem and I > should re-run the pg_basebackup again with the *--xlog-method=stream* > option? > If the master is successfully ships WALs to the slave you are setting up y

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:49 PM, drum.lu...@gmail.com wrote: > Hi, > > If the master is successfully ships WALs to the slave you are setting up >> you do not need the "stream" option. > > > yes.. the master is successfully shipping the WALs > > Is there anything else? Help, please hehehehe >

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:54 PM, drum.lu...@gmail.com wrote: > Hi, > > If you are able to stop Postgres on the slave you are taking the base >> backup from, you could do this: > > > I'm not... the data base is 2 TB. > So, a RSYNC would take DAYS. And I'm not able to stop the SLAVE for > that

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:10 PM, drum.lu...@gmail.com wrote: > I could stop the slave then But I'm afraid getting it back online and > get some other errors > At this point I think your options are slim. If you are feeling adventurous, you can try doing the rsync with the slave running, then

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:22 PM, drum.lu...@gmail.com wrote: > *rsync would be something like:* > > from slave1: > rsync -av data/pg_xlog slave2:/var/lib/postgresql/data/ > Normally I run something like the following from the slave I am setting up. rsync -azr --progress --partial postgres@$MASTE

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:35 PM, drum.lu...@gmail.com wrote: > rsync -azr --progress --partial postgres@$MASTER_IP: >> /var/lib/postgresql/data/var/lib/postgresql/data/ --exclude >> postmaster.pid > > > Ah ok! So this will do an incrementa, right? not supposed to copy ALL the > base/ again? > Yes

Re: [GENERAL] Subtract one array from another, both with non-unique elements

On Sun, Mar 6, 2016 at 9:22 AM, Alexander Farber wrote: > could someone please recommend the most efficient way subtracting elements > of one array from the other in PostgreSQL 9.5? > There are quite a few examples of array functions at http://postgres.cz/wiki/Array_based_functions, probably wor

Re: [GENERAL] vacuum - reclaiming disk space.

On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell wrote: > I have a large table with numerous indexes which has approximately doubled > in size after adding a column - every row was rewritten and 50% of the > tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot > seem to finish

Re: [GENERAL] More correlated (?) index woes

On Tue, Mar 29, 2016 at 3:47 AM, Geoff Winkless wrote: > On 28 March 2016 at 20:23, I wrote: > >> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's >> absolutely not reasonable to expect this to be an optimal strategy. >> ​ >> > It occurred to me that even though the majority

Re: [GENERAL] pg_restore load data

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson wrote: > v9.2.7 (Yes, I know, it's old. Nothing I can do about it.) > > During a "whole database" restore using pg_restore of a custom dump, when > is the data actually loaded? I've looked in the list output and don't see > any "load" statements. >

Re: [GENERAL] how to get current sql execution time?

On Thu, Oct 14, 2010 at 3:47 PM, sunpeng wrote: > when I use the psql to send a sql, how to get current sql execution time? > At the psql prompt: # \timing Timing is on. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postg

Re: [GENERAL] pg view of table columns needed for scripting

On Fri, Oct 22, 2010 at 9:14 AM, Gauthier, Dave wrote: > Is there something like a pg_xxx view that I can use to get the column names > and data types of a table, similar to what I see with \d ?  I need to run > this is a script, so \d isn't viable.  I did a \df and looked around, but > nothing po

Re: [GENERAL] Can Postgres Not Do This Safely ?!?

On Fri, Oct 29, 2010 at 4:59 PM, Jeff Davis wrote: > On Fri, 2010-10-29 at 16:57 -0500, Andy Colson wrote: >> begin >> insert into logged select * from events where processed = false; >> update events set processed = true where processed = false; >> commit; > > There's a race condition there. The

Re: [GENERAL] Trouble Accessing Schema-Qualified Table

On Mon, Nov 15, 2010 at 8:35 AM, Jerry Richards wrote: > > teo=# select * from sip_presence('ts_sofia_internal') where > sip_presence.sip_user='1003'; > > ERROR:  function sip_presence(unknown) does not exist > > LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr... >    

Re: [GENERAL] How to obtain the maximum value of a date, between 3 tables...

On Wed, Dec 8, 2010 at 5:15 AM, Andre Lopes wrote: > Hi, > > I need to obtain the maximum value of a date, but that comparison will be > made between 3 tables... I will explain better with a query... > > [code] > select > a.last_refresh_date as d1, ae.last_refresh_date as d2, ha.last_refresh_date

Re: [GENERAL] calculation of database size

On Thu, Dec 9, 2010 at 8:37 AM, Jaiswal Dhaval Sudhirkumar wrote: > How can I measure the approximate database size based on raw* data. > Is there any calculation template? > > -- > Thanks & Regards > DJ One option: select pg_size_pretty( pg_database_size( 'yourdatabase' ) ); -- Sent via pgsql-

Re: [GENERAL] Can the query planner create indexes?

On Tue, Dec 21, 2010 at 7:34 AM, Jeremy Harris wrote: > On 2010-12-21 14:26, t...@fuzzy.cz wrote: >>> >>> Why not auto-create indices for some limited period after database load >>> (copy?  any large number of inserts from a single connection?), track >>> those >>> that actually get re-used and re

Re: [GENERAL] Restore

On Fri, Dec 24, 2010 at 3:38 PM, Adrian Klaver wrote: > On Friday 24 December 2010 3:28:38 pm Bob Pawley wrote: >> Thanks Adrian but >> >> psql –U postgres -d PDW  -f PDW_June_10.sql >> >> asks - "Password for user postgres:" >> >> When I type the password the cursor doesn't respond and on enter I

[GENERAL] B-tree index with sorting question

In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html, I see that you can build indexes that include ordering. Eg. create index t_col_idx on t (col DESC NULLS LAST); Does that mean that the initial creation of the index acts like the CLUSTER command? If so, what happens to

Re: [GENERAL] B-tree index with sorting question

On 2010-12-28, at 5:22 PM, Tom Lane wrote: > bricklen writes: >> In the docs at >> http://www.postgresql.org/docs/9.0/static/sql-createindex.html, >> I see that you can build indexes that include ordering. >> Eg. create index t_col_idx on t (col DESC NULLS LAST); &g

Re: [GENERAL] B-tree index with sorting question

On Tue, Dec 28, 2010 at 11:04 PM, Guillaume Lelarge wrote: > Le 29/12/2010 05:28, Bricklen a écrit : >> On 2010-12-28, at 5:22 PM, Tom Lane wrote: >> >>> bricklen writes: >>>> In the docs at >>>> http://www.postgresql.org/docs/9.0/static/sql

[GENERAL] ERROR: could not open relation base/2757655/6930168: No such file or directory -- during warm standby setup

Hi all, After setting up a warm standby (pg_start_backup/rsync/pg_stop_backup), and promoting to master, we encountered an error in the middle of an analyze of the new standby db. (the standby server is a fresh server) Source db: PostgreSQL 8.4.2 Standby db: PostgreSQL 8.4.6 ... INFO: analyzing

Re: [GENERAL] ERROR: could not open relation base/2757655/6930168: No such file or directory -- during warm standby setup

On Wed, Dec 29, 2010 at 11:35 AM, Tom Lane wrote: > bricklen writes: >> After setting up a warm standby >> (pg_start_backup/rsync/pg_stop_backup), and promoting to master, we >> encountered an error in the middle of an analyze of the new standby >> db. (the stand

Re: [GENERAL] ERROR: could not open relation base/2757655/6930168: No such file or directory -- during warm standby setup

On Wed, Dec 29, 2010 at 12:11 PM, Tom Lane wrote: > bricklen writes: >> On Wed, Dec 29, 2010 at 11:35 AM, Tom Lane wrote: >>> What can you tell us about what was happening on the source DB while >>> the backup was being taken? > >> The source db has between

[GENERAL] B-tree + sorting + unique constraint

Hi, I have a follow-up question to my earlier question[1] about how sorting works in an index. Does creating a UNIQUE constraint not allow the aforementioned sorting capability? eg. create table t (x int, y int, z int); insert into t values (1,1,1),(2,2,2),(3,3,3),(1,2,3),(15,23,21); -- works:

Re: [GENERAL] B-tree + sorting + unique constraint

On Wed, Dec 29, 2010 at 5:04 PM, Jeff Davis wrote: > On Wed, 2010-12-29 at 16:39 -0800, bricklen wrote: >> -- works: >> create unique index t_uidx on t (x desc nulls last,y desc nulls last, z asc); >> drop index t_uidx; > > ... > >> -- creating the unique con

Re: [GENERAL] ERROR: could not open relation base/2757655/6930168: No such file or directory -- during warm standby setup

On Wed, Dec 29, 2010 at 1:53 PM, bricklen wrote: > On Wed, Dec 29, 2010 at 12:11 PM, Tom Lane wrote: >> >> The difference in ctid, and the values of xmin and relfrozenxid, >> seems to confirm my suspicion that this wasn't just random cosmic rays. >> You did

Re: [GENERAL] ERROR: could not open relation base/2757655/6930168: No such file or directory -- during warm standby setup

On Fri, Dec 31, 2010 at 11:53 AM, Tom Lane wrote: > I think you're missing the point here: there is something about your > standby setup procedure that is causing you to get an inconsistent set > of row states. For the sake of the archives, the problem turned out to be due to impatience. We were

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

On Mon, Jan 3, 2011 at 2:48 PM, Kurt Westerfeld wrote: > I have a JDBC-based application which passes date/time parameters using JDBC > query parameters, which is performing very badly (ie. doing full table > scans).  In an effort to try to narrow down the problem, I am taking the > query and runn

[GENERAL] PD_ALL_VISIBLE flag was incorrectly set in relation "pg_statistic"

We just had a slew of the following messages in our log. How concerned should I be at this point? I have no idea what triggered it. Version: PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit 2011-01-31 14:16:13 PST [32767]: [1-1] (u

Re: [GENERAL] PD_ALL_VISIBLE flag was incorrectly set in relation "pg_statistic"

On Mon, Jan 31, 2011 at 2:55 PM, bricklen wrote: > We just had a slew of the following messages in our log. How concerned > should I be at this point? I have no idea what triggered it. > > Version: > PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc > (GCC) 4.1.2

[GENERAL] View to show privileges on views/tables/sequences/foreign tables

A while back I was looking for a way to display object privileges quickly with a bit better readibility. The following view is what I came up with. Suggestions and improvements welcome (or comments stating that there are much easi\er ways to get the same details). (was created in a utility "admin"

Re: [GENERAL] View to show privileges on views/tables/sequences/foreign tables

On Thu, Feb 21, 2013 at 9:38 AM, bricklen wrote: > A while back I was looking for a way to display object privileges > quickly with a bit better readibility. The following view is what I > came up with. Suggestions and improvements welcome (or comments > stating that there are much

Re: [GENERAL] Error installation of openmolar

Those are not postgresql errors. Have you tried the openmolar mailing list at https://groups.google.com/forum/?fromgroups#!forum/openmolar ? On Mon, Apr 22, 2013 at 6:05 PM, elmekki mahdhaoui wrote: > Hello everyone > > I tried to install openmolar on my ubuntu > luc

Re: [GENERAL] bloating vacuum

On Tue, May 14, 2013 at 7:51 AM, S H wrote: > I created small table and started doing insertion/deletion/updation on > 2 rows in infinite loop. It started bloating around 844 times, but after it > stopped bloating.. what could be the reason? > Did autovacuum kick in and clean up the table? Is

Re: [GENERAL] Random numbers

On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar < kesco...@estudiantes.uci.cu> wrote: > I want to generate random numbers in Pl/pgSQL. How can I do this? > To be more specific, I have to generate random numbers among 0 and 5. > One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by r

Re: [GENERAL] search_path for database vs user vs session

On Mon, May 27, 2013 at 12:16 PM, Moshe Jacobson wrote: > Oh, and I'd also like to see the current setting of the database so I know > what will happen if I clear the user setting. I think you can find some of what you are looking for here: http://www.postgresql.org/docs/9.2/static/view-pg-sett

Re: [GENERAL] Database performs massive reads when I'm doing writes.

On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote wrote: > Starting a few days ago we started to see a strange behavior where writing > to the database causes massive read operations. > For example, I have a table that needs to be updated every night (about > 20,000 rows). > Using Django ORM, we update the

Re: [GENERAL] decrease my query duration

On Thu, Jul 4, 2013 at 5:26 PM, David Carpio wrote: > Hello All > > I am newbie in this forum that's why I will be very grateful if would be > able to help me and if you need some additional information please let me > know. > I have a query that I would like decrease its duration > Have a loo

Re: [GENERAL] Update big table

On Mon, Jul 15, 2013 at 6:08 AM, Haiming Zhang wrote: > I run my query using JOIN for two hours, and did not get it done. Here is > my query: > > update table1 set col1 = true from table2 where table1.event_id = > table2.event_id > Did you already post the query plan from "EXPLAIN update table1

Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982 wrote: > There is another situation, > > We have a demo table with about 1700 rows, the "select count(1) from > demotable" statement finishes with-in 3 seconds when executed directlly > against the Greenplum database,but it takes about 230 secon

Re: Reply: [GENERAL] Can we specify transaction level

On Fri, Jul 19, 2013 at 5:08 PM, guxiaobo1982 wrote: > > what does a wrapper function mean, count is a standard function in Greenplum, > or how to creat the wrapper function. > > regards > > On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982 wrote: > > There is another situation, > > > We have a de

Re: [GENERAL] Rule Question

On Thu, Jul 25, 2013 at 4:18 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > (TG_OP = 'UPDATE' AND > (NEW.b != OLD.b OR > (NEW.b IS NULL AND OLD.b IS NOT NULL) OR > (NEW.b IS NOT NULL AND OLD.b IS NULL) >

Re: [GENERAL] 5 is not a smallint but '5' is

On Mon, Jul 29, 2013 at 8:11 AM, John DeSoi wrote: > I was surprised to discover this today. I can work around it, but it seems > counterintuitive. If 5 can't be parsed as a smallint, I would feel better > if '5' was not one either. > > > temp=# create or replace function itest (param smallint) r

Re: [GENERAL] Fwd: corrupted files

On Mon, Jul 29, 2013 at 11:50 PM, Klaus Ita wrote: > I am trying to remember, there was a tool that plotted the contents of the > wal_files in a more readable format ... > xlogdump? https://github.com/snaga/xlogdump

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé wrote: > Hi, I need to do an alter table on a small table (~300 records), but it > never ends. It may be because there are clients using that table. > > How can I force disconnect all clients to let me alter that table?. > If you are using Postgre

Re: [GENERAL] Fwd: corrupted files

On Tue, Jul 30, 2013 at 8:18 AM, Klaus Ita wrote: > > thank you! It turned out that really there was a corruption in the main pg > server which was 'virally' propagated to > > 1. streaming replica > 1. replaying wal receiver > 1. old backup that tried to replay the wal's > > I really thought with

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé wrote: > > select pg_cancel_backend(pid) from pg_stat_activity where pid <> > pg_backend_pid(); > > And it returned this: > > pg_cancel_backend > --- > t > t > (2 rows) > > But when I execute my update table command, it still nev

Re: [GENERAL] Alter table never finishes

ct pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid(); That is much safer than kill -9, which if issued against the parent postgres process will crash your cluster. > > Sorry bricklen, I've killed all idle connections with "kill -9 ", > then I was able to execute the alter table. > No problem.

Re: [GENERAL] Alter table never finishes

On Tue, Jul 30, 2013 at 10:34 AM, bricklen wrote: > Sorry bricklen, I've killed all idle connections with "kill -9 ", > >> then I was able to execute the alter table. >> > > No problem. > btw, I meant 'no need to apologize that the queries were gone', not that I recommend "kill -9" (I don't!)

Re: [GENERAL] setting high value for wal_keep_segments

On Thu, Aug 8, 2013 at 6:23 AM, AI Rumman wrote: > Hi, > > I am going to sync slave with my master which is almost 500 G. I am not > using archive directory instead of I am using wal files for streaming. As > it may take almost 3 hours, I am thinking of setting up 400 for > wal_keep_segments wher

Re: [GENERAL] bi-directional syncing help request

On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra wrote: > --all you need to do is switch master and slave so that "master" is the > one box you are currently on > That probably isn't a reasonable solution, considering the OP mentioned that she was not a professional DBA. Setting up a hot/warm standb

Re: [GENERAL] bi-directional syncing help request

On Fri, Aug 9, 2013 at 8:12 AM, Bèrto ëd Sèra wrote: > or you can just take a full dump from one box and import it on the other > any time you switch. If it's not a big db it should probably be quicker > than any alternative approach. > A pg_dump from one system or the other will effectively over

Re: [GENERAL] devide and summarize sql result (all)

On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski wrote: > Hi, > > My sql query results sth. like this: > > user percentage > franz 78% > smith 98% > franz 81% > jason 79% > smith 89% > smith 85% > smith 99% > > Now I'd like to summarize the percentages oder every user like this. > smith > 2 matc

Re: [GENERAL] pg_get_triggerdef can't find the trigger using OID.

On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman wrote: > Why can't pg_get_triggerdef find the trigger using OID. > > testdb=# SELECT > testdb-# p.oid, > testdb-# n.nspname as "Schema", > testdb-# p.proname as "Name", > testdb-# pg_catalog.pg_get_function_result(p.oid) as "Result data type", >

Re: [GENERAL] Temp files on Commit

On Thu, Aug 22, 2013 at 8:40 AM, Tom Lane wrote: > > There is some setting that controls whether such messages appear at > all > Is it "log_temp_files"?

Re: [GENERAL] Column names for INSERT with query

On Thu, Aug 22, 2013 at 3:30 PM, Robert James wrote: > I would like to: > > INSERT INTO t > SELECT * FROM r, (x + y) AS field3 > INSERT INTO t (col1, col2, col3) SELECT ...

Re: [GENERAL] Update quey

On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) < sam.h...@alcatel-lucent.com> wrote: > I have a table (pubacc_lo) from the US government with 500,00+ rows. It > has latitude and longitude in three columns each for degrees, minutes and > seconds. I need a Point geometry column. So I wrote th

Re: [GENERAL] OLAP

On Tue, Aug 27, 2013 at 3:04 PM, Jerry Sievers wrote: > Vertica is another case of an analytics focused platform that dirived > from Postgres, version 8.0 IIRC. > FWIW, You might be thinking of Greenplum, and I think it forked from pg 8.1. Vertica was built from scratch, it is not using Postgres

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

On Mon, Apr 24, 2017 at 9:17 PM, Andres Freund wrote: > > Questions (answer as many you can comfortably answer): > - How many MB/s, segments/s do you see on busier servers? > - What generates the bulk of WAL on your servers (9.5+ can use > pg_xlogdump --stats to compute that)? > - Are you seein

Re: [GENERAL] Error in insert statement

Try dropping the word "VALUES". eg. INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec") SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID", "PaperID", "AttributeID","Spec" from "tblTempSpecs"; On Thu, Jul 16, 2009 at 12:40 PM, Re

Re: [GENERAL] commercial adaptation of postgres

Greenplum uses a modified version of PostgreSQL for their MPP product. http://www.greenplum.com/ On Mon, Jul 20, 2009 at 6:56 PM, Dennis Gearon wrote: > > I once talked to a company that made a custome version of Postgres. It split > tables up on columns and also by rows, had some other custome

Re: [GENERAL] Design Database, 3 degrees of Users.

Would Veil be useful to you? http://veil.projects.postgresql.org/curdocs/index.html On Fri, Jul 31, 2009 at 4:38 AM, Andre Lopes wrote: > I need to design a Database that will handle 3 degrees of users: > > > Administrators - They can see all the information in the database. > > Managers - They o

Re: [GENERAL] fsync and wal_sync_method

On Wed, Sep 11, 2013 at 6:11 PM, ascot.m...@gmail.com wrote: > Hi, > > I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and > pg-Slave with streaming replication. > > The archive_command is enabled and the "rsync" is used in pg-Master to > save all its archived WAL files to the 3

Re: [GENERAL] Why does this array query fail?

On Mon, Sep 16, 2013 at 5:32 PM, Ken Tanzer wrote: > SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pass','fail']) ); > It works if you drop the inner SELECT. SELECT 'found' WHERE 'test' = ANY( ARRAY['test','pass','fail'] );

Re: [GENERAL] Why does this array query fail?

On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer wrote: > Thanks for the explanation. I think I at least understand what it's doing > now. I'm either surprised or confused though, as I was under the > impression that you could substitute a subquery for a value pretty much > anywhere, but I guess tha

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:06 AM, Andrus wrote: > > >Could you please post EXPLAIN for that query? > Could you also post the results of the following query? SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); Also, what is the total memory in

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:33 AM, Andrus wrote: > Hi! > >Could you also post the results of the following query? > >SELECT name, current_setting(name), source > >FROM pg_settings > >WHERE source NOT IN ('default', 'override'); > In real server where problem is: > > > 21 shared_buffers

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:12 AM, Andrus wrote: > > work_mem = 400MB > > Hope that this is OK. > For cluster-wide setting you will probably want to drop that significantly -- start lower, as in somewhere around 10MB and work up from there as necessary. For the queries you are finding slow (the re

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:50 AM, Andrus wrote: > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > wrote that max reasonable value in Windows for shared_buffers is 512MB > Is my setting shared_buffers= 2400MB reasonable in Windows ? > Someone else will hopefully answer that questi

Re: [GENERAL] how to tell master from replica

On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe wrote: > Assuming a master & replica set up using streaming replication, hot > standby, 9.3. > > I'd like to have a single script on both machines distinguish whether it's > running on the machine with the master or replica, and take different > actions

Re: [GENERAL] how to tell master from replica

On Wed, Sep 25, 2013 at 5:11 PM, bricklen wrote: > > On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe > wrote: > >> Assuming a master & replica set up using streaming replication, hot >> standby, 9.3. >> >> I'd like to have a single script on both machin

Re: [GENERAL] How do I find a trigger function that is raising notices?

On Thu, Sep 26, 2013 at 9:04 AM, Rob Richardson wrote: > Select * from pg_proc where lower(prosrc) = '%%' A slight revision should work: select distinct proname from pg_proc where prosrc ilike '%%';

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, Oct 2, 2013 at 9:01 AM, David Rysdam wrote: > I had some vague notion of tables "doing work" but really if it can load > one > partition into RAM and get most of my hits from there, it'd be a big > win. The same concept applies to the frequently-used indexes on that partition.

Re: [GENERAL] how to know cpu utilization using sql command

On Thu, Oct 3, 2013 at 1:50 AM, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > How to know cpu and memory utilization in postgres. > > We require this information to show it on our Application using any > postgres command or tool. > pg_activity works pretty well: https://github.

Re: [GENERAL] Fighting the autovacuumer (to prevent wraparound)

On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham wrote: > Hi all, > > We partition the data in postgres in a per-month basis and run a script > to delete old partitions. Does "delete" = "drop"? > Sometimes this script fails and the delete > doesn't happen because of a deadlock, today I notice

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: > HINT: To avoid a database shutdown, execute a full-database VACUUM in > "patnadbold". > ERROR: could not access status of transaction 33011 > DETAIL: could not open file "pg_clog/": No such file or directory > exit > > > After this i am a

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote: > > On Tue, Oct 8, 2013 at 8:36 PM, bricklen wrote: > >> >> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: >> >>> HINT: To avoid a database shutdown, execute a full-database VACUUM in >>&

Re: [GENERAL] Many thousands of partitions

On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk wrote: > Hello, > > I have question regarding one of caveats from docs: > http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html > > If you are using Postgresql 8.3 then you should consider upgrading to 9.3 instead. > "Partitioning using

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:25 AM, shailesh singh wrote: > yes i am executing psql to connect to this database. > > su - postgres > psql patnadbold > ..and then what? Does it immediately throw an error stating that you must issue a VACUUM?

[GENERAL] Re: [GENERAL] Forms for entering data into postgresql‏

On Wed, Oct 9, 2013 at 7:05 PM, Sudhir P.B. wrote: > I have developed an application using MS SQL. I have used MS Access for > creating forms to enter data into the database. I am thinking of changing > over to postgresql and would also like to use any other available open > source tool for creat

Re: [GENERAL] Need some help on Performance 9.0.4

On Sun, Oct 13, 2013 at 8:43 AM, akp geek wrote: > Sorry all not posting clear. I posted our postresql.conf file. What we > are having is very slow response from the database. > > would like to get some feedback about postgresql.conf file parameters that > I posted are causing the issue for perf

Re: [GENERAL] autovaccum task got cancelled

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu wrote: > Hello, > > I'm running an application (with programs in Perl) through pgpool 3.1 with > replication mode to two postgresql db servers (version 9.0.13). Recently, > I noticed that the following messages repeatedly showed in postgres log > files.

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin wrote: > Hi, > > I have Postgres full text search set up for my application and it's been > working great! However, my users would like their searches to turn up > parts of URLs. For example, they would like a search for "foobar" to turn > up a docum

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin wrote: > On 11/06/2013 01:47 PM, bricklen wrote: > >> >> On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin >> mailto:zev-pg...@strangersgate.com>> wrote: >> >> Hi, >> >> I have Postgr

Re: [GENERAL] pg_log filling up with false logs

On Mon, Nov 11, 2013 at 6:32 PM, Shree wrote: > Also pg_ctl command to stop, restart does not seem to have any effect. > Does anyone have any ideas? > I can't speak to what issued the shutdown command, but have you tried "pg_ctl restart -m fast" to expedite the restart (if that is indeed what y

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong wrote: > We'd like to seek out your expertise on postgresql regarding this error > that we're getting in an analytical database. > > Some specs: > proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on. > memory: 48GB > OS: Oracle Enterp

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

On Mon, Nov 18, 2013 at 8:30 PM, Brian Wong wrote: > I've tried any work_mem value from 1gb all the way up to 40gb, with no > effect on the error. I'd like to think of this problem as a server process > memory (not the server's buffers) or client process memory issue, primarily > because when w

Re: [GENERAL] Postgres as In-Memory Database?

On Mon, Nov 18, 2013 at 11:16 PM, Stefan Keller wrote: > > > I don't think there's any evidence that the Postgres developers ignore > > useful optimisations. What you're arguing is that the optimisation > > you have in mind isn't covered. > > No; my point is that I - and others like Stonebraker,

Re: [GENERAL] nested query vs left join: query planner very confused

On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam wrote: > > > At my client's location, the query is very slow (same table size, > similar hardware/config, although they are running 9.0.x and I'm on > 9.2.x). By "slow" I mean like an *hour*. 'explain' has this structure: > > Seq scan on mags >

Re: [GENERAL] unnest on multi-dimensional arrays

the liberty of adding your nifty function to the Postgresql Wiki at https://wiki.postgresql.org/wiki/Unnest_multidimensional_array Feel free to edit directly or suggest any changes to it. Cheers, Bricklen

  1   2   3   >