Re: [GENERAL] Fresh Restore - relation contains more than "max_fsm_pages"

On 28/07/11 18:13, Maton, Brett wrote: > postgresql v8.3.14 > > Can anyone help me identify why a vacuum on clean pg_restore database > would give this warning? > I've been told that the database was restored and not been touched > since, i.e. no inserts / updates or deletes. So I'm curious as to

Re: [GENERAL] How to implement autostart of postgres?

On 28/07/11 18:06, Gavrina, Irina wrote: > Yes, you are right, this is a question of database robustness. Honestly, this is the wrong approach to high availability. Don't try to make one machine unbreakable - you *cannot* do it. Use multi-machine failover between a master and a replica with STONI

Re: [GENERAL] issue with pg_restore

Nigel Heron writes: > On 11-07-28 09:41 AM, Tom Lane wrote: >> I'm wondering if it could be the same bug reported two days ago: >> http://archives.postgresql.org/message-id/201107270042.22427.jul...@mehnle.net >> Have you got standard_conforming_strings turned on? > That must be it! I do have sta

Re: [GENERAL] issue with pg_restore

On 11-07-28 09:41 AM, Tom Lane wrote: Adrian Klaver writes: On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: I'm trying to restore a backup into a database with a new name It doesn't work .. pg_restore claims to be creating tables, indexes, etc. and there are no errors in the output

Re: [GENERAL] I'm in the depths of a CAST nightmare and I can't work my out

On Thursday, July 28, 2011 11:22:44 am Wells Oliver wrote: > Hi folks. On 8.4 here, and I can't solve this tedious thing. I have two > servers. One of them, all the casts are properly configured. I've > pg_dump'ed the DB there, and restored it. And I get tons of errors about > incompatible types.

Re: [GENERAL] List Functions and Code

I'm pretty sure there is a way to get the source from either the schema or catalog, but I do not know what it is. However, if you are dealing with a one-time need you could always just pg_dump the schema as text and search through it using regex/grep tools (or even just "find") David J On Ju

Re: [GENERAL] using xmin in a query?

Michael Nolan writes: > So, a <> operator (either xid,xid or xid,integer) would need to be > implemented using the hash opclass, correct? No, it's unrelated to the opclass. It'd be worth marking it as the negator of the equality operator, but otherwise it'd really be unconnected to anything else

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 5:36 PM, Tom Lane wrote: > Michael Nolan writes: > > It also appears you cannot group on a column of type xid. > > You can in 8.4 and up. Previous versions only know how to GROUP BY > sortable columns, which requires a btree opclass, which xid doesn't > have and really c

Re: [GENERAL] using xmin in a query?

Michael Nolan writes: > It also appears you cannot group on a column of type xid. You can in 8.4 and up. Previous versions only know how to GROUP BY sortable columns, which requires a btree opclass, which xid doesn't have and really can't have because it doesn't have a linear ordering. There is

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 5:09 PM, Tom Lane wrote: > Michael Nolan writes: > > It seems like we're being inconsistent here in allowing 'where xid = > > integer' but not allowing 'where xid != integer'. > > Well, if you look into pg_operator you'll soon find that there are > exactly two built-in op

Re: [GENERAL] using xmin in a query?

Michael Nolan writes: > It seems like we're being inconsistent here in allowing 'where xid = > integer' but not allowing 'where xid != integer'. Well, if you look into pg_operator you'll soon find that there are exactly two built-in operators that accept type xid: "=(xid,xid)" and "=(xid,integer)

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 2:27 PM, Tom Lane wrote: > Michael Nolan writes: > > Why does this query succeed: > > select count(*) from tablename where xmin = 2 > > > while this query fails: > > > select count(*) from tablename where xmin != 2 > > It told you why not: > > > ERROR: operator does not

Re: [GENERAL] using xmin in a query?

Michael Nolan writes: > Why does this query succeed: > select count(*) from tablename where xmin = 2 > while this query fails: > select count(*) from tablename where xmin != 2 It told you why not: > ERROR: operator does not exist: xid <> integer You could do "where not (xmin = 2)", I suppose

