Re: [GENERAL] Help on Index only scan

=?utf-8?Q?Ertan_K=C3=BC=C3=A7=C3=BCko=C4=9Flu?= writes: >>> I want to have an index only scan for my below query: >>> select autoinc, fileversion from updates where filename = 'Robox.exe' order >>> by autoinc desc; >> On 14 Aug 2017, at 01:15, Melvin Davidson wrote: >> As far as "Index only sca

Re: [GENERAL] Help on Index only scan

On Sun, Aug 13, 2017 at 7:37 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote: > > On 14 Aug 2017, at 01:15, Melvin Davidson wrote: > > > On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < > ertan.kucuko...@1nar.com.tr> wrote: > >> Hello, >> >> My table details: >> robox=# \dS+ updates >>

Re: [GENERAL] Help on Index only scan

> On 14 Aug 2017, at 01:15, Melvin Davidson wrote: > > >> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu >> wrote: >> Hello, >> >> My table details: >> robox=# \dS+ updates >>Table "public.updates" >> Column | Type |

Re: [GENERAL] Help on Index only scan

On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote: > Hello, > > My table details: > robox=# \dS+ updates >Table "public.updates" > Column | Type | Modifiers > | Storage | Sta

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Ken Tanzer writes: >> FWIW, the business with making and editing a list file should work just >> fine with a tar-format dump, not only with a custom-format dump. The >> metadata is all there in either case. > The pg_dump doc page kinda suggests but doesn't quite say that you can't > re-order tar

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 05:59 PM, Ken Tanzer wrote: Not sure why just know that if I stay within the guidelines it works, if I do not its does not work:) That's fair enough, leaving aside the curiosity part. Usually though the things you can't do just aren't allowed. It's easier to overlook

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > > So I can switch to Custom format for future backups. But regarding the > > existing backups I have in Tar format, is there any way to successfully > > restore them? > > FWIW, the business with making and editing a list file should work just > fine with a tar-format dump, not only with a cust

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Ken Tanzer writes: > ...The rest of the DB is fine, but tbl_payment has 0 rows. I believe this is > because tbl_payment has a constraint that calls a function has_perm() that > relies on data in a couple of other tables, and that tbl_payment is being > restored before those tables. I was able to

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 6:21 PM, Ken Tanzer wrote: > I do get the "make \d show relevant information" argument and that is one >> that seems easier to solve... >> > > Maybe I'm missing something, but I'm not sure how you'd solve this or > change what \d shows for a table. Right now I get to see t

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I do get the "make \d show relevant information" argument and that is one > that seems easier to solve... > Maybe I'm missing something, but I'm not sure how you'd solve this or change what \d shows for a table. Right now I get to see this in my \d: "authorized_approvers_only" CHECK (approve

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:59 PM, Ken Tanzer wrote: > I can't really make this an FK. I can (and probably will) put this into a >>> trigger. Although it seems like an extra layer of wrapping just to call a >>> function. I'm curious if there's any conceptual reason why constraints >>> couldn't (a

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I can't really make this an FK. I can (and probably will) put this into a >> trigger. Although it seems like an extra layer of wrapping just to call a >> function. I'm curious if there's any conceptual reason why constraints >> couldn't (as an option) be restored after all the data is loaded

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 05:15 PM, Ken Tanzer wrote: Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments:: Ideally figure out how to write an actual FK constraint - othe

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:49 PM, David G. Johnston wrote: On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce >wrote: ​i​ ndeed, any sort of constraint that invokes a function call which looks at other tables could later be invalidated if those other tables change, and

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce wrote: > ​i​ > ndeed, any sort of constraint that invokes a function call which looks at > other tables could later be invalidated if those other tables change, and > postgres would be none the smarter. the same goes for trigger based > checks. > ​

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > Aside from being a bit more verbose there is nothing useful that writing > this as "CHECK function()" provides that you don't also get by writing > "CREATE TRIGGER". > I agree you get the same result. It may be a minor issue, but for me it is convenient to see the logic spelled out when using

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:32 PM, David G. Johnston wrote: On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer >wrote: From the docs: https://www.postgresql.org/docs/9.6/static/sql-createtable.html

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer wrote: > From the docs: >> https://www.postgresql.org/docs/9.6/static/sql-createtable.html >> "Currently, CHECK expressions cannot contain subqueries nor refer to >> variables other than columns of the current row. The system column tableoid >> may be re

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:15 PM, Ken Tanzer wrote: I can't really make this an FK. I can (and probably will) put this into a trigger. Although it seems like an extra layer of wrapping just to call a function. I'm curious if there's any conceptual reason why constraints couldn't (as an option) be restore

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments:: Ideally figure out how to write an actual FK constraint - otherwise use > triggers. I can't really make this an

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 03:35 PM, Ken Tanzer wrote: On 9.3.17, I tried to restore a tar from pg_dump. It failed thusly: bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 10

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 3:35 PM, Ken Tanzer wrote: > I believe this is because tbl_payment has a constraint that calls a > function has_perm() that relies on data in a couple of other tables > ​Indeed this is the cause. That configuration is not supported. If you need to lookup values in other

Re: [GENERAL] Help with terminology to describe what my software does please?

>> Cluster comparison would only occur if you have two or more clusters on >> the same server, although it's possible to compare across servers, > > > Explain, because as I understand it a server = one cluster: > I think he was using server in the server=one machine sense, ie a single machine/serv

Re: [GENERAL] Help with terminology to describe what my software does please?

> > > Cluster comparison would only occur if you have two or more clusters on > the same server, although it's possible to compare across servers, > but that would involve a lot more work. AFAIK, the only differences for a > cluster would be: > 1. PostgreSQL version > 2. path to database > 3. datab

Re: [GENERAL] Help with terminology to describe what my software does please?

On 05/28/2017 07:53 AM, Melvin Davidson wrote: Cluster comparison would only occur if you have two or more clusters on the same server, although it's possible to compare across servers, Explain, because as I understand it a server = one cluster: https://www.postgresql.org/docs/9.6/s

Re: [GENERAL] Help with terminology to describe what my software does please?

Neil Anderson writes: > I guess I don't know what is the most common way to say that it > compares everything but the data. Any suggestions from your > experience? FWIW, I think it's pretty common to use "schema" in an abstract way to mean "the structure of your database", ie everything but the d

Re: [GENERAL] Help with terminology to describe what my software does please?

On Sun, May 28, 2017 at 9:51 AM, Adrian Klaver wrote: > On 05/28/2017 05:49 AM, Neil Anderson wrote: > >> Hi, >> >> I'm working on a tool that can compare the properties of Postgres >> objects from different instances, finding the differences and >> outputting the update SQL. >> >> It can compare

Re: [GENERAL] Help with terminology to describe what my software does please?

On 05/28/2017 05:49 AM, Neil Anderson wrote: Hi, I'm working on a tool that can compare the properties of Postgres objects from different instances, finding the differences and outputting the update SQL. It can compare objects that are defined at the cluster, database or schema level. As such I

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Magnus Hagander writes: > On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz wrote: >> Not sure whether we should *fix* this or not on RPM side. This may break >> some of the existing installations, right? > Changing that in a minor version seems like a *really* bad idea, because > things *will* br

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/16/2017 01:00 AM, Devrim Gündüz wrote: Hi, On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: https://redmine.postgresql.org/issues/2409 Not sure whether we should *fix* this or not on RPM side. This may break some of the existing installations, right? I'm not objecting, just asking

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz wrote: > > Hi, > > On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: > > https://redmine.postgresql.org/issues/2409 > > Not sure whether we should *fix* this or not on RPM side. This may break > some > of the existing installations, right? > > I'

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: > https://redmine.postgresql.org/issues/2409 Not sure whether we should *fix* this or not on RPM side. This may break some of the existing installations, right? I'm not objecting, just asking for opinions. Regards, -- Devrim Gündüz Ente

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 4:45 PM, Adrian Klaver wrote: > On 05/15/2017 01:40 PM, Ken Tanzer wrote: > > > >> But let me ask, is there a big warning about this somewhere I missed? >> Can the 9.2 updates do something to fix this, or at least create a warning >> or an RPMNEW file? I'm happy this is a

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/15/2017 01:40 PM, Ken Tanzer wrote: But let me ask, is there a big warning about this somewhere I missed? Can the 9.2 updates do something to fix this, or at least create a warning or an RPMNEW file? I'm happy this is a cloud server and that I worked on a copy. However, in differen

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 16:34 -0400, Tom Lane wrote: > > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > > psql: could not connect to server: Connection refused > >    Is the server running locally and accepting > >    connections on Unix domain socket > > "/var/run/postgresql/.s.PGSQL.5432

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

> > >> Workarounds: >> >> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows >> the >> old socket directory. >> > > That was where I was going until I saw this in the OP: > > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > psql: could not connect to server: Connection refused >

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Adrian Klaver writes: > On 05/15/2017 01:10 PM, Devrim Gündüz wrote: >> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows the >> old socket directory. > That was where I was going until I saw this in the OP: > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > psql: could not con

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/15/2017 01:10 PM, Devrim Gündüz wrote: Hi, On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote: Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to install PGDG 9.6 alongside the already-running 9.2. After installing the 9.6 packages (and even before doing an initd

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote: > Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2.  After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Ken Tanzer writes: > Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2. After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local connection to the 9.2 server. Instead

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 12:55:48PM -0700, Ken Tanzer wrote: > Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2. After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local

Re: [GENERAL] Help with Trigger

Thank you for your suggestion which solved the problem. Much better solution that what I was trying to accomplish. Much smaller table to query since it only has one entry per user. Clifford On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver wrote: > On 12/28/2016 07:06 PM, Clifford Snow wrote: > >>

Re: [GENERAL] Help with Trigger

On 12/28/2016 07:06 PM, Clifford Snow wrote: I'm trying to write a trigger (my first) to update another table if the user_id is new. But I'm getting a index exception that the user_id What is the actual error message? already exists. I'm picking up data from another feed which gives provides

Re: [GENERAL] help with moving tablespace

> Bonus question: I found an ER diagram of some of the pg_* tables at h > ttp://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL. Is there an > ERD of all of them so a person can better understand how to use them > when one must? I suppose the same question applies to > information_schema since

Re: [GENERAL] help with moving tablespace

> On Thu, Nov 17, 2016 wrote: > > On Thu, Nov 17, 2016 at 9:16 AM, wrote: > > First, the above works only *most* of the time in our testing on multiple > > servers. When it fails, it's because not everything was moved out of the > > old tablespace and I don't understand why. An "ls $PGDATA/ourd

Re: [GENERAL] help with moving tablespace

On Thu, Nov 17, 2016 at 9:16 AM, wrote: > First, the above works only *most* of the time in our testing on multiple > servers. When it fails, it's because not everything was moved out of the > old tablespace and I don't understand why. An "ls $PGDATA/ourdb/PG*/" shows > files are still present. A

Re: [GENERAL] Help with slow query - Pgsql 9.2

On Mon, Sep 5, 2016 at 6:53 PM, Patrick B wrote: > Hi guys, > > I got this query: > >> SELECT id,jobid,description,serialised_data >> FROM logtable >> WHERE log_type = 45 >> AND clientid = 24011 >> ORDER BY gtime desc > > What is really going to help you here is multicolumn index on (clientid, l

Re: [GENERAL] Help on recovering my standby

I had the same issue... A slave server had missing wal_files... and it wasn't synced. I had to re-sync all the DB, by running the pg_basebackup command So.. basically, what I did is: 1 - Ensure that the wal_files are being inserted into the slave 2 - Backup the recovery.conf, postgresql.conf

Re: [GENERAL] Help on recovering my standby

On Wed, Jun 22, 2016 at 12:22 PM, Alan Hodgson wrote: > On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > > Hi I have my standby (streaming replication) down due to missing wal > files. > > You would see the same error in the logs stating "cannot find the wal > file > > ..." What

Re: [GENERAL] Help on recovering my standby

On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > Hi I have my standby (streaming replication) down due to missing wal files. > You would see the same error in the logs stating "cannot find the wal file > ..." What is the best way to get it going so that when we switch between > st

Re: [GENERAL] Help needed structuring Postgresql correlation query

Thanks for that, looks like something to sink my teeth into ! On 21 June 2016 at 13:29, Alban Hertroys wrote: > >> On 19 Jun 2016, at 10:58, Tim Smith wrote: >> >> Hi, >> >> My postgresql-fu is not good enough to write a query to achieve this >> (some may well say r is a better suited tool to ac

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

Thank you David. -Allan. On Mon, Jun 20, 2016 at 11:19 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau wrote: > >> I have an xml document from which I would like to extract the contents of >> several elements. >> >> I would like to use x

Re: [GENERAL] Help needed structuring Postgresql correlation query

> On 19 Jun 2016, at 10:58, Tim Smith wrote: > > Hi, > > My postgresql-fu is not good enough to write a query to achieve this > (some may well say r is a better suited tool to achieve this !). > > I need to calculate what I would call a correlation window on a time > series of data, my table l

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau wrote: > I have an xml document from which I would like to extract the contents of > several elements. > > I would like to use xpath to extract the contents of "name" from the xml > document shown below. > > WITH x AS > ( > SELECT > ' > http://uniprot.

Re: [GENERAL] HELP! Uninstalled wrong version of postgres

El 24/03/16 a las 14:19, Howard News escribió: Hi, I uninstalled the wrong version of postgres on Ubuntu using apt-get remove postgresql-9.0, convinced that this was an old unused version. You guess the rest... The data files still appear to be there, all 485GB of them. Can these be restored?

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/9/15 7:05 PM, Andreas Kretschmer wrote: I'm really newbie to PostgreSQL but the boss pushed me to handle it >and implement it in production f*&%*$%%$#%$#&# (forgive me) >They don't hire a database expert, I don't know why. You can learn that. PostgreSQL is really, really great. Btw.: i kno

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

> FattahRozzaq hat am 10. Dezember 2015 um 01:27 > geschrieben: > > > Hi John, > > I really don't know why I should keep the wal archives. That's the problem! But that's your part, not our. If you need a Backup with PITR-capability you have to create a so called basebackup and continously W

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 04:27 PM, FattahRozzaq wrote: Hi John, I really don't know why I should keep the wal archives. So who set up the archiving and why? Is archive recovery set up on the standby?: http://www.postgresql.org/docs/9.4/interactive/archive-recovery-settings.html I implement streaming

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Hi John, Really thanking you for spend time typing and responding my email. I think the archive_command returns success, I can see the archive directory piling up 16MB every 2 minutes. Maybe the pgarchivecleanup is the solution to cleanup the contents of archive folder? How to properly do it? What

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/9/2015 4:27 PM, FattahRozzaq wrote: I really don't know why I should keep the wal archives. I implement streaming replication into 1 server (standby server). I'm really newbie to PostgreSQL but the boss pushed me to handle it and implement it in production f*&%*$%%$#%$#&# (forgive me) They

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 04:38 PM, FattahRozzaq wrote: Quick information, After I realize, the line "archive_command=/bin/true" is a bad decision, I have revert it back. Now I'm really confused and panic. I don't know what to do, and I don't really understand the postgresql.conf I'm a network engineer, I s

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Quick information, After I realize, the line "archive_command=/bin/true" is a bad decision, I have revert it back. Now I'm really confused and panic. I don't know what to do, and I don't really understand the postgresql.conf I'm a network engineer, I should handle the network and also postgresql d

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Hi John, I really don't know why I should keep the wal archives. I implement streaming replication into 1 server (standby server). I'm really newbie to PostgreSQL but the boss pushed me to handle it and implement it in production f*&%*$%%$#%$#&# (forgive me) They don't hire a database expert, I do

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/8/2015 4:55 PM, FattahRozzaq wrote: ...I want to limit the total size use by WAL archive to around 200-400 GB...? for what purpose are you keeping a wal archive ? if its for PITR (point in time recovery), you need ALL WAL records since the start of a base backup up to the point in time

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 11:15 AM, Alan Hodgson wrote: On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote: archive_mode = on archive_command = 'cp -i %p /home/postgres/archive/master/%f' The WAL archive folder is at /home/postgres/archive/master/, right? This directory consumes around 750GB o

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote: > archive_mode = on > archive_command = 'cp -i %p /home/postgres/archive/master/%f' > > > The WAL archive folder is at /home/postgres/archive/master/, right? > This directory consumes around 750GB of Disk-1. > Each segment in the /ho

Re: [GENERAL] Help me recovery databases.

Thank you very much. Well done. Backups it's all))  01.06.2015, 03:05, "Melvin Davidson" :If you have a pg_dumpall, or a pg_dump of your databases, you "might" be able to get your data back by doing the following.1. If your data directory is corrupted or still exists, rename it.2. Make copies of yo

Re: [GENERAL] Help me recovery databases.

If you have a pg_dumpall, or a pg_dump of your databases, you "might" be able to get your data back by doing the following. 1. If your data directory is corrupted or still exists, rename it. 2. Make copies of your postgresql.conf & pg_hba.conf if you still have them. 3. use initdb to recreate the

Re: [GENERAL] Help me recovery databases.

"base" is where all the data files are located, so the answer is most likely 'no'. On 05/31/15 15:11, Evi-M wrote: Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb) This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have

Re: [GENERAL] Help with slow table update

On 4/19/15 9:53 PM, Tim Uckun wrote: On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov mailto:pawel.vese...@gmail.com>> wrote: I found some dangling prepared transactions How do you find and remove these? SELECT * FROM pg_prepared_xacts; ROLLBACK PREPARED xid; -- Jim Nasby, Data Architec

Re: [GENERAL] Help with slow table update

On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov wrote: > I found some dangling prepared transactions How do you find and remove these?

Re: [GENERAL] Help with slow table update

> > [skipped] > > >> But remember that if you update or delete a row, removing it from an >>> index, the data will stay in that index until vacuum comes along. >>> >>> Also, there's no point in doing a REINDEX after a VACUUM FULL; >>> vacuum full rebuilds all the indexes for you. >

Re: [GENERAL] Help with slow table update

> > [skipped] > > > > This is where using sets becomes really tedious, as Postgres severely > lacks an upsert-like statement. > > I don't think there are joins allowed in UPDATE statement, so I will need > to use WITH query, right? > > Also, I'm not sure how LEFT JOIN will help me isolate and inser

Re: [GENERAL] Help with slow table update

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pawel Veselov Sent: Tuesday, April 14, 2015 8:01 PM To: Jim Nasby Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Help with slow table update [skipped] This is where using sets becomes

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 3:29 PM, Jim Nasby wrote: > On 4/14/15 4:44 PM, Pawel Veselov wrote: > >> On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby > > wrote: >> >> On 4/14/15 1:28 PM, Pawel Veselov wrote: >> >> >> I wonder if what I need to do, considering t

Re: [GENERAL] Help with slow table update

On 4/14/15 4:44 PM, Pawel Veselov wrote: On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby wrote: > On 4/14/15 1:28 PM, Pawel Veselov wrote: > >> >> I wonder if what I need to do, considering that I update a lot of "the >> same" rows as I process this queue, is to create a temp table, update >> the rows there, and then update the actual tables

Re: [GENERAL] Help with slow table update

On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this queue, is to create a temp table, update the rows there, and then update the actual tables once at the end... That's what I'd do. The other option would

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 7:37 PM, Jim Nasby wrote: > On 4/13/15 7:01 PM, Pawel Veselov wrote: > >> Cursors tend to make things slow. Avoid them if you can. >> >> >> Is there an alternative to iterating over a number of rows, where a >> direct update query is not an option? >> >> I really doubt

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 6:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov > wrote: > >> >> r_agrio_hourly - "good", r_agrio_total - "bad". >> >> Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual >> time=2.248..

Re: [GENERAL] Help with slow table update

On 4/13/15 7:01 PM, Pawel Veselov wrote: Cursors tend to make things slow. Avoid them if you can. Is there an alternative to iterating over a number of rows, where a direct update query is not an option? I really doubt that either the actual processing logic, including use of types has any

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov wrote: > > r_agrio_hourly - "good", r_agrio_total - "bad". > > Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual > time=2.248..2.248 rows=0 loops=1) >-> Index Scan using u_r_agrio_hourly on r_agrio_hourly > (cost=0.42..9

Re: [GENERAL] Help with slow table update

On Sun, Apr 12, 2015 at 5:40 PM, Jim Nasby wrote: > On 4/9/15 6:18 PM, Pawel Veselov wrote: > >> Hi. >> >> I have a plpgsql procedure that updates a few similar tables. >> for some reason, updates on one of the tables take a lot longer the >> updates on the other ones. The difference is, say, 7 s

Re: [GENERAL] Help with slow table update

On 4/9/15 6:18 PM, Pawel Veselov wrote: Hi. I have a plpgsql procedure that updates a few similar tables. for some reason, updates on one of the tables take a lot longer the updates on the other ones. The difference is, say, 7 seconds vs. 80 milliseconds. the procedure uses cursors and record v

Re: [GENERAL] Help with tokenization of age-ranges in full text search

Mason Hale wrote: > Hello, I've got a 9.3 database hosted at Heroku. > > I'm full text search to search for "group names" in part of my application, > and some of my group names are the names of youth sports age groups like > "Boys 9-10" or "Girls 11-12". > > I would like for a search for the ter

Re: [GENERAL] help troubleshooting invalid page header error

Cory Zue wrote: > I was able to get the database back to a normal functional state > using the zero_damaged_pages flag. However, after getting > everything working and starting to use the database again, I am > again getting "invalid page header" errors on a certain table. > > Does this imply the

Re: [GENERAL] help troubleshooting invalid page header error

Hi again, I was able to get the database back to a normal functional state using the zero_damaged_pages flag. However, after getting everything working and starting to use the database again, I am again getting "invalid page header" errors on a certain table. Does this imply there is a hardware i

Re: [GENERAL] help troubleshooting invalid page header error

(nevermind - it looks like the zero_damaged_pages setting only took for the duration of the session) On Fri, Dec 26, 2014 at 5:15 PM, Cory Zue wrote: > Hi Chiru, > > I am trying to pg_dump the database to have a snapshot of the current > state. I've turned on 'zero_damaged_pages' but pg_dump is

Re: [GENERAL] help troubleshooting invalid page header error

Hi Chiru, I am trying to pg_dump the database to have a snapshot of the current state. I've turned on 'zero_damaged_pages' but pg_dump is still failing with an "invalid page header" error - this time from what looks like a sequence object that is auto-setting IDs on a table. Any advice on how to r

Re: [GENERAL] help troubleshooting invalid page header error

Hi Cory, After recovering table turn off *zero_damaged_pages *parameter. On Fri, Dec 26, 2014 at 9:13 PM, Cory Zue wrote: > Hi all, > > Thanks for the responses. Chiru, I'm looking into your suggestion. > > Sameer, here is the kernel version info: > > Linux dimagi 2.6.32-431.20.5.el6.x86_64 #

Re: [GENERAL] help troubleshooting invalid page header error

Hi all, Thanks for the responses. Chiru, I'm looking into your suggestion. Sameer, here is the kernel version info: Linux dimagi 2.6.32-431.20.5.el6.x86_64 #1 SMP Wed Jul 16 05:26:53 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux Does that seem like it could be a problematic version? More generally -

Re: [GENERAL] help troubleshooting invalid page header error

On 23 Dec 2014 12:05, "Cory Zue" wrote: > > Hi all, > > Our postgres instance on one of our production machines has recently been returning errors of the form "DatabaseError: invalid page header in block 1 of relation base/16384/76623" from normal queries. I've been reading that these are often li

Re: [GENERAL] help troubleshooting invalid page header error

Hi Cory, We have *zero_damaged_pages* parameter in PostgreSQL configuration,by default it is set be *off*. To recover data from corrupted table,we can turn *on* this parameter as a super user and populate new table using dump or copy utility. Note : The damaged pages we can't recover from table,

Re: [GENERAL] Help Optimizing a Summary Query

Robert DiFalco wrote > I have users, friends, and friend_requests. I need a query that > essentially > returns a summary containing: > > * user (name, imageURL, bio, ...) > * Friend status (relative to an active user) >* Is the user a friend of the active user? >* Has the u

Re: [GENERAL] Help Optimizing a Summary Query

On Thu, Dec 11, 2014 at 6:52 PM, Robert DiFalco wrote: > Thanks Arthur. I don't think there is as big a different between BIGINT > and INTEGER as you think there is. In fact with an extended filesystem you > might not see any difference at all. > > As I put in the first emal I am using a GIST ind

Re: [GENERAL] Help Optimizing a Summary Query

Thanks Arthur. I don't think there is as big a different between BIGINT and INTEGER as you think there is. In fact with an extended filesystem you might not see any difference at all. As I put in the first emal I am using a GIST index on user.name. I was really more interested in the LEFT OUTER J

Re: [GENERAL] Help Optimizing a Summary Query

On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco wrote: > I'm sorry, I missed a JOIN on the second variation. It is: > > SELECT u.id, u.name, u.imageURL, u.bio, >CASE > WHEN f.friend_id IS NOT NULL THEN 'isFriend' > WHEN s.to_id IS NOT NULL THEN 'hasSentRequest' > WHEN r.to_id

Re: [GENERAL] Help Optimizing a Summary Query

I'm sorry, I missed a JOIN on the second variation. It is: SELECT u.id, u.name, u.imageURL, u.bio, CASE WHEN f.friend_id IS NOT NULL THEN 'isFriend' WHEN s.to_id IS NOT NULL THEN 'hasSentRequest' WHEN r.to_id IS NOT NULL THEN 'hasReceivedRequest' ELSE 'none' END AS 'f

Re: [GENERAL] Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers

David G Johnston writes: > Neil Tiffin-3 wrote >> Trying to wrap my head around postgresql 9.4 jsonb and would like some >> help figuring out how to do the following. >> >> Given the following example jsonb: >> >> ‘{“name1” : value1, “name2” : value2, “name3” : [int1, int2, int3] >> }’

Re: [GENERAL] Help related to Postgresql for RHEL 6.5

On 08/28/2014 09:14 PM, Yogesh. Sharma wrote: Dear David, Are you currently using PostgreSQL? Currently we are using PostgreSQL 8.1.18 version on RHEL 5.8. Now we plan to update this to PostgreSQL 9.0 version with RHEL6.5. As in verion 9.0 I found least Compatibilities. So what are the thi

  1   2   3   4   5   6   7   8   9   >