Re: [GENERAL] Regarding template1 database

> > - I want to know the tables of tempate1> > There are none, unless you > > choose to add some.I got it there are no tables. What about the system > > tables of template1 database ? Are they updated or not? > > - If i want to monitor the transactions done in the template1 database ( > > inserti

Re: [GENERAL] Regarding template1 database

On 04/22/2013 07:48 PM, S H wrote: I would like some assistance in understanding of template1 database. - I want to know the tables of tempate1 There are none, unless you choose to add some. - I want to know when there is any insertion/updation in template1 theoretically. You could if you c

[GENERAL] Regarding template1 database

I would like some assistance in understanding of template1 database.- I want to know the tables of tempate1- I want to know when there is any insertion/updation in template1 theoretically.- If i want to monitor the transactions done in the template1 database ( insertion/updation ) , is there an

Re: [GENERAL] Need solution for weekly database "snapshot"

On Monday, April 22, 2013, Moshe Jacobson wrote: > We have a report in our system that is pulled many times each week, but > needs to be based off data from Sunday at noon. So every Sunday at noon, we > back up our main database and restore it into a new reporting snapshot > database. > > The prob

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

[GENERAL] Error installation of openmolar

Hello everyone I tried to install openmolar on my ubuntu lucid system following instructions in this page < http://www.openmolar.com/docs/installation/ubuntu.html> Some errors appears during the installation, and this is the image of errors

Re: [GENERAL] pg_restore from split files

It works ... " combine them again. " Thank you all Regards On Mon, Apr 22, 2013 at 3:57 PM, Steven Schlansker wrote: > > On Apr 22, 2013, at 12:47 PM, akp geek wrote: > > > pg_dump dbname -n schemaname -t table_name -Fc | split -b 500m -t > table.dump > > Since you split the files outside

Re: [GENERAL] Negative replication lag?

On 2013-04-22 16:36:38 -0600, Quentin Hartman wrote: > I'm using this script to check my replication lag on my streaming > replication pairs with Nagios: > > https://gist.github.com/jacobian/743942 > > It generally works fine, but will occasionally return a negative lag value > (-37kb for example

[GENERAL] Negative replication lag?

I'm using this script to check my replication lag on my streaming replication pairs with Nagios: https://gist.github.com/jacobian/743942 It generally works fine, but will occasionally return a negative lag value (-37kb for example) which of course causes it to throw an alarm, but is total nonsens

Re: [GENERAL] bug in 8.4 and resolved

On 4/22/2013 3:13 PM, Thomas Kellerer wrote: Abhinav Dwivedi wrote on 22.04.2013 07:12: select * from district where statecode in (Select districtcode from state) Please note that the attribute districtcode is not existent in the table state and if this query i.e. Select districtcode from sta

Re: [GENERAL] bug in 8.4 and resolved

Abhinav Dwivedi wrote on 22.04.2013 07:12: select * from district where statecode in (Select districtcode from state) Please note that the attribute districtcode is not existent in the table state and if this query i.e. Select districtcode from state is executed in isolation then it correctly

Re: [GENERAL] bug in 8.4 and resolved

On 04/21/2013 10:12 PM, Abhinav Dwivedi wrote: Dear Member, We found a bug in postgresql 8.4 details are follows. If a syntactically wrong query is made inner query of a nested query then the whole query wrongly returns a dataset instead of an error message. For example: select * from distr

Re: [GENERAL] Need solution for weekly database "snapshot"

On 04/22/2013 12:16 PM, Moshe Jacobson wrote: On Mon, Apr 22, 2013 at 1:41 PM, Christophe Pettus mailto:x...@thebuild.com>> wrote: > Not bad, but the transaction logs would fill up the file system. I'm not sure I understand that comment. Why would the transaction logs be particul

[GENERAL] bug in 8.4 and resolved

Dear Member, We found a bug in postgresql 8.4 details are follows. If a syntactically wrong query is made inner query of a nested query then the whole query wrongly returns a dataset instead of an error message. For example: select * from district where statecode in (Select districtcode from s

Re: [GENERAL] pg_restore from split files

On Apr 22, 2013, at 12:47 PM, akp geek wrote: > pg_dump dbname -n schemaname -t table_name -Fc | split -b 500m -t table.dump Since you split the files outside of the Postgres world, you have to combine them again. Roughly, cat table.dump.* > table.dump.combined pg_restore --usual-arguments t

Re: [GENERAL] pg_restore from split files

I don't think pg_restore is able to deal with your split files. What was the reasoning for doing this? Did you try first on smaller table/files to see if your dump/split/restore procedure would work? Igor Neyman From: akp geek [mailto:akpg...@gmail.com] Sent: Monday, April 22, 2013 3:47 PM To:

Re: [GENERAL] pg_restore from split files

pg_dump dbname -n schemaname -t table_name -Fc | split -b 500m -t table.dump On Mon, Apr 22, 2013 at 3:41 PM, Igor Neyman wrote: > How exactly did you create “split” dump? > > ** ** > > Igor Neyman > > ** ** > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@

Re: [GENERAL] pg_restore from split files

How exactly did you create "split" dump? Igor Neyman From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of akp geek Sent: Monday, April 22, 2013 3:36 PM To: pgsql-general Subject: [GENERAL] pg_restore from split files Hi All - I have created a du

[GENERAL] pg_restore from split files

Hi All - I have created a dump of a big table into 5 split files. What is the procedure to restore them using pg_dump. I am getting the following error pg_restore: [custom archiver] could not read from input file: end of file pg_restore: *** aborted because of error Appreciate your help.

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 03:16:19PM -0400, Moshe Jacobson wrote: > > > Not bad, but the transaction logs would fill up the file system. > > > > I'm not sure I understand that comment. Why would the transaction logs be > > particularly voluminous in this case? > > > > I assumed the logs would be s

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 1:41 PM, Christophe Pettus wrote: > > Not bad, but the transaction logs would fill up the file system. > > I'm not sure I understand that comment. Why would the transaction logs be > particularly voluminous in this case? > I assumed the logs would be shipping to the slav

[GENERAL] ERROR: not enough stack items

"PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit" I see in the postgres log, on a DEV box . . . 2013-04-18 17:09:44.721 EDT 516f10af.34fb 2/160904 0 ERROR: not enough stack items 2013-04-18 17:09:44.721 EDT 516f10af.34fb 2/160904 0 S

Re: [GENERAL] using text search

I forgot to say that the function is "position ( txtseach in txtcomplete)" :) Bye El 22/04/2013, a las 19:36, Alfonso Afonso escribió: > And using substring function? Do you have the same results? > > I understand your arguments about normalization but, as you know, BNF is the > best SQL Deve

Re: [GENERAL] using text search

And using substring function? Do you have the same results? I understand your arguments about normalization but, as you know, BNF is the best SQL Developer friend ;) I used to avoid text fields and search/filter them (when it is possible), mainly because this consumption issues if the query