Re: [GENERAL] How to implement autostart of postgres?

On Thu, Jul 28, 2011 at 5:06 AM, Gavrina, Irina wrote: > Thank you all for your answers. > > I’m using the Red Hat 5 OS. So I have no the ‘upstart’ utility at my > disposal. Unfortunately. > > > > Yes, you are right, this is a question of database robustness. You know, If > any of postgres process

Re: [GENERAL] using xmin in a query?

On 07/28/2011 03:09 PM, Michael Nolan wrote: On Thu, Jul 28, 2011 at 12:23 PM, Andy Colson > wrote: On 7/28/2011 11:40 AM, Michael Nolan wrote: Why does this query succeed: select count(*) from tablename where xmin = 2 while this que

[GENERAL] I'm in the depths of a CAST nightmare and I can't work my out

Hi folks. On 8.4 here, and I can't solve this tedious thing. I have two servers. One of them, all the casts are properly configured. I've pg_dump'ed the DB there, and restored it. And I get tons of errors about incompatible types. For example, a view w/ some UNION statements blows up: UNION/INT

Re: [GENERAL] using xmin in a query?

On Thu, Jul 28, 2011 at 12:23 PM, Andy Colson wrote: > On 7/28/2011 11:40 AM, Michael Nolan wrote: > >> Why does this query succeed: >> >> select count(*) from tablename where xmin = 2 >> >> while this query fails: >> >> select count(*) from tablename where xmin != 2 >> >> > You probably want <>.

[GENERAL] pg_largeobject vs pg_toast_XXXX

After some changes on my DB I notice that where I used to have a large pg_toast_X table, I now have a large pg_largeobject table. Can't find an explanation of the difference between the two, could someone enlighten me?

Re: [GENERAL] using xmin in a query?

On 7/28/2011 11:40 AM, Michael Nolan wrote: Why does this query succeed: select count(*) from tablename where xmin = 2 while this query fails: select count(*) from tablename where xmin != 2 You probably want <>. select count(*) from tablename where xmin <> 2 -Andy -- Sent via pgsql-gener

[GENERAL] How to implement autostart of postgres?

Thank you all for your answers. I'm using the Red Hat 5 OS. So I have no the 'upstart' utility at my disposal. Unfortunately. Yes, you are right, this is a question of database robustness. You know, If any of postgres processes died the postmaster process will again start up it. The main w

[GENERAL] Fresh Restore - relation contains more than "max_fsm_pages"

postgresql v8.3.14 Can anyone help me identify why a vacuum on clean pg_restore database would give this warning? I've been told that the database was restored and not been touched since, i.e. no inserts / updates or deletes. So I'm curious as to why it appears to have been created with excessive

[GENERAL] using xmin in a query?

Why does this query succeed: select count(*) from tablename where xmin = 2 while this query fails: select count(*) from tablename where xmin != 2 The latter will generate an error message (using 9.0.4, but it does not seem to be version specific): ERROR: operator does not exist: xid <> intege

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 10:36 AM, Chris Travers wrote: > On Thu, Jul 28, 2011 at 8:23 AM, Merlin Moncure wrote: >> >> This function is an absolute no-go if the string literal is coming >> from untrusted source, and any robust defenses would ruin the intended >> effect of the function.  There are

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 8:23 AM, Merlin Moncure wrote: > > This function is an absolute no-go if the string literal is coming > from untrusted source, and any robust defenses would ruin the intended > effect of the function.  There are a number of nasty ways you can (at > minimum) DOS your databas

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 8:08 AM, David Johnston wrote: > At best, based upon the example using "current_timestamp()", you could only > mark it as being stable, right? > > Also not mentioned; what risk is there of this function being hacked?  It > places the supplied data within a "SELECT  ()

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 10:08 AM, David Johnston wrote: > > Merlin Moncure writes: >> Couple points: >> *) why a special case for boolean values? > > That seemed weird to me too ... > >> *) this should be immutable > > What if the passed expression is volatile?  Better to be safe. > > ---

