Re: [GENERAL] Similarity Search with Wildcards
On 02/28/2013 06:12 PM, John R Pierce wrote: > On 2/27/2013 10:35 PM, Ghislain Hachey wrote: >> I have a varchar column with content such as "Client Name - Brief >> Description of Problem" (it's a help desk ticket system). I want to >> generate reports by clients and the only thing I can base my query on >> is this column. The client names often contain typos or are entered >> slightly differently. I installed the pg_trgm extension and it almost >> does what I want. The problem is that it searches the similarity of >> the whole field and not just the client name resulting in not so >> similar searches (I include my query below). > > > why isn't client name a separate field?? thats the logical approach I know, but the system and the workflow of the staff is already in place. I was hoping to get something quick with minimal changes. I was also hoping to understand more how wildcards can be used with pg_trgm. Otherwise, I will add a field and modify the app. Thanks, -- GH
Re: [GENERAL] Similarity Search with Wildcards
I'm not sure about the indexing and performance impacts, but I think you
could use SUBSTRING with a regex to pull out the client name, and then
match on that.
SELECT substring('Client Name - Description' FROM '^(.*) [-]');
substring
-
Client Name
On Thu, Feb 28, 2013 at 12:02 AM, Ghislain Hachey wrote:
> On 02/28/2013 06:12 PM, John R Pierce wrote:
>
> On 2/27/2013 10:35 PM, Ghislain Hachey wrote:
>
> I have a varchar column with content such as "Client Name - Brief
> Description of Problem" (it's a help desk ticket system). I want to
> generate reports by clients and the only thing I can base my query on is
> this column. The client names often contain typos or are entered slightly
> differently. I installed the pg_trgm extension and it almost does what I
> want. The problem is that it searches the similarity of the whole field and
> not just the client name resulting in not so similar searches (I include my
> query below).
>
>
>
> why isn't client name a separate field?? thats the logical approach
>
>
> I know, but the system and the workflow of the staff is already in place.
> I was hoping to get something quick with minimal changes. I was also hoping
> to understand more how wildcards can be used with pg_trgm. Otherwise, I
> will add a field and modify the app.
>
> Thanks,
>
> --
> GH
>
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing
list
to
learn more about AGENCY or
follow the discussion.
Re: [GENERAL] Poor performance when using a window function in a view
On Wed, Feb 27, 2013 at 10:18 PM, Merlin Moncure wrote: > On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks > wrote: > > Hi. Sorry if this is repetitive, I tried posting to pgsql-performance > first > > but I think it got stuck in moderation. > > > > I'm trying to create a view that uses a window function, but it seems > that > > Postgres is unable to optimize it. Here's a reproduction of my situation > > with 9.2.2: > > > > --- > > > > drop table if exists values cascade; > > > > create table values ( > > fkey1 integer not null, > > fkey2 integer not null, > > fkey3 integer not null, > > value float not null, > > constraint values_pkey primary key (fkey1, fkey2, fkey3) > > ); > > > > -- This is kind of hacky, but it roughly resembles the size and > distribution > > of my dataset. > > insert into values select distinct on (fkey1, fkey2, fkey3) > > i / 12 + 1 as fkey1, > > i % 4 + 1 as fkey2, > > ceil(random() * 10) as fkey3, > > random() * 2 - 1 as value from generate_series(0, 19) i; > > > > create or replace view values_view as > > select fkey1, fkey3, > > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > > from ( > > select fkey1, fkey3, > > cast(sum((case when (value > 0.0) then 4 else 1 end)) as double > > precision) as derived1, > > sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) > as > > derived2 > > from values > > group by fkey1, fkey3 > > ) as t1; > > > > -- This query requires a sequential scan on values, though all the data > it > > needs could be found much more efficiently with an index scan. > > explain analyze select * from values_view where fkey1 = 1263; > > > > --- > > > > Can anyone suggest a way to rewrite this query? Or if postgres isn't > capable > > of optimizing this right now, is there a workaround of some kind? This > is a > > view I'd like to be able to join a smaller table against. > > this comes up a lot. only way to expose as a view is to push the query > into a set returning function which you then wrap into a view. > downside is that any query except on fkey1/fkey 2 will have to fully > materialize view. > > merlin > What would that look like? I've googled around for an example of what you're talking about, but I'm not finding anything. I think I know how to write a SQL function that will return a set of rows given a fkey1 value, but I don't see how I'd turn that into a view...? Thanks!
Re: [GENERAL] Poor performance when using a window function in a view
Chris Hanks writes: > create or replace view values_view as > select fkey1, fkey3, > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > from ( > select fkey1, fkey3, > cast(sum((case when (value > 0.0) then 4 else 1 end)) as double > precision) as derived1, > sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) as > derived2 > from values > group by fkey1, fkey3 > ) as t1; > -- This query requires a sequential scan on values, though all the data it > needs could be found much more efficiently with an index scan. > explain analyze select * from values_view where fkey1 = 1263; To use the outer WHERE clause as an index constraint, postgres would have to prove that scanning only the rows with fkey1 = 1263 would still find all the rows that would get examined by the window functions --- and in this case, it's not only the window functions that make that less than obvious, but the grouped aggregates in the sub-select below them. There's not nearly that amount of intelligence in the system about window functions, as yet. So you'll have to write out the query longhand and put the WHERE clause at the lower level, if you want this optimization to happen. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql log file not getting update
My postgresql-9.0-main.log file has suddenly stopped getting updated. I do not know why it stopped all of a sudden. We made a slight modification where changed in the postgresql.conf param: From log_connections = off log_disconnections = off To log_connections = on log_disconnections = on I'm on a debian environment w/ version 9.0.7. It's a single instance w/ no cluster on here. It was done w/a simple apt-get install back when, when it was a fresh server (we'll call this db1) I have an identical setup for another server (db4), and when I made the change to the same paramers in conf, it's updating the postgresql-9.0-main.log file. I reverted the connections and discon from on back to off, and did a postgresql reload as well as pg_reload_conf(). For both servers. db4 the log file is getting updated db1 nothing. I thought the file was locked or some perms may have changed and I deleted the postgresql-9.0-main.log file. It didn't create a new log file until i did a postgresql reload. It created the new file, but still nothing getting written to it. I know first thing people would say is to restart the instance, but restarting is not an option for me in this case. I've changed ucommented out this param #log_destination = 'stderr' to be stderr, and even syslog and did a reload and nothing. I know i should use log collector, but again, that also requires a restart as well, unless a reload will work? The way I reload is /etc/init.d/postgresql reload The distro for Debian is squeeze. Again, it works on my other server, this server it doesn't. Any one have any ideas, where I don't need to restart the pg instance? Thanks in advance. -Anson
Re: [GENERAL] postgresql log file not getting update
*Note when I change log_destination = 'syslog' it does log to the syslog file. When I changed to to log_destination = 'stderr' still nothing logged. commenting out doesn't do much either. I initially thought it would be a perms thing, but when I deleted the file and did a reload, postgres created the log file. So not sure what is going on exactly. Again, any help would be appreciated here to figure this out, where I don't need to restart the db. Thanks On Thu, Feb 28, 2013 at 11:19 AM, Anson Abraham wrote: > My postgresql-9.0-main.log file has suddenly stopped getting updated. I > do not know why it stopped all of a sudden. We made a slight modification > where changed in the postgresql.conf param: > From > log_connections = off > log_disconnections = off > > To > > log_connections = on > log_disconnections = on > > I'm on a debian environment w/ version 9.0.7. It's a single instance w/ > no cluster on here. It was done w/a simple apt-get install back when, when > it was a fresh server (we'll call this db1) > > I have an identical setup for another server (db4), and when I made the > change to the same paramers in conf, it's updating the > postgresql-9.0-main.log file. I reverted the connections and discon from > on back to off, and did a postgresql reload as well as pg_reload_conf(). > For both servers. db4 the log file is getting updated db1 nothing. I > thought the file was locked or some perms may have changed and I deleted > the postgresql-9.0-main.log file. It didn't create a new log file until i > did a postgresql reload. It created the new file, but still nothing getting > written to it. > > I know first thing people would say is to restart the instance, but > restarting is not an option for me in this case. > > I've changed > ucommented out this param > #log_destination = 'stderr' > > to be stderr, and even syslog and did a reload and nothing. > > > I know i should use log collector, but again, that also requires a restart > as well, unless a reload will work? > > The way I reload is /etc/init.d/postgresql reload > > The distro for Debian is squeeze. Again, it works on my other server, > this server it doesn't. Any one have any ideas, where I don't need to > restart the pg instance? > Thanks in advance. > -Anson >
Re: [GENERAL] postgresql log file not getting update
On Feb 28, 2013, at 19:01, Anson Abraham wrote: > *Note when I change > log_destination = 'syslog' > > it does log to the syslog file. > > When I changed to to log_destination = 'stderr' still nothing logged. > > commenting out doesn't do much either. I initially thought it would be a > perms thing, but when I deleted the file and did a reload, postgres created > the log file. So not sure what is going on exactly. > > Again, any help would be appreciated here to figure this out, where I don't > need to restart the db. > Thanks > > On Thu, Feb 28, 2013 at 11:19 AM, Anson Abraham > wrote: >> My postgresql-9.0-main.log file has suddenly stopped getting updated. I do >> not know why it stopped all of a sudden. We made a slight modification >> where changed in the postgresql.conf param: >> From >> log_connections = off >> log_disconnections = off >> >> To >> >> log_connections = on >> log_disconnections = on >> >> I'm on a debian environment w/ version 9.0.7. It's a single instance w/ no >> cluster on here. It was done w/a simple apt-get install back when, when it >> was a fresh server (we'll call this db1) >> >> I have an identical setup for another server (db4), and when I made the >> change to the same paramers in conf, it's updating the >> postgresql-9.0-main.log file. I reverted the connections and discon from on >> back to off, and did a postgresql reload as well as pg_reload_conf(). For >> both servers. db4 the log file is getting updated db1 nothing. I thought >> the file was locked or some perms may have changed and I deleted the >> postgresql-9.0-main.log file. It didn't create a new log file until i did a >> postgresql reload. It created the new file, but still nothing getting >> written to it. >> >> I know first thing people would say is to restart the instance, but >> restarting is not an option for me in this case. >> >> I've changed >> ucommented out this param >> #log_destination = 'stderr' >> >> to be stderr, and even syslog and did a reload and nothing. >> >> >> I know i should use log collector, but again, that also requires a restart >> as well, unless a reload will work? >> >> The way I reload is /etc/init.d/postgresql reload >> >> The distro for Debian is squeeze. Again, it works on my other server, this >> server it doesn't. Any one have any ideas, where I don't need to restart >> the pg instance? From the bin directory you can do: pg_ctl reload -D (followed by the data dierctory) >> Thanks in advance. >> -Anson >
Re: [GENERAL] postgresql log file not getting update
how is that different from /etc/init.d/postgres reload or for that matter select pg_reload_conf() ? Which I execute both and they don't work either. On Thu, Feb 28, 2013 at 12:43 PM, Chris wrote: > > > On Feb 28, 2013, at 19:01, Anson Abraham wrote: > > *Note when I change > log_destination = 'syslog' > > it does log to the syslog file. > > When I changed to to log_destination = 'stderr' still nothing logged. > > commenting out doesn't do much either. I initially thought it would be a > perms thing, but when I deleted the file and did a reload, postgres created > the log file. So not sure what is going on exactly. > > Again, any help would be appreciated here to figure this out, where I > don't need to restart the db. > Thanks > > On Thu, Feb 28, 2013 at 11:19 AM, Anson Abraham > wrote: > >> My postgresql-9.0-main.log file has suddenly stopped getting updated. I >> do not know why it stopped all of a sudden. We made a slight modification >> where changed in the postgresql.conf param: >> From >> log_connections = off >> log_disconnections = off >> >> To >> >> log_connections = on >> log_disconnections = on >> >> I'm on a debian environment w/ version 9.0.7. It's a single instance w/ >> no cluster on here. It was done w/a simple apt-get install back when, when >> it was a fresh server (we'll call this db1) >> >> I have an identical setup for another server (db4), and when I made the >> change to the same paramers in conf, it's updating the >> postgresql-9.0-main.log file. I reverted the connections and discon from >> on back to off, and did a postgresql reload as well as pg_reload_conf(). >> For both servers. db4 the log file is getting updated db1 nothing. I >> thought the file was locked or some perms may have changed and I deleted >> the postgresql-9.0-main.log file. It didn't create a new log file until i >> did a postgresql reload. It created the new file, but still nothing getting >> written to it. >> >> I know first thing people would say is to restart the instance, but >> restarting is not an option for me in this case. >> >> I've changed >> ucommented out this param >> #log_destination = 'stderr' >> >> to be stderr, and even syslog and did a reload and nothing. >> >> >> I know i should use log collector, but again, that also requires a >> restart as well, unless a reload will work? >> >> The way I reload is /etc/init.d/postgresql reload >> >> The distro for Debian is squeeze. Again, it works on my other server, >> this server it doesn't. Any one have any ideas, where I don't need to >> restart the pg instance? >> > > From the bin directory you can do: pg_ctl reload -D (followed by the data > dierctory) > > Thanks in advance. >> -Anson >> > >
Re: [GENERAL] postgresql log file not getting update
On 02/28/2013 08:19 AM, Anson Abraham wrote: My postgresql-9.0-main.log file has suddenly stopped getting updated. I do not know why it stopped all of a sudden. We made a slight modification where changed in the postgresql.conf param: From log_connections = off log_disconnections = off To log_connections = on log_disconnections = on I'm on a debian environment w/ version 9.0.7. It's a single instance w/ no cluster on here. It was done w/a simple apt-get install back when, when it was a fresh server (we'll call this db1) I have an identical setup for another server (db4), and when I made the change to the same paramers in conf, it's updating the postgresql-9.0-main.log file. I reverted the connections and discon from on back to off, and did a postgresql reload as well as pg_reload_conf(). For both servers. db4 the log file is getting updated db1 nothing. I thought the file was locked or some perms may have changed and I deleted the postgresql-9.0-main.log file. It didn't create a new log file until i did a postgresql reload. It created the new file, but still nothing getting written to it. I know first thing people would say is to restart the instance, but restarting is not an option for me in this case. I've changed ucommented out this param #log_destination = 'stderr' to be stderr, and even syslog and did a reload and nothing. I know i should use log collector, but again, that also requires a restart as well, unless a reload will work? The way I reload is /etc/init.d/postgresql reload The distro for Debian is squeeze. Again, it works on my other server, this server it doesn't. Any one have any ideas, where I don't need to restart the pg instance? Thanks in advance. Just so I can follow, what are you defining as a server? Or to put it another way, are db1, db4 separate machines? Are you sure that a another instance of Postgres did not slip in during an upgrade? -Anson -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how long to wait on 9.2 bitrock installer?
9.2.3, Was running overnight for 17 hours. Cancelled install. May investigate further. Installing to Windows. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-long-to-wait-on-9-2-bitrock-installer-tp5723762p5746908.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] broke postgres, how to fix??
Hi Adrian, That's guaranteed to break everything badly. > Even if I "read only style" copied the files? Do you mind elaborating on why this happens? ( or point me to relevant documentation ) Thanks, -JD On Tue, Feb 26, 2013 at 7:04 PM, Lonni J Friedman wrote: > On Tue, Feb 26, 2013 at 4:02 PM, JD Wong wrote: > > Hi Adrian, yes I completely copied the config-file and data directories > > over. > > > > Lonnie, I don't remember. I might not have shut down the "old" postgres, > > yes I set PGDATA accordingly. > > > That's guaranteed to break everything badly. >
Re: [GENERAL] broke postgres, how to fix??
Hi Adrian, yes I completely copied the config-file and data directories over. Lonnie, I don't remember. I might not have shut down the "old" postgres, yes I set PGDATA accordingly. To sum things up, I created a situation where I would use the service to start "old" postgres and pg_ctl to start the "new" one. Thanks! -JD On Tue, Feb 26, 2013 at 6:32 PM, Lonni J Friedman wrote: > Did you shut down the 'old' postgres before copying these files? > Did you (re)configure the 'new' postgres to set its $PGDATA directory > to the location of the 'new' files? > > On Fri, Feb 22, 2013 at 3:46 PM, JD Wong wrote: > > I tried copying postgres over to a new directory. it was working until I > > deleted a bunch of databases from the "old" postgres. Lo and behold this > > somehow broke the "new" copy too. > > > > Now when I start with pg_ctl > > 2013-02-22 18:36:13 EST DETAIL: The database subdirectory > "base/1066060" is > > missing. > > 2013-02-22 18:36:25 EST FATAL: database "wormmine" does not exist > > 2013-02-22 18:36:25 EST DETAIL: The database subdirectory > "base/1027296" is > > missing. > > 2013-02-22 18:37:13 EST FATAL: database "wormmine-dev" does not exist > > > > and it won't start... > > > > How can I fix this? re-creating these databases with the old postgres > > didn't work... > > > > Also, why did this happen? I created a new data directory for the new > > postgres, this should be impossible > > > > I have no ideas left, can anyone help? > > > > Thanks in advance, >
Re: [GENERAL] how long to wait on 9.2 bitrock installer?
On 02/28/2013 07:05 AM, Ronnie and Sandy wrote: 9.2.3, Was running overnight for 17 hours. Cancelled install. May investigate further. Installing to Windows. Would seem to be a question for the folks at BitRock: http://bitrock.com/about.html -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-long-to-wait-on-9-2-bitrock-installer-tp5723762p5746908.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql log file not getting update
db1 and db4 are 2 separate machines. no other instances of PG running on the box, doing a pg_ctl reload did nothing as well. Also looking @ all proecsses running, only PG instance on the box. On Thu, Feb 28, 2013 at 2:27 PM, Adrian Klaver wrote: > On 02/28/2013 08:19 AM, Anson Abraham wrote: > >> My postgresql-9.0-main.log file has suddenly stopped getting updated. I >> do not know why it stopped all of a sudden. We made a slight modification >> where changed in the postgresql.conf param: >> From >> log_connections = off >> log_disconnections = off >> >> To >> >> log_connections = on >> log_disconnections = on >> >> I'm on a debian environment w/ version 9.0.7. It's a single instance w/ >> no cluster on here. It was done w/a simple apt-get install back when, >> when it was a fresh server (we'll call this db1) >> >> I have an identical setup for another server (db4), and when I made the >> change to the same paramers in conf, it's updating the >> postgresql-9.0-main.log file. I reverted the connections and discon >> from on back to off, and did a postgresql reload as well >> as pg_reload_conf(). For both servers. db4 the log file is getting >> updated db1 nothing. I thought the file was locked or some perms may >> have changed and I deleted the postgresql-9.0-main.log file. It didn't >> create a new log file until i did a postgresql reload. It created the >> new file, but still nothing getting written to it. >> >> I know first thing people would say is to restart the instance, but >> restarting is not an option for me in this case. >> >> I've changed >> ucommented out this param >> #log_destination = 'stderr' >> >> to be stderr, and even syslog and did a reload and nothing. >> >> >> I know i should use log collector, but again, that also requires a >> restart as well, unless a reload will work? >> >> The way I reload is /etc/init.d/postgresql reload >> >> The distro for Debian is squeeze. Again, it works on my other server, >> this server it doesn't. Any one have any ideas, where I don't need to >> restart the pg instance? >> Thanks in advance. >> > > Just so I can follow, what are you defining as a server? > Or to put it another way, are db1, db4 separate machines? > Are you sure that a another instance of Postgres did not slip in during an > upgrade? > > -Anson >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] how long to wait on 9.2 bitrock installer?
Adrian Klaver wrote on 28.02.2013 20:45: 9.2.3, Was running overnight for 17 hours. Cancelled install. May investigate further. Installing to Windows. Would seem to be a question for the folks at BitRock: http://bitrock.com/about.html I'm pretty sure that is the call of setacl.exe again - which is used to set the privileges on the data directory correctly. But for some reasons it sometimes traverses the whole harddisk, starting at c:\ and then recursing into each and every directory - which could easily lead to the reported runtimes. This has been reported several times, but it seems this still isn't fixed. I'm only using the ZIP distribution - amongst other reasons also because of this problem. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql log file not getting update
On 02/28/2013 11:58 AM, Anson Abraham wrote: db1 and db4 are 2 separate machines. no other instances of PG running on the box, doing a pg_ctl reload did nothing as well. Also looking @ all proecsses running, only PG instance on the box. I thought this sounded familiar, this is a reprise of the question you asked back in January? I know you say have only one cluster installed. Have you looked to see if the pg_cluster command is installed? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] warm standby question
Hi, I have a primary server (9.1), which does wal archiving like this: wal_level = hot_standby archive_mode = on archive_command = 'test ! -f /home/autobackup/wal/%f && cp %p /home/autobackup/wal/%f' then I have a warm standby with this configuration in recovery.conf: restore_command = 'cp /opt/WAL/%f %p' standby_mode = on when I then start the server it applies all WAL files. But when I then stop the server after a few minutes and start it again, it asks for a WAL file it already processed. Logfile: 2013-02-28 16:21:00 CET LOG: database system was shut down in recovery at 2013-02-28 16:20:53 CET 2013-02-28 16:21:00 CET LOG: entering standby mode 2013-02-28 16:21:00 CET LOG: incomplete startup packet ---> 2013-02-28 16:21:00 CET LOG: restored log file "0001000700C0" from archive 2013-02-28 16:21:00 CET LOG: redo starts at 7/C0F9A650 2013-02-28 16:21:00 CET LOG: consistent recovery state reached at 7/C0FFE398 cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory 2013-02-28 16:21:01 CET FATAL: the database system is starting up 2013-02-28 16:21:01 CET FATAL: the database system is starting up 2013-02-28 16:21:02 CET FATAL: the database system is starting up 2013-02-28 16:21:02 CET FATAL: the database system is starting up 2013-02-28 16:21:03 CET FATAL: the database system is starting up 2013-02-28 16:21:03 CET FATAL: the database system is starting up 2013-02-28 16:21:04 CET FATAL: the database system is starting up 2013-02-28 16:21:04 CET FATAL: the database system is starting up 2013-02-28 16:21:05 CET FATAL: the database system is starting up cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory 2013-02-28 16:21:05 CET FATAL: the database system is starting up 2013-02-28 16:21:06 CET FATAL: the database system is starting up 2013-02-28 16:21:06 CET LOG: incomplete startup packet 2013-02-28 16:21:08 CET LOG: received fast shutdown request 2013-02-28 16:21:08 CET LOG: shutting down 2013-02-28 16:21:08 CET LOG: database system is shut down 2013-02-28 16:21:12 CET LOG: database system was shut down in recovery at 2013-02-28 16:21:08 CET 2013-02-28 16:21:12 CET LOG: entering standby mode > 2013-02-28 16:21:12 CET LOG: restored log file "0001000700C0" from archive 2013-02-28 16:21:12 CET LOG: redo starts at 7/C0F9A650 2013-02-28 16:21:12 CET LOG: consistent recovery state reached at 7/C0FFE398 cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory 2013-02-28 16:21:12 CET LOG: incomplete startup packet 2013-02-28 16:21:13 CET FATAL: the database system is starting up 2013-02-28 16:21:13 CET FATAL: the database system is starting up 2013-02-28 16:21:14 CET FATAL: the database system is starting up 2013-02-28 16:21:14 CET FATAL: the database system is starting up 2013-02-28 16:21:15 CET FATAL: the database system is starting up 2013-02-28 16:21:15 CET FATAL: the database system is starting up 2013-02-28 16:21:16 CET FATAL: the database system is starting up 2013-02-28 16:21:17 CET FATAL: the database system is starting up 2013-02-28 16:21:17 CET FATAL: the database system is starting up cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory 2013-02-28 16:21:18 CET FATAL: the database system is starting up 2013-02-28 16:21:18 CET FATAL: the database system is starting up 2013-02-28 16:21:18 CET LOG: incomplete startup packet cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory cp: cannot stat `/opt/WAL/0001000700C1': No such file or directory thanks sebastian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connection limit exceeded for non-superusers when there are plenty of available slots
We have a number of applications using a production database under a super user account. I've created a new (non-super) user account and if I try to login using this account I get the "Non-superuser connection limit exceeded" error. I'm aware of the postgresql.conf settings for max user connections etc. Here are the queries I ran (under super user) when the login exception occurs. SELECT usesuper, count(*) FROM pg_stat_activity JOIN pg_user USING (usesysid) GROUP BY usesuper; 62 SHOW superuser_reserved_connections; 480 SHOW max_connections; 500 Is there something I'm missing here? Thanks for your help. Here's my OS + Postgres Ubuntu x86_64 , Postgres 8.4.8
Re: [GENERAL] Connection limit exceeded for non-superusers when there are plenty of available slots
On Fri, Mar 1, 2013 at 7:38 AM, G B wrote: > SHOW superuser_reserved_connections; > > 480 > > SHOW max_connections; > 500 > > Is there something I'm missing here? Thanks for your help. This leaves just 20 connections for non-root users. Did you intend to set superuser_reserved_connections to 20, thus leaving 480 for normal use? ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql log file not getting update
oh yeah, i did ask this question. I had forgotten, and was hadnling other things. when you say cluster are you asking if there are multiple instances on the hardware. There's only one instance running, and has been for some time. Our other physical server which is identical server and postgres but different db's has no issues. I've been going in and making exact changes and to that server(server B), and no issues writing to the postgresql-9.0-main.log file located in /var/log/postgres/ on Server A which is the issue, doesn't write to it, but when I change to log_destination to stderr doesn't work, however syslog does. By that i assumed then that it must've been a perms issue, but doesn't look to be the case. Heck i even put the file and folder as chmod 777 and still nothing. On Thu, Feb 28, 2013 at 3:16 PM, Adrian Klaver wrote: > On 02/28/2013 11:58 AM, Anson Abraham wrote: > >> db1 and db4 are 2 separate machines. no other instances of PG running >> on the box, doing a pg_ctl reload did nothing as well. >> Also looking @ all proecsses running, only PG instance on the box. >> >> > I thought this sounded familiar, this is a reprise of the question you > asked back in January? I know you say have only one cluster installed. Have > you looked to see if the pg_cluster command is installed? > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] Connection limit exceeded for non-superusers when there are plenty of available slots
My intention was to have 480 max connections for super user and 20 connections for non super user. (I created the non super user account for an external user to log into PgAdmin and look at the schema) Is there a problem with 20 connections in such a scenario? When I select from pg_stat_activity, I only see 62 connections from the super user (postgres) and that number is less than 480. I don't see any connections from non super user. Yet , when I try to login as non super user, it claims it ran out of non super user reserved connections. On Thu, Feb 28, 2013 at 3:49 PM, Chris Angelico wrote: > On Fri, Mar 1, 2013 at 7:38 AM, G B wrote: > > SHOW superuser_reserved_connections; > > > > 480 > > > > SHOW max_connections; > > 500 > > > > Is there something I'm missing here? Thanks for your help. > > This leaves just 20 connections for non-root users. Did you intend to > set superuser_reserved_connections to 20, thus leaving 480 for normal > use? > > ChrisA > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Connection limit exceeded for non-superusers when there are plenty of available slots
>From the docs: "superuser_reserved_connections: Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted." ( http://www.postgresql.org/docs/9.0/static/runtime-config-connection.html) So in your case, you have 480 connections for super users out of 50 total. Its not that 20 of them are reserved for non-superusers so since you have less then 480 connections available, only super users can use them. I don't believe postgresql cares who is currently using a connection... rather if the number of available connections is less then what is set in 'superuser_reserved_connections', only superusers can use it. (Someone can correct me if I'm wrong here) On Thu, Feb 28, 2013 at 2:17 PM, G B wrote: > My intention was to have 480 max connections for super user and 20 > connections for non super user. (I created the non super user account for > an external user to log into PgAdmin and look at the schema) Is there a > problem with 20 connections in such a scenario? > > When I select from pg_stat_activity, I only see 62 connections from the > super user (postgres) and that number is less than 480. > I don't see any connections from non super user. Yet , when I try to login > as non super user, it claims it ran out of non super user reserved > connections. > > > > > > > > > > On Thu, Feb 28, 2013 at 3:49 PM, Chris Angelico wrote: > >> On Fri, Mar 1, 2013 at 7:38 AM, G B wrote: >> > SHOW superuser_reserved_connections; >> > >> > 480 >> > >> > SHOW max_connections; >> > 500 >> > >> > Is there something I'm missing here? Thanks for your help. >> >> This leaves just 20 connections for non-root users. Did you intend to >> set superuser_reserved_connections to 20, thus leaving 480 for normal >> use? >> >> ChrisA >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- Virtually, Ned Wolpert "Settle thy studies, Faustus, and begin..." --Marlowe
Re: [GENERAL] postgresql log file not getting update
On 02/28/2013 12:58 PM, Anson Abraham wrote: oh yeah, i did ask this question. I had forgotten, and was hadnling other things. when you say cluster are you asking if there are multiple instances on the hardware. Aah, tells you how long since I used Debian, the command is actually pg_wrapper. My guess it is installed on your machine. My guess is also that you will need to use it to reload the log configuration. It is installed do man pg_wrapper to get usage instructions. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Poor performance when using a window function in a view
On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane wrote: > Chris Hanks writes: > > create or replace view values_view as > > select fkey1, fkey3, > > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > > from ( > > select fkey1, fkey3, > > cast(sum((case when (value > 0.0) then 4 else 1 end)) as double > > precision) as derived1, > > sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) > as > > derived2 > > from values > > group by fkey1, fkey3 > > ) as t1; > > > -- This query requires a sequential scan on values, though all the data > it > > needs could be found much more efficiently with an index scan. > > explain analyze select * from values_view where fkey1 = 1263; > > To use the outer WHERE clause as an index constraint, postgres would > have to prove that scanning only the rows with fkey1 = 1263 would still > find all the rows that would get examined by the window functions --- > and in this case, it's not only the window functions that make that less > than obvious, but the grouped aggregates in the sub-select below them. > There's not nearly that amount of intelligence in the system about > window functions, as yet. So you'll have to write out the query > longhand and put the WHERE clause at the lower level, if you want this > optimization to happen. > > regards, tom lane > Ok, that makes sense, thanks. Can anyone point me to an example of wrapping a function in a view, like Merlin suggested? I'm not sure how that would work.
Re: [GENERAL] Floating point error
> "TD" == Tom Duffey writes:
TD> Riddle me this. I have a database column of type "real" that gets
TD> mapped to a Java field of type double via JDBC. ...
TD> - Selecting values from both test and production DBs using psql
TD> shows "10.3885" as the value
TD> - The Java app on production shows "10.3884573" while the test app
TD> shows "10.3885"
I suspect the issue is that psql(1) and whatever java method you use to
convert the floats to text choose different rounding.
By default, it seems that psql(1) uses something like printf("%.4f",...)
whereas your java app calls a routing which works more like "%.7f".
(The wire format for floats is the same as they are stored, not a text
representation thereof.)
-JimC
--
James Cloos OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Floating point error
Hi Everyone,
To bring closure to this thread, my whole problem was caused by not knowing
about the extra_float_digits setting. We have a script that uses COPY to
transfer a subset of rows from a very large production table to a test table.
The script was not setting extra_float_digits so the values did not match even
though they appeared to match when running queries in psql. Definitely another
gotcha for floating point values and it might be a good idea to mention this
setting on the "Numeric Types" page of the docs.
Thanks to all who chimed in to help!
Tom
On Feb 28, 2013, at 7:05 PM, James Cloos wrote:
>> "TD" == Tom Duffey writes:
>
> TD> Riddle me this. I have a database column of type "real" that gets
> TD> mapped to a Java field of type double via JDBC. ...
>
> TD> - Selecting values from both test and production DBs using psql
> TD> shows "10.3885" as the value
>
> TD> - The Java app on production shows "10.3884573" while the test app
> TD> shows "10.3885"
>
> I suspect the issue is that psql(1) and whatever java method you use to
> convert the floats to text choose different rounding.
>
> By default, it seems that psql(1) uses something like printf("%.4f",...)
> whereas your java app calls a routing which works more like "%.7f".
>
> (The wire format for floats is the same as they are stored, not a text
> representation thereof.)
>
> -JimC
> --
> James Cloos OpenPGP: 1024D/ED7DAEA6
--
Tom Duffey
tduf...@trillitech.com
414-751-0600 x102
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GetHierarchy
Greetings ! I have a hierarchy table with two attributes : Parent_ID, Child_ID with the sample data (can go upto n-level) as below: ParentID ChildID 1 3 1 4 3 5 5 6 6 7 I need a query to retrieve all the n level hierarchy when a Child node_id is passed. For example; if I pass 7, then i need parent nodes as below: I want to achive this in postgres database (version 9.2) without connect by prior. 3 5 5 6 6 7 Any reply on this is great help. Thanks and REgards RAdha Krishna
