Re: [GENERAL] Are there rules for add parameter in postgresql.conf?

On 4/22/2014 8:07 PM, xbzhang wrote: I want to add some function for INSERT statement, but i can not decide that i need add one parameter in postgresql.conf or need add parameter in INSERT statement. Are there some rules for developer to decide it ? your request is completely unclear? noth

[GENERAL] Are there rules for add parameter in postgresql.conf?

I want to add some function for INSERT statement,?but i can not decide that i need?add one parameterin postgresql.conf or need?add?parameter in INSERTstatement.Are there some rules for developer to decide it ? 张晓博?? 研发二部 北京人大金仓信息技术股份有限公司 地址：北京市海淀区上地西路八号院上地科技大厦4号楼501 邮编：100085 电话：(010)

Re: [GENERAL] Is it good to have toast table for information schema table?

On 04/22/2014 04:58 PM, AI Rumman wrote: Hi, Is it good to have toast table for information schema table? I am using Postgresql 8.4 and current state is: *select datname, datfrozenxid from pg_database; * datname | datfrozenxid ---+-- template1 | 1462730397 template

[GENERAL] Is it good to have toast table for information schema table?

Hi, Is it good to have toast table for information schema table? I am using Postgresql 8.4 and current state is: *select datname, datfrozenxid from pg_database; * datname | datfrozenxid ---+-- template1 | 1462730397 template0 | 1462741467 postgres | 1562

Re: [GENERAL] Using 9.3 as a slave to 9.1 for upgrade purposes

On 04/22/2014 03:52 PM, Wells Oliver wrote: As a way of upgrading, I'd like to setup a 9.3 cluster as a slave to a 9.1 master so that I can then promote that 9.3 instance to master, using streaming replication. Curious if this is a possible/advisable route. Not possible with streaming replicat

Re: [GENERAL] Using 9.3 as a slave to 9.1 for upgrade purposes

On Tue, Apr 22, 2014 at 03:52:19PM -0700, Wells Oliver wrote: > As a way of upgrading, I'd like to setup a 9.3 cluster as a slave to a 9.1 > master so that I can then promote that 9.3 instance to master, using streaming > replication. > > Curious if this is a possible/advisable route. Streaming r

[GENERAL] Using 9.3 as a slave to 9.1 for upgrade purposes

As a way of upgrading, I'd like to setup a 9.3 cluster as a slave to a 9.1 master so that I can then promote that 9.3 instance to master, using streaming replication. Curious if this is a possible/advisable route. -- Wells Oliver wellsoli...@gmail.com

[GENERAL] aggregate returning anyarray and 'cannot determine result data type'

Hi all, I needed to implement an aggregate producing a random sample, with an upper bound on the number of items. I.e. not the usual "5% of values" but "up to 1000 values". So my plan was to do something like this: sample_append(internal, anyelement, int) -> internal sample_final(internal) -

Re: [GENERAL] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:39 PM, Igor Neyman wrote: > > > From: pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] On Behalf Of Craig Libscomb > Sent: Tuesday, April 22, 2014 4:27 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Can't delete role because

Re: [GENERAL] [SOLVED] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:30 PM, Tom Lane wrote: > > Craig Libscomb writes: > > The following command: > > DROP USER IF EXISTS jpate; > > > generates the following output: > > ERROR: role "jpate" cannot be dropped because some objects depend on it > > DETAIL: 1 object in database products > > >

Re: [GENERAL] Can't delete role because of unknown object

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig Libscomb Sent: Tuesday, April 22, 2014 4:27 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Can't delete role because of unknown object On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'D

Re: [GENERAL] Can't delete role because of unknown object

On 22/04/2014 21:26, Craig Libscomb wrote: > On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell > wrote: > > On 22/04/2014 20:47, Craig Libscomb wrote: > > The following command: > > DROP USER IF EXISTS jpate; > > > > generates the following output: > >

Re: [GENERAL] Can't delete role because of unknown object

Craig Libscomb writes: > The following command: > DROP USER IF EXISTS jpate; > generates the following output: > ERROR: role "jpate" cannot be dropped because some objects depend on it > DETAIL: 1 object in database products > It would be most helpful to know what object in the products databa

Re: [GENERAL] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:06 PM, Raymond O'Donnell wrote: > On 22/04/2014 20:47, Craig Libscomb wrote: > > The following command: > > DROP USER IF EXISTS jpate; > > > > generates the following output: > > ERROR: role "jpate" cannot be dropped because some objects depend on it > > DETAIL: 1 obje

Re: [GENERAL] Can't delete role because of unknown object

On Tue, Apr 22, 2014 at 3:02 PM, droletguillaume wrote: > hi Craig, > > I think this thread could help you: > > > http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it > > Sent from Samsung Mobile > As far as I can tell, there are no co

Re: [GENERAL] Can't delete role because of unknown object