Re: [GENERAL] eval function

Merlin Moncure writes: > Couple points: > *) why a special case for boolean values? That seemed weird to me too ... > *) this should be immutable What if the passed expression is volatile? Better to be safe. - At best, based upon the example using "current_ti

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 4:18 AM, Sim Zacks wrote: > I need an eval function that will evaluate a valid SQL expression and return > the value. > > I've seen variations of  this asked before with no real answer. > > I wrote a function to handle it, but it looks like there should be a better > way to

Re: [GENERAL] eval function

Merlin Moncure writes: > Couple points: > *) why a special case for boolean values? That seemed weird to me too ... > *) this should be immutable What if the passed expression is volatile? Better to be safe. regards, tom lane -- Sent via pgsql-general mailing list (p

Re: [GENERAL] eval function

On Thu, Jul 28, 2011 at 6:18 AM, Sim Zacks wrote: > I need an eval function that will evaluate a valid SQL expression and return > the value. > > I've seen variations of  this asked before with no real answer. > > I wrote a function to handle it, but it looks like there should be a better > way to

Re: [GENERAL] List Functions and Code

Hello 2011/7/28 Rebecca Clarke : > Hi > I want to search and list all the functions in a database that reference a > particular table within its code. Is there a way to do this? > I can list all the functions from pg_proc, however there is nothing there > which provides the code of the function, s

Re: [GENERAL] List Functions and Code

On Thursday, July 28, 2011 6:31:32 am Rebecca Clarke wrote: > Hi > > I want to search and list all the functions in a database that reference a > particular table within its code. Is there a way to do this? > > I can list all the functions from pg_proc, however there is nothing there > which prov

Re: [GENERAL] issue with pg_restore

Adrian Klaver writes: > On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: >> I'm trying to restore a backup into a database with a new name >> It doesn't work .. pg_restore claims to be creating tables, indexes, >> etc. and there are no errors in the output. It only takes a few seconds >>

Re: [GENERAL] issue with pg_restore

On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote: > Hi list, > I'm trying to restore a backup into a database with a new name > > the dump was done on a 8.4 server with: > pg_dump -F c -f bakfile olddb > > i'm trying to restore it with: > createdb newdb; pg_restore -v --jobs=4 --disable-t

[GENERAL] List Functions and Code

Hi I want to search and list all the functions in a database that reference a particular table within its code. Is there a way to do this? I can list all the functions from pg_proc, however there is nothing there which provides the code of the function, so therefore I can't query if it mentions a

Re: [GENERAL] postgres unable to start

Hallo, some info: Postgres 8.4 Ubuntu 10.04.1 Linux hdmisv03 2.6.32-24-server #39-Ubuntu SMP Wed Jul 28 06:21:40 UTC 2010 x86_64 GNU/Linux and we installed Postgres through atp. The user wanted just to kill a connection from another user on a database. He did not kill the whole server process

Re: [GENERAL] postgres unable to start

On 28/07/2011 7:51 PM, tommaso wrote: Hi All, one of our users killed a postgres process with kill -9 "PID" after that the DB server is not longer able to start. There is a lot of information missing from this question, though you did include the log entries (thanks). Please see this for the

[GENERAL] postgres unable to start

Hi All, one of our users killed a postgres process with kill -9 "PID" after that the DB server is not longer able to start. Here is the log: Jul 28 13:06:46 hdmisv03 postgres[7916]: [1790-1] user=,db=,host= DEBUG: 0: server process (PID 7918) exited with exit code 0 Jul 28 13:06:46 hdmisv03

[GENERAL] eval function

I need an eval function that will evaluate a valid SQL expression and return the value. I've seen variations of this asked before with no real answer. I wrote a function to handle it, but it looks like there should be a better way to do this (see below). My use case is a table with date ran

[GENERAL] Statistics about Streaming Replication deployments in production

Hi all, We, at Avaya India, have been using postgres for a few years and are very happy with the stability and performance of the system. We would want to utilise the newly released streaming replication feature to build a master-(multiple)slave based geographically redundant setup . We ship to our