Re: [GENERAL] Inheritance and foreign keys

On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > The way I do it is the following : > - ensure a common sequence for the ID for all tables in the inheritance > tree (usually one parent and one or more children) > - enforce normal FK constraints for all F

[GENERAL] Inheritance and foreign keys

Hi, I designed three tables so that one table inherits another, and the third table references the parent table. If a record is inserted into the third table and the value does exist in the parent table indirectly, because it is present in the inherited table, I still get an error. Is some option

Re: [GENERAL] truncate table getting blocked

On Tue, Apr 26, 2016 at 7:25 PM, Albe Laurenz wrote: > > > It is not the "SET search_path" statement that is blocking the truncate, > but probably some earlier statement issued in the same transaction. > You are right. I had a select against that table. Adding this line fixed it ... conn.set_is

[GENERAL] truncate table getting blocked

Hello, I have a python script. It opens a cursor, and sets the search_path (using psycopg2). In case something goes wrong in the script , a record is inserted into a table. In that script, I am not doing any thing else other than reading a file and publishing the lines to a queue (no database oper

Re: [GENERAL] Partitioning and constraint exclusion

> > > > ​I am not sure but am doubting it is intelligent enough to recognize the > functional expression even if all of the values are present. "simple > equality" ( > http://www.postgresql.org/docs/9.4/interactive/ddl-partitioning.html) > this is not. > Looks like the tables with about 100+ valu

Re: [GENERAL] Partitioning and constraint exclusion

On Mon, Sep 7, 2015 at 7:12 PM, Melvin Davidson wrote: > First, what is the PostgresSQL version ?? > 9.3.6 > > Next, in postgresql.conf, what is the value of constraint_exclusion ? > partition In response to the other possible issues pointed out - the planner is indeed *skipping the rest

[GENERAL] Partitioning and constraint exclusion

Hello , I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the form CHECK (myuid in (123,456,..)). myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for

Re: [GENERAL] Streaming replication - slave not getting promoted

On Sun, Jan 4, 2015 at 8:01 PM, Adrian Klaver wrote: > On 01/04/2015 06:09 AM, Jayadevan M wrote: > >> Hi, >> I have streaming replication set up, with PostgreSQL 9.3. The entries in >> recovery.conf on the slave are as follows - >> standby_mode = 'on' &g

[GENERAL] Streaming replication - slave not getting promoted

Hi, I have streaming replication set up, with PostgreSQL 9.3. The entries in recovery.conf on the slave are as follows - standby_mode = 'on' primary_conninfo = 'host=127.0.0.1 port=2345 user=postgres password=password' restore_command = 'cp /pgdata/archive/%f "%p"' trigger_file = ‘/tmp/down.trg’ I

[GENERAL] Query planning

Hi all, I am going through PostgreSQL documentation on how row estimation is done in PostgreSQL. http://www.postgresql.org/docs/current/static/row-estimation-examples.html There is a reference to cardinality (rel_cardinality) there. But that is an estimate of the number of rows in the table, right

Re: [GENERAL] Insert zero to auto increment serial column

Will a rule work? http://www.postgresql.org/docs/9.3/static/sql-createrule.html On Wed, Apr 2, 2014 at 5:47 PM, loc wrote: > I'm currently using an Informix Innovator-C database with Aubit4GL and I > would like to migrate to PostgreSQL, it looks like the transition will not > be too difficult,

[GENERAL] Doubts on startup costs

Hi, A few academic questions related PostgreSQL query planner and output - In the output of EXPLAIN for SQL statements I have seen so far, the startup cost for the innermost node/leaf has been 0. Are there situations where it may be non-zero? The startup costs for outer nodes will always be equa

Re: [GENERAL] High Level Committers Wanted

On Sat, Mar 15, 2014 at 1:51 AM, Andy Colson wrote: > On 3/14/2014 6:08 AM, Antman, Jason (CMG-Atlanta) wrote: > >> I'm not a "high level committer", nor am I even a regular poster to this >> list. >> >> not saying this post is true, but... If I'm reading between the lines >> correctly, this coul

[GENERAL] table design and data type choice

Hi, We have a table to record the voteup/votedown by users of questions and answers (like on stackoverflow). So there will be a large number of inserts (voteup/down), some updates(user changes mind)and may be a few deletes. The queries will be mostly aggregates (count(*) where question_id= and vo

Re: [GENERAL] authentication failure

I am able to login as postgres with password from the same machine. So it is not an expiry issue (as you too concluded). Output from strace is about 500 lines. I am pasting what I feel may be relevant. I hope this will be useful. execve("/usr/pgsql-9.3/bin/psql", ["psql", "-h", "localhost"], [/*

Re: [GENERAL] authentication failure

gpass $ env | grep PG PGPORT=1234 PGUSER=postgres PGHOST=localhost On Sun, Jan 5, 2014 at 9:03 PM, Adrian Klaver wrote: > On 01/04/2014 08:46 PM, Jayadevan M wrote: > >> Log entries for 3 situations - 2 successful and one failed attempt - >> >> From non-chroot, shell user

Re: [GENERAL] authentication failure

=[unknown] LOG: connection received: host=::1 port=47949 2014-01-05 10:12:51 IST [17052]: [2-1] user=postgres,db=postgres LOG: connection authorized: user=postgres database=postgres On Sat, Jan 4, 2014 at 8:43 PM, Adrian Klaver wrote: > On 01/03/2014 09:29 PM, Jayadevan M wrote: > >> T

Re: [GENERAL] authentication failure

st a minor inconvenience, but I would like to resolve this. The no password supplied message comes back so fast, it is as if it did not even attemp to connect. On Fri, Jan 3, 2014 at 8:37 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 01/03/2014 04:54 AM, Jayadevan M wrote: > >>

Re: [GENERAL] authentication failure

:13 PM, Jayadevan M > wrote: > >> I am able to login from the non-chroot environment. So it is not an issue >> with pg_hba.conf and not an issue of password expiration. Is there a debug >> psql option? >> >> OK. > > Have you checked the PGPASSWORD environmen

Re: [GENERAL] authentication failure

I am able to login from the non-chroot environment. So it is not an issue with pg_hba.conf and not an issue of password expiration. Is there a debug psql option? On Fri, Jan 3, 2014 at 5:09 PM, Chris Curvey wrote: > > > > On Fri, Jan 3, 2014 at 4:16 AM, Jayadevan M > wrote: &

Re: [GENERAL] authentication failure

Nope - psql -W psql: FATAL: password authentication failed for user "postgres" On Fri, Jan 3, 2014 at 2:49 PM, Ashesh Vashi wrote: > Try "psql -W" for prompting the password forcefully. > > > On Fri, Jan 3, 2014 at 2:46 PM, Jayadevan M > wrote: > >&g

[GENERAL] authentication failure

Hi, I am trying to login from psql and consistently getting a "psql: FATAL: password authentication failed for user "xyz"" for all users. I am not being prompted for a password at all. I faced a similar issue sometime ago because there was a .pgpass file and it had wrong entries. This time there i

Re: [GENERAL] Refresh Materialized View

To answer my own question, I saw this thread - http://postgresql.1045698.n5.nabble.com/Efficiency-of-materialized-views-refresh-in-9-3-td5762618.html I think that does answer my questions. Nothing has changed? Regards, Jayadevan On Wed, Jan 1, 2014 at 6:42 PM, Jayadevan M wrote: > Hello

[GENERAL] Refresh Materialized View

Hello all, A few questions about materialized views. When I refresh a materialized view, is it a DELETE/INSERT behind the scenes? Do we need to vacuum to reclaim space? If a query is executed against the view when the refresh is happening, will the query see the data before the refresh started? Doe

Re: [GENERAL] design for multiple time series

On Fri, Dec 13, 2013 at 12:15 AM, Seb wrote: > Hi, > > I'm working on the design of a database for time series data collected > by a variety of meteorological sensors. Many sensors share the same > sampling scheme, but not all. I initially thought it would be a good > idea to have a table ident

[GENERAL] vacuuming - doubt

Hi, Another theory question - PostgreSQL documentation says that - "There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space " I created a table, inserted 1000 records and deleted them. The size after a vacuum and a vacuum full are given - select pg

[GENERAL] WITH and exclusive locks

Hi, When conducting performance tests of our application, I saw quite a few exclusive locks in SELECTs with WITH clause. So I created a big table and executed a query which takes time. with x as (select * from emp where first_name like 'Sco%') select * from x; I got 2 locks - one accesshare lock a

[GENERAL] log_line_prefix

Hi, I was trying different options of log_line_prefix. I am making chnages, doing a pg_ctl reload and checking the output in the log files. For some reason ,the changes seem to have no impact. What am I doing wrong? Here is the output form the log files where you can see the change being accepted (

[GENERAL] Postgres Server backend process

Hello, I was going through http://www.postgresql.org/files/developer/tour.pdf and the source at http://doxygen.postgresql.org/postmaster_8c_source.html The pdf shows the daemon process doing authentication and spawning a process to handle the request from the client. The code has these comments -

Re: [GENERAL] Tuning 9.3 for 32 GB RAM

Hi, On Thu, Nov 14, 2013 at 7:14 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > Hello, > > do these changes please look okay for a PostgreSQL 9.3 running on CentOS > 6.4 server with 32 GB RAM (with Drupal 7 and few custom PHP scripts) > > postgresql.conf: > >shared_buffers = 4096

Re: [GENERAL] Theory question

On Thu, Nov 14, 2013 at 7:58 AM, Jayadevan wrote: > Jeff Janes wrote > > No. The checkpointer writes all data that was dirty as of a certain time > > (the start of the checkpoint) regardless of how often it was used since > > dirtied, and the background writer writes data that hasn't been used >

[GENERAL] Theory question

Hi, What are the real differences between the bgwriter and checkpointer process? Both of them write data from the buffer to the data files, right? Is it just a matter of 'when' they write? Regards, Jayadevan

Re: [GENERAL] Documents/ppts/pdfs

> These presentations are available in > http://www.postgresql.org/developer/coding/ > > Presentations > >- Transaction Processing in > PostgreSQL (From >OSDN Oct 2000) >- A Tour of PostgreSQL > Internals

[GENERAL] Documents/ppts/pdfs

Hi, When I search for material on PostgreSQL, once in a while I see reasonably old, but very good content in file such as http://www.postgresql.org/files/developer/transactions.pdf How can I browse to a top level folder ( http://www.postgresql.org/files/developer) and see what else is available ?

Re: [GENERAL] search_path and current_schema

OK. When I logged in as a user who had access to the schema, the output from current_schemas and search_path were matching. Thanks. On Sun, Oct 27, 2013 at 9:55 PM, Tom Lane wrote: > Jayadevan M writes: > > I thought current_schemas and search_path will return the same set of &g

[GENERAL] search_path and current_schema

Hi, I was trying out schema settings and related functions. PostgreSQL version is 9.3 [postgres@MyCentOS 9.3]$ psql psql (9.3.0) Type "help" for help. postgres=# show search_path; search_path "$user",public (1 row) postgres=# select current_schemas(true); current_schemas

[GENERAL] PostgreSQL Point In Time Recovery

Hi, I went through http://www.postgresql.org/docs/9.3/static/continuous-archiving.html and set up the archiving process. With this approach, if my database crashes after a couple of weeks after the base backup is taken, recovering would mean replaying the WAL logs for about 2 weeks, right? To avoid

Re: [GENERAL] postgresql.conf error

ot;"/pgdata/prod/data_93/postgresql.conf"" contains errors; unaffected changes were applied","" On Fri, Oct 18, 2013 at 1:12 PM, Tom Lane wrote: > Jayadevan M writes: > > Which is the quickest way to troubleshot the message " > > LOG: co

[GENERAL] postgresql.conf error

Hi, Which is the quickest way to troubleshot the message " LOG: configuration file "/postgresql.conf" contains errors; unaffected changes were applied"" ? I made a couple of changes a few days ago, and did not reload Today I made some more changes and did a pg_ctl reload. Is there an optio

Re: [GENERAL] logging statements in PostgreSQL

: > Jayadevan M wrote: > > Thanks for the pointer. I do not really want to log the plans of > queries, just the queries, execution > > time and a couple of other details (database,user). If I use the > auto-explain module, it will end up > > printing the plan for all query

Re: [GENERAL] logging statements in PostgreSQL

, Amit Langote wrote: > On Wed, Sep 25, 2013 at 12:18 PM, Jayadevan M > wrote: > > Hi all, > > I am planning to use pgbadger for analyzing queries. Documentation for > > pgbadger says "Do not enable log_statement as their log format will not > be >

[GENERAL] logging statements in PostgreSQL

Hi all, I am planning to use pgbadger for analyzing queries. Documentation for pgbadger says "Do not enable log_statement as their log format will not be parsed by pgBadger. " I have log_min_duration_statement = 0 I do see quite a few SELECTs, INSERTS etc in the log files, function calls too, for e

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

Could you please post EXPLAIN for that query? How 'fat' are the temporary tables - just a couple of columns or really wide? On Mon, Sep 23, 2013 at 7:08 PM, Andrus wrote: > Query > > SELECT * FROM toode > WHERE toode in (SELECT toode FROM tempkaive) > OR toode in (SELECT toode FROM tempalg

Re: [GENERAL] Postgres 9.3 Installation Problems Windows 7 64 Bit Pro

It should be possible to install the SW without initializing the cluster. That way you may be in a better position to troubleshoot the issue. Complete the installation, then use initdb to initialize a cluster. Have a look at this url too - http://wiki.postgresql.org/wiki/Running_%26_Installing_Post

Re: [GENERAL] Using oracle stored procedures for postgresql database

Hi, Have a look at orafce for the plugins. Try ora2pg for initial migration. There are differences in how you execute procedures (syntax), packages are missing in PostgreSQL and so on. Please have a look at http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle Regar

[GENERAL] Query - CPU issue

Hi, I have this query explain analyze select distinct geoip_city(src_ip) , src_ip from alert where timestamp>=1378512000 and timestamp < 1378598400 The explain takes forever, and CPU goes upto 100%. So I end up killing the query/explain. This one, without the function call, comes back in under

[GENERAL] Moving from 9.2 to 9.3

Hello, When I upgraded from 9.2 to 9.3, I copied the postgresql.conf and pg_hba.conf files form 9.2 installation and used those. Is that likely to cause any issues? I did not see any significant parameter changes from 9.2 to 9.3. So far, it is running fine. Regards, Jayadevan

Re: [GENERAL] [pgeu-general] Call for design: PostgreSQL mugs

Hello all, "Elephants Never Forget" sounds like a good idea. It refers to reliability of the database (from a transaction perspective) and ability to recover "lost data". http://www.scientificamerican.com/article.cfm?id=elephants-never-forget Regards, Jayadevan On Sun, Sep 8, 2013 at 9:20 PM, Eri

Re: [GENERAL] PostgreSQL 9.3

Hi, Thanks. So we are close to the tentative release date. Good. Regards, Jayadevan On Mon, Aug 19, 2013 at 10:16 AM, Sandro CAZZANIGA < cazzaniga.san...@gmail.com> wrote: > Le 19/08/2013 06:38, Jayadevan M a écrit : > > Hello all, > > Is the release date for PostgreSQL 9

[GENERAL] PostgreSQL 9.3

Hello all, Is the release date for PostgreSQL 9.3 production decided? We are going live in a couple of weeks with a portal and if possible, would like to go with 9.3, Materialized Views being the key feature that will add value. Regards, Jayadevan

Re: [GENERAL] Debugging Postgres?

Hi, Having a look at the PostgreSQL log may help. http://stackoverflow.com/questions/71/how-to-log-postgres-sql-queries You could also try logging in via psql on the database host itself, to ensure it is not a network issue. Regards, Jayadevan On Wed, Aug 14, 2013 at 2:21 AM, Barth Weishoff

Re: [GENERAL] Trigger to update table info

Hi, In case you are not keen on getting the latest and really accurate counts/size, you could just refer the views readily available - http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres You won't get the updatetime, though. Regards, Jayadevan

Re: [GENERAL] Replication Postgre > Oracle

Hi, >From Oracle to PostgreSQL, you could have a look at Goldengate. It does not support PostgreSQL as the source database. Regards, Jayadevan On Wed, Aug 7, 2013 at 1:54 PM, BOUVARD Aurélien < aurelien.bouv...@supinfo.com> wrote: > Hi all, > > ** ** > > My compagny would like to configure

Re: [GENERAL] Migration from Symfoware to PostgreSQL-Constructor functions

Hi, System tables, views and most of the metadata can be retrieved from these views - http://www.postgresql.org/docs/9.2/static/information-schema.html Regards, Jayadevan On Sun, Aug 4, 2013 at 2:48 PM, sachin kotwal wrote: > Hello > > While migrating small application from Symfoware to Postgr

Re: [GENERAL] pg recovery

overy has begun. >> Recovery has not begun. Without the change, the recovery will not start. So, >do I have to do a reload, or restart, for the parameter to take effect? The >situation is - master is up and running, slave is up and running. I made a >change to recovery.conf. How can I make slave '

Re: [GENERAL] pg recovery

Hi, > wrote: >> I have postgresql streaming replication set up. I forgot to add an >> entry for trigger_file in recovery.conf. So I added that entry and did >> a pg_ctl reload. >Recovery parameters are not GUC parameters, so doing a parameter reload >has no effect. Also, such parameters cannot be c

[GENERAL] pg recovery

Hi, I have postgresql streaming replication set up. I forgot to add an entry for trigger_file in recovery.conf. So I added that entry and did a pg_ctl reload. Is there a way to confirm that the entry has been read by the server? Any view/function? Regards, Jayadevan DISCLAIMER: "The informa

Re: [GENERAL] query on query

> >> >> So each student may get counted many times, someone with 99 will be >> counted >> 10 times. Possible to do this with a fat query? The table will have >> many thousands of records. >> > > >Not sure I got the point, but I guess this is a good candidate for a CTE: > >WITH RECURSIVE t(n) AS ( >

[GENERAL] query on query

Hi, I have a table like this - postgres=# \d m Table "public.m" Column | Type | Modifiers +-+--- id | integer | marks | integer | postgres=# select * from m; id | marks +--- 1 |27 2 |37 3 |17 4 |27 5 |18 (5 rows) I wante

Re: [GENERAL] json functions

Hi, >json_send like all send functions is internal. Not all functions available in >the >catalog are exposed through SQL -- for example they may be used to serialize >data for transmission over the wire. If you can handle C you can hunt down >the location of the function to see what it does (whi

[GENERAL] json functions

Hi, I have PostgreSQL 9.2.1. I can see a few json functions under pg_catalog, json_send, for example. But I can't find any documentation. Am I missing something? Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is a

[GENERAL] Oracle to PostgreSQL replication with Goldengate

Hello all, GoldenGate added PostgreSQL as a target database for replication. I tried setting it up, and not finding any tutorial, put together a how to here - http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-oracle-to-postgresql/ I think near real-time replication might have q

Re: [GENERAL] Data sets for download

>Have a look a Benerator. It can create quite reasonable test data (e.g. valid >addresses, "real" looking names and so on). > >It has a bit steep learning curve, but I'm quite happy with the results >http://databene.org/databene-benerator > > >Another option might be the Dell DVD Store Loadtest: >h

[GENERAL] Data sets for download

Hello all, Does anyone know of reasonably-sized data dumps (csv or excel or xml..) that can be used for learning/teaching about performance tuning. Say - a set of 6-7 tables, may be two of them with a few million records etc? Total data volume would be in a few GB range. There are tools which ge

[GENERAL] Replication - doubts

Hello all, I went through the documentation on replication. and am trying to understand the various options available. Does replication available in PostgreSQL always use WAL (i.e irrespective of whether it is synchronous/asynchronous, whether it is using archived logs or 'live' logs or streamin

Re: [GENERAL] PostgreSQL data loads - turn off WAL

Hi, > Basically my question is: > Is there currently any way to avoid wal generation during data load for given > tables and then have point in time recovery after that? Please have a look at unlogged and temporary options here - http://www.postgresql.org/docs/9.1/static/sql-createtable.html I don

Re: [GENERAL] How to isolate the result of SELECT's?

Hi, > > I need to do an operation that I will use some SELECT's and get the > results, but I want to have sure that those tables have not been > changed with INSERT's or UPDATES during the operation. > > Example: > > BEGIN OPERATION > Select field from table1; > ... > Select other_field from tab

Re: [GENERAL] Hope for a new PostgreSQL era?

Hello, > > Some of the features currently under development right now will make > this sort of thing easier to build into the core database. For > example, the recent "Command Triggers" feature submission will make > it easier to catch DDL changes as well as queries for this sort of thing. Tha

Re: [GENERAL] Hope for a new PostgreSQL era?

Hello, > Absolutely. SystemTap would be useful if I'm able to reproduce the situation > outside the production system. However, in most cases, it would be actually > difficult. At the db level, Oracle provides "Database replay" feature. that lets you replay the production server events in the de

Re: [GENERAL] Conceptual Architecture

Hello, > > Could you please point us to a simple white paper/doc which describes > > the Conceptual Architecture of PostgresSQL? I found these very useful. http://www.postgresql.org/files/developer/tour.pdf http://www.postgresql.org/files/developer/internalpics.pdf http://www.westnet.com/~gsmith/co

Re: [GENERAL] Help on PostgreSQL

Hello, > 2.Is there any enterprise version available with all features? We just completed migrating one of our products to PostgreSQL and load testing it. My suggestion- if your product uses stored procedures/packages heavily, have a look at EnterpriseDB. Otherwise, try plain simple PostgreSQL.

Re: [GENERAL] Materialized views in Oracle

Hello, .. > up front, I'm not a database expert by any means and I'm nowhere close > to an Oracle expert, or even an Oracle novice. If Oracle was a > swimming pool, I would have those little floaty duck things on my > arms. Please go through this link http://asktom.oracle.com/pls/asktom/f?p=100:1

Re: [GENERAL] How to convert ByteA to Large Objects

> > Thank you. We are working on an Oracle to PostgreSQL migration project. > > BLOB columns got converted to BYTEA in PostgreSQL and we ran into problems. > > We used this to convert the data type to OID. Thank you. > > you probably should detail the problems you ran into. large objects > no

Re: [GENERAL] Complex query question

Hello, > I have a complex query question whose answer I think would help me to > understand subselects and aggregates better. I have a table with four > columns of interest: > > id (int primary key), loc_title (varchar null), loc_value (float > null), loc_unit (varchar null) > > I want the output

Re: [GENERAL] COPY FROM how long should take ?

> Date: 08/26/2011 05:40 PM > Subject: [GENERAL] COPY FROM how long should take ? > Sent by: pgsql-general-ow...@postgresql.org > > Hello, > today I try to restore on of my tables with copy from file that I made > before. > The file is 2.4 GB, only integers ... took me 3 hours and 30 min and I >

Re: [GENERAL] Getting value of bind variables

Hi, Thank you for the reply. > > I am trying to debug a query that gives an error. The error in the > > application server log is > > " ERROR: operator does not exist: timestamp without time zone = character > > varying > > > It looks like the error is for the condition ( CUSINDINF.MEMDOB ) =

[GENERAL] Getting value of bind variables

Hell l, I am trying to debug a query that gives an error. The error in the application server log is " ERROR: operator does not exist: timestamp without time zone = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Po

Re: [GENERAL] PostgreSQL documentation on kindle - best practices?

> > So my question: has anyone found a best practice solution to convert > the PostgreSQL documentaiton into a kindle-friendly format? Or has > even an .azw file downloadable somewhere? > > Best wishes, > > Harald You could always send the pdf file and get it converted to kindle format, free of

Re: [GENERAL] How to restore from backup to 8.4.3 server using 9.0 dump/restore

Hello, > I don't know for sure, but I don't see why it should fail - it's only > reading it, not writing data to it or making any changes. Probably it will fail... http://www.postgresql.org/docs/9.0/static/sql-createdatabase.html Although it is possible to copy a database other than template1 by

Re: [GENERAL] Debug advice when postgres connection maxing out

Hello, > > Server: OS X 10.5 > PostgreSQL version: 8.3 > > We experience this connection maxing out once in the full moon. > The request from client reaches to the server but client never > receive response back. > The queries are very simple update on one record or select one > record using p

Re: [GENERAL] gap between error and cure

Hello, > > I saw a reference to run a command "show hba_file" but there hasn't been any > clue as to WHERE one runs that command. Please hold my hand. Whichuser (root > or postgres) and which program? Please try the command from psql. postgres=# select user; current_user -- po

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

Hello, > > Thank you for the reply. But my doubt was not about layout, rather the > > DMLs. If I do an insert into an 'unlogged' table, what happens to that? > > Will that be replicated in the slave (using PostgreSQL's inbuilt > > replication)? > > What are the use-cases for replicating unlo

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

Hi, > > One doubt - if the tables are 'unlogged' , will the DMLs against these > > still be replicated to a slave? > > > > Yes, because the system tables, which store the layout of all tables, is > written to WAL. Thank you for the reply. But my doubt was not about layout, rather the DMLs. If

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

Hello, > > PostgreSQL 9.1 is likely to have, as a feature, the ability to create > > tables which are "unlogged", meaning that they are not added to the > > transaction log, and will be truncated (emptied) on database restart. > > Such tables are intended for highly volatile, but not very valuable

Re: [GENERAL] How to search ignoring spaces and minus signs

Hello, > > For example searching for code 12344 should return > 12 3-44 as matching item. > > Andrus. > This will do? postgres=# select name from myt; name 13-333-333 12 3-44 33 33 333 12345 (4 rows) postgres=# select * from myt where translate(translate(name,'-',''),' '

Re: [GENERAL] [9.0] hot standby plus streaming replication

> > P.s. Glad to see that also in Italy there are PostgreSQL guru ;) > Glad to see that more people are using Indian words (Guru) :) http://en.wikipedia.org/wiki/Guru Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom

Re: [GENERAL] Monitoring object usage?

Hi, > From: "Colin 't Hart" > To: pgsql-general@postgresql.org > Date: 15/09/2010 17:37 > Subject: [GENERAL] Monitoring object usage? > Sent by: pgsql-general-ow...@postgresql.org > > Hi, > > I'm trying to get a grip on a new body of code and a Postgres > database that I'm working with. In par

Re: [GENERAL] Postgres 32bit on Windows 64bit, related components

> We're about to purchase a new server for our Postgres 8.4 database. > We’d like to go with Windows 64bit for possible future developments, > but are happy to stick with 32bit Postgres + Npgsql, ODBC, OpenSSL, > slony2 and libxml2, libpq. Any specific reasons for choosing Windows? Once in a whil

Re: [GENERAL] Jira and PostgreSQL

> Our admin team just moved our Jira from MySQL to PostgreSQL, but I > can't recall the exact reasons anymore. So far we do not have any > problems (from an end-user perspective that is) > Thanks for the reply. We plan to use it for our helpdesk, expecting it to be up and running 24*7 (or as

[GENERAL] Jira and PostgreSQL

Hello all, Has any one worked with Jira on PostgreSQL? We are considering Jira implementation for our organization (about 1500 users). The question is - "Jira on MySQL or Jira on PostgreSQL?" Any tips/suggestions are welcome. We do not have much expertise in either of these databases. Oracle,

Re: [GENERAL] delete query taking way too long

> From: Ivan Sergio Borgonovo > To: pgsql-general@postgresql.org > Date: 12/08/2010 16:43 > Subject: [GENERAL] delete query taking way too long > Sent by: pgsql-general-ow...@postgresql.org > > I've > delete from catalog_items where ItemID in (select id from > import.Articoli_delete); Does catalo

Re: [GENERAL] PostgreSQL and distributed transactions

Hello, > > > > See about 2 Phase Commit, but you will need a transaction manager I guess : > > > > http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html > > > > If I may be allowed to toot my own horn, may I suggest this as an example: > http://blog.endpoint.com/2010

[GENERAL] PostgreSQL and distributed transactions

Hello everyone, I saw a question about PostgreSQL and distributed transactions in the mail archives. But it looked a bit old. I am hoping things have changed and hence this mail. We have a database for 'Admin' which will be one PostgreSQL server. We have different servers for our 'Clients'. We d

Re: [GENERAL] Rescue data after power off

> Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. The "Power Off" - is it a planned/regular one? If yes, it makes sense to have a normal shutdown of the database before the "Power Off". You said "some of table is blank". If those table

[GENERAL] PostgreSQL PITR - more doubts

Hello all, One doubt about how PostgreSQL PITR works. Let us say I have all the archived WALs for the past week with archive_command = 'cp -i %p /home/postgres/archive/%f http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Queries about PostgreSQL PITR

Hi, >Because you didn't disable recovery_target_inclusive, I guess. > http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE Thanks. I was almost sure this will fix it. But the issue seems to be something else. Even if I give a time that is a few more minutes

[GENERAL] Queries about PostgreSQL PITR

Hello all, I am learning PostgreSQL PITR. I have PostgreSQL running with the following parameters set up for archiving/log switching: archive_mode = on archive_command = 'cp -i %p /home/postgres/archive/%f http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to Get Column Names from the Table

> Use: > \d tablename And what I really like about it is the way you can make a guess about the table name and use * . postgres-# \d mt* Table "public.mt1" Column | Type | Modifiers +-+--- id | integer | Table "public.mt2" Column | Type | Modi

Re: [GENERAL] Does enterprisedb.com down?

Hello, > The EnterpriseDB binaries are not on the community ftp servers, and > can't be because some of the community servers are in the USA, and those > USA servers might not block embargoed countries. Thanks for the clarification. We are in a very early stage of evaluating EnterpriseDB for our

Re: [GENERAL] Does enterprisedb.com down?

May be you will be able to get one that is not blocked from the ftp sites list? I don't know if the rules applicable to main server are automatically applied to the mirror sites too. http://wwwmaster.postgresql.org/download/mirrors-ftp Regards, Jayadevan DISCLAIMER: "The information in thi

  1   2   >