On 22/04/2014 20:47, Craig Libscomb wrote: > The following command: > DROP USER IF EXISTS jpate; > > generates the following output: > ERROR: role "jpate" cannot be dropped because some objects depend on it > DETAIL: 1 object in database products > > It would be most helpful to know what object

Re: [GENERAL] Can't delete role because of unknown object

hi Craig,  I think this thread could help you: http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it Sent from Samsung Mobile Original message From: Craig Libscomb Date:04-22-2014 15:47 (GMT-05:00) To: pgsql-gene

[GENERAL] Can't delete role because of unknown object

The following command: DROP USER IF EXISTS jpate; generates the following output: ERROR: role "jpate" cannot be dropped because some objects depend on it DETAIL: 1 object in database products It would be most helpful to know what object in the products database depends on the jpate role, but I

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

On 22 April 2014 17:13, basti wrote: > 2014-04-22 11:01:42 CEST LOG: could not open temporary statistics file > "/run/shm/pgstat.tmp": Permission denied > > I don't understand what's going on there: > > postgres@srv1:/home/postgresql_data/postgresql/9.1/main/pg_log$ date && > ls -la /run/shm/ > T

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

On 04/22/2014 08:13 AM, basti wrote: I use Debian 7, Postgres 9.1, Kernel 3.2.0-3-amd64 Syslog says nothing about postgres during this time. SELinux is not installed/active. I also found this (2 hours later) in the postgres log: 2014-04-22 11:01:42 CEST LOG: could not open temporary statistics

Re: [GENERAL] Is this a planner bug?

=?UTF-8?B?VG9yc3RlbiBGw7ZydHNjaA==?= writes: > Using the index: > Limit (cost=0.57..2.95 rows=1 width=0) >(actual time=0.095..0.095 rows=1 loops=1) >-> Index Scan ... (cost=0.57..14857285.83 rows=6240539 width=0) > (actual time=0.095..0.095 rows=1 loops=1) >

Re: [GENERAL] Is this a planner bug?

On 22/04/14 16:45, Tom Lane wrote: > No. The model is that startup cost is what's expended before the scan can > start, and then the run cost (total_cost - startup_cost) is expended while > scanning. Applying a filter increases the run cost and also reduces the > number of rows returned, but that

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

I use Debian 7, Postgres 9.1, Kernel 3.2.0-3-amd64 Syslog says nothing about postgres during this time. SELinux is not installed/active. I also found this (2 hours later) in the postgres log: 2014-04-22 11:01:42 CEST LOG: could not open temporary statistics file "/run/shm/pgstat.tmp": Permission

Re: [GENERAL] Is this a planner bug?

On 22/04/14 16:39, Albe Laurenz wrote: > Could you run EXPLAIN ANALYZE for the query with enable_seqscan > on and off? I'd be curious > a) if the index can be used > b) if it can be used, if that is actually cheaper > c) how the planner estimates compare with reality. > Using the index: Limit

Re: [GENERAL] importing downloaded data

On 04/22/2014 07:39 AM, Sim Zacks wrote: Postgresql 9.3 I am downloading data that I want to import into a table. The data comes in tab delimited, CRLF format. I am using plpython to get the data and I wanted to use copy with stdin to import it without having to save it to a file. To do this, I a

Re: [GENERAL] Is this a planner bug?

=?UTF-8?B?VG9yc3RlbiBGw7ZydHNjaA==?= writes: > What I'm asking is the following. Assuming node without any filter has a > startup cost C1, a total cost of C2 and produces N rows. Now, a filter > is applied which passes through M rows. Then the startup cost for the > node *with* the filter applied

Re: [GENERAL] Is this a planner bug?

> Torsten Förtsch wrote: >>> I got this plan: >>> >>> Limit (cost=0.00..1.12 rows=1 width=0) >>>-> Seq Scan on fmb (cost=0.00..6964734.35 rows=6237993 width=0) >>> Filter: ... >>> >>> The table has ~80,000,000 rows. So, the filter, according to the plan, >>> filters out >90% of the

[GENERAL] importing downloaded data

Postgresql 9.3 I am downloading data that I want to import into a table. The data comes in tab delimited, CRLF format. I am using plpython to get the data and I wanted to use copy with stdin to import it without having to save it to a file. To do this, I am setting sy

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

On 04/22/2014 07:11 AM, basti wrote: When Postgres has no permission why "pgstat.stat" is created by postgres? I have also try to create a file via su postgres -c "cd /run/shm/ && touch " /run# ls -la shm total 52 drwxrwxrwt 2 root root 100 Apr 22 16:09 . drwxr-xr-x 20 root ro

Re: [GENERAL] Is this a planner bug?

On 22/04/14 14:24, Pavel Stehule wrote: > what is your effective_cache_size in postgresql.conf? > > What is random_page_cost and seq_page_cost? > 8GB, 4, 1 But I am not asking about how to get a different plan or how to optimize the query. I know that. What I'm asking is the following. Assumin

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