Re: [GENERAL] Need solution for weekly database "snapshot"

On 4/22/2013 10:05 AM, Moshe Jacobson wrote: Setup a slave. Not bad, but the transaction logs would fill up the file system. Besides, it would not be worth it to set up a whole database cluster just for this purpose. Any alternatives? if your file system supports it (ZFS, for instance

Re: [GENERAL] Need solution for weekly database "snapshot"

On Apr 22, 2013, at 10:05 AM, Moshe Jacobson wrote: > Not bad, but the transaction logs would fill up the file system. I'm not sure I understand that comment. Why would the transaction logs be particularly voluminous in this case? > Besides, it would not be worth it to set up a whole database

[GENERAL] Using an index for IS DISTINCT FROM queries

Hi everyone, I have a large table (~150M rows) that keeps a version field. At any given time, it is expected that the vast majority of the rows are on the "current" version, but some may be missing. To figure out all the missing our outdated values, I run a query along the lines of SELECT id

[GENERAL] how can this get faster

Hi, I have a table with mainly a list of keywords and a few odd columns referring to their origin. There is a standard btree index, and searches are carried out as anchored regex. Entries in the table and queries are uppercased; this turned out to have a big speed advantage over a ~* condition.

Re: [GENERAL] using text search

Hi, W dniu 04/22/2013 05:57 PM, Alfonso Afonso pisze: Hi Rafal Maybe you should think or consider to have normalized database to do this kind of select. I mean that if the keyword is a key you could add a column to table orders and have it ordered and indexed without having to reevaluate the

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 12:14 PM, Karsten Hilbert wrote: > > We have a report in our system that is pulled many times each week, but > > needs to be based off data from Sunday at noon. So every Sunday at noon, > we > > back up our main database and restore it into a new reporting snapshot > > data

Re: [GENERAL] Need solution for weekly database "snapshot"

On Mon, Apr 22, 2013 at 11:58:58AM -0400, Moshe Jacobson wrote: > We have a report in our system that is pulled many times each week, but > needs to be based off data from Sunday at noon. So every Sunday at noon, we > back up our main database and restore it into a new reporting snapshot > databas

[GENERAL] Need solution for weekly database "snapshot"

We have a report in our system that is pulled many times each week, but needs to be based off data from Sunday at noon. So every Sunday at noon, we back up our main database and restore it into a new reporting snapshot database. The problem is that backing up and restoring the database every week

Re: [GENERAL] using text search

Hi Rafal Maybe you should think or consider to have normalized database to do this kind of select. I mean that if the keyword is a key you could add a column to table orders and have it ordered and indexed without having to reevaluate the query every time. About your question, if you are searc

Re: [GENERAL] Memory usage after upgrade to 9.2.4

I'm running pgBadger over the log, and will get some queries to explain analyze them. The 9.2 cluster is running in the same server as the production, so I will try to compare some critical explains and publish on explain.depesz.com. 2013/4/21 Adrian Klaver > On 04/21/2013 09:29 AM, Daniel Cris

Re: [GENERAL] Where in the source code does postgres write to disk?

On Fri, Apr 19, 2013 at 11:26 PM, Mike Levine wrote: > According to > http://www.postgresql.org/docs/9.2/static/storage-file-layout.html > > "When a table or index exceeds 1 GB, it is divided into gigabyte-sized > segments. The first segment's file name is the same as the filenode; > subsequent se

[GENERAL] using text search

... or not (I'm not quite sure) Hello, I have the following tables: CREATE TABLE orders (info text, ); CREATE TABLE keywords (phrase text, .); And I need to find all the ORDERS rows, which conain a PHRASE present in the info column ... like so: SELECT o.* from orders o join keywords k

[GENERAL] Regression test fails v9.2.4

Hi all... In my organisation we build PG from source on SLES11.2 using GCC 4.3. Versions 9.1.x of Postgresql and earlier always passed all of the regression tests but 9.2.3 and .4 fail on the following : Union Join Select views Polymorphism With This happens when using gmake check & gmake MAX_C

[GENERAL] PostgreSQL archiving last replayed WAL after recovery

Hello, I'm wondering about PostgreSQL always archiving the last replayed WAL after getting out of recovery. This does not seem to be documented anywhere, and this part of code is quite tricky to understand. IMHO, PostgreSQL should not try to archive again WAL files coming from the restore_command

Re: [GENERAL] Implementing DB2's "distinct" types

On 2013.04.22 12:09 AM, Thomas Kellerer wrote: create type sno as varchar(50) with comparisons; create type pno as varchar(50) with comparisons; The following query will be rejected because sno and pno are not comparable (even though both are varchar columns): select * from p jo

Re: [GENERAL] Implementing DB2's "distinct" types

create type sno as varchar(50) with comparisons; create type pno as varchar(50) with comparisons; The following query will be rejected because sno and pno are not comparable (even though both are varchar columns): select * from p join s on s.sno = p.pno; I wonder if a similar