When Postgres has no permission why "pgstat.stat" is created by postgres? I have also try to create a file via su postgres -c "cd /run/shm/ && touch " /run# ls -la shm total 52 drwxrwxrwt 2 root root 100 Apr 22 16:09 . drwxr-xr-x 20 root root 660 Apr 8 11:15 .. -rw-r--r--

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

On 04/22/2014 02:39 AM, basti wrote: Hello, since I have enabled "stats_temp_directory = '/run/shm'" in /etc/postgresql/9.1/main/postgresql.conf I get the following error: 2014-04-21 02:37:29 CEST LOG: could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat":

Re: [GENERAL] Is this a planner bug?

Hello what is your effective_cache_size in postgresql.conf? What is random_page_cost and seq_page_cost? Regards Pavel 2014-04-22 14:10 GMT+02:00 Torsten Förtsch : > Hi, > > I got this plan: > > Limit (cost=0.00..1.12 rows=1 width=0) >-> Seq Scan on fmb (cost=0.00..6964734.35 rows=62379

Re: Fwd: [GENERAL] How to ignore blank lines with file_fdw

On 22/04/14 21:09, Nicklas Avén wrote: Hallo I am struggling to find the best solution to ignore blank lines in csv-file when using file_fdw. A blank line makes the table unreadable. I would like to avoid manipulating the file directly and avoid the need to make a new corrected copy of the fi

Re: [GENERAL] hstore binary representation of keys

Currently hstore is mongodb. It writes the keys everytime (and values as strings!, its mostly for dynamic keys or very sparse keys in my opinion). You can shorten keys,or put them in dedicated columns. I haven't read that there is a plan to compress the strings. On Tue, Apr 22, 2014 at 2:01 PM,

Re: [GENERAL] How to ignore blank lines with file_fdw

Nicklas Avén wrote: > I have also found in an email from 2011 > http://www.postgresql.org/message-id/4e699de6.8010...@gmail.com > > that when force_not_null was implemented in file_fdw the patch also included > "some cosmetic changes > such as removing useless blank lines." That is refering to b

[GENERAL] Is this a planner bug?

Hi, I got this plan: Limit (cost=0.00..1.12 rows=1 width=0) -> Seq Scan on fmb (cost=0.00..6964734.35 rows=6237993 width=0) Filter: ... The table has ~80,000,000 rows. So, the filter, according to the plan, filters out >90% of the rows. Although the cost for the first row to come

[GENERAL] hstore binary representation of keys

Hi all, I’ve been using hstore to record various key/val pairs, but I’ve noticed it consumes a lot more disk than I would have expected. I don’t have any hard figures to illustrate, but empirical testing has shown that if I record these pairs as traditional column based fields, I can save a signif

[GENERAL] How to ignore blank lines with file_fdw

Hallo I am struggling to find the best solution to ignore blank lines in csv-file when using file_fdw. A blank line makes the table unreadable. I would like to avoid manipulating the file directly and avoid the need to make a new corrected copy of the file. I am on Linux so I have found a

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

/run/shm and /var/run/shm would be the same. On my system I found no symlink to each other but there is the same content. Basti On 22.04.2014 12:02, Tomáš Vondra wrote: > Seems you're using /run/shm and /var/run/shm by mistake. > > Tomas > > basti napsal/a: > >> Hello, >> >> since I have enabl

Re: [GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

Seems you're using /run/shm and /var/run/shm by mistake. Tomas basti napsal/a: >Hello, > >since I have enabled "stats_temp_directory = '/run/shm'" in >/etc/postgresql/9.1/main/postgresql.conf I get the following error: > >2014-04-21 02:37:29 CEST LOG: could not rename temporary statistics >file

[GENERAL] could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory

Hello, since I have enabled "stats_temp_directory = '/run/shm'" in /etc/postgresql/9.1/main/postgresql.conf I get the following error: 2014-04-21 02:37:29 CEST LOG: could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory /var/run/shm

Re: [GENERAL] Stored procedures and schema renames

On 04/10/2014 03:29 PM, Rob Sargent wrote: Code for db functions should be a repository. Easy edit and rerun Well, not necessarily inside the transaction that renames the schema. I've settled for this inside the transaction (running as a superuser): UPDATE pg_proc SET proconfig = '{

Re: [GENERAL] Make basebackup use low level API and rsync

PostgreSQL 9.2.4 On Tue, Apr 22, 2014 at 1:58 PM, Michael Paquier wrote: > > > > On Tue, Apr 22, 2014 at 1:28 PM, wd wrote: > >> I'v try to make a base backup use pg_start_backup, pg_stop_backup and >> rsync, but failed. >> >> After run select pg_start_backup('label') on db server, I run rsync