Re: [GENERAL] Postgresql Error ask for password

Pedro Santos wrote: > Im trying to install one application in my Linux but when I try to use the > expression SU postgresql it > ask for password, what is the password? I try my root password but dont work. It may be that you never set that password. Try "grep postgresql /etc/shadow" as root use

Re: [GENERAL] PQfformat question and retrieving bytea data in C

Jason Armstrong wrote: > I have updated my C library to return the binary data correctly. I > note the restriction on not being able to retrieve different columns > in different formats. Actually, PostgreSQL supports that if you use the line protocol to talk to the server (see the description of "

Re: [GENERAL] Need help on autovacuum in postgres 9.1.2

Khangelani Gama wrote: > I need help in turning off autovacuum in the Database that's running postgres 9.1.2 DB. I tried to > turn it off by putting "off" in postgresql.cont file and restarted the postmaster but when I run "show > autovacuum; " query I still see autovacuum set on inside the databas

Re: [GENERAL] Understanding autocommit

Chris Angelico wrote: > I'm looking at these two pages: > > http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html > http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html > > According to the first page, autocommit is off by default for > "embedded SQL programs". Do

Re: [GENERAL] .Net/C# - How to use Entity Framework Code First with Npgsql?

Hermano Cabral wrote: > Does anyone know if its possible to use entity framework code first with the > npgsql connector? I know > devart's connector does the job, but I'm low on funds for this project and > their stuff is not cheap. Yes, Npgsql supports Entity Framework. For questions concernin

Re: [GENERAL] Best practice non privilege postgres-user

Frank Lanitz wrote: > I'm looking for some kind of best practice for a non-privilege postgres > user. As not all operations can be done within psql you might need > access to postgres- on command line from time to time. Currently this is > done via root-privvileges and >su - postgres< directly on d

Re: [GENERAL] More probs with BLOBs

Neanderthelle Jones wrote: > We are getting a strange thing happening if the lo_export(attr, path) > destination is a fifo. > > First, in the normal case, there is output to the file but also > feedback to stdout (or somewhere, appearing on the VT screen) of the > number 1. > > --- >

Re: [GENERAL] Migrating Postgresql 8.0 to 8.3

quickinfo wrote: > I would like to migrate postgresql 8.0 to 8.3. Please suggest me the best way of restoring my backup. > > In which format should I take the backup and how should I restore the data. Does it require to write > any sql script. will it create any problems in migrating 8.0 to 8.3. F

Re: [GENERAL] Postgres and Upstart

Chris Angelico wrote: > I'm looking for a reliable way to be sure that Postgres has finished > its initialization and is ready to rumble. The normal way to test this is a connection attempt. You could also look into the server logs for the appropriate message, but that seems more fragile and diffi

Re: [GENERAL] Function to dump function ddl

Douglas Little wrote: > In deploying new versions of function source, I want to export the current definition to a file. > > After looking around it seems that I needed to create my own function. > > I got the function to work, but am having a slight problem with the execution of the exported fil

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

Craig Ringer wrote: >> Of course this is not always correct. >> But what should the result of >>INTERVAL '1 month' = INTERVAL '30 days' >> be? FALSE would be just as wrong. > NULL? In all honesty, it's a reasonable fit for NULL in its > "uncertain/unknowable" personality, because two interva

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

Dmitry Koterov wrote: >>> I've just discovered a very strange thing: >>> >>> SELECT '1 mon'::interval = '30 days'::interval --> TRUE??? >> Intervals are internally stored in three fields: months, days >> and microseconds. A year has 12 months. > >> PostgreSQL converts intervals into microsecond

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

Dmitry Koterov wrote: >> I've just discovered a very strange thing: >> >> SELECT '1 mon'::interval = '30 days'::interval --> TRUE??? >> >> This returns TRUE (also affected when I create an unique index using an >> interval column). Why? >> >> I know that Postgres stores monthes, days and seconds

Re: [GENERAL] warnings about invalid "search_path"

Samba wrote: > I'm seeing some weired errors in the postgres logs after upgrading > to postgres-9.1(.3) about the schema added by default to search patch > > WARNING: invalid value for parameter "search_path": "system_data" > DETAIL: schema "system_data" does not exist > > We do have a user nam

Re: [GENERAL] How does connect privilege works?

Shridhar Daithankar wrote: > I am trying to setup a cluster for trac databases and want to isolate each db, by assigning a specific > user to a DB. > > I followed the documentation but as shown in the following example, limiting access by connect does > not seem to be working. > > What am I missi

Re: [GENERAL] Problem setting environmental variables for postgresql or plpythonu

Mark Wynter wrote: > I''m hoping someone can help me out. I'm wanting to run GRASS GIS from within a plpythonu function > block. But to run GRASS GIS externally, the following environmental variables need to be available to > the Postgresql server... [...] > So far I've added these variables t

Re: [GENERAL] PostgreSQL index issue

mperformer wrote: > I have a question regarding PostgreSQL 9.1 indexing. > > I am having a table and want to create a index for a column and I want to store the data with time > zone for that column. The questions are: > > 1. Can I create a index for a column which store time stamp with time zone

Re: [GENERAL] is there any query so that I may find the list of columns those have rules?

AI Rumman wrote: > I am getting the following error: > ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6); > ERROR: cannot alter type of a column used by a view or rule > DETAIL: rule _RETURN on view master_view depends on column "base_table_field1" > > I know that I have a

Re: [GENERAL] PostgreSQL Slony-I Replication

Iqbal Aroussi wrote: > I'm trying to setup a master-slave replication with PostgreSQL 9.1 / Slony-I, this is first time I'm > doing it and I'm kind of lost :( > Is there any tutorial explaining the steps how to do it with FreeBSD ? > > I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 /

Re: [GENERAL] Regarding parallel running of 9.1 and 9.2beta2

Atri Sharma wrote: > I am running PostgreSQL 9.1 currently and I want to run 9.2beta2 > simultaneously with it.Is it possible? > > I downloaded the 9.2beta2 sources,untared them and ran > ./configure,make and make install without uninstalling 9.1. > > Now,when I am running pg_ctl start,I am getti

Re: [GENERAL] log_min_duration_statement modification for non superusers?

Paul McGarry wrote: > I have a situation where I'd like to lower the > log_min_duration_statement for a particular connection > . > The DB is used for several reporting type queries which may reasonably > take several seconds so the log_min_duration_statement in the DB is > set accordingly. > > Ho

Re: [GENERAL] dblink causing import errors

Christoph Zwerschke wrote: > Our developers like the dblink modules, so I have installed it into the > template1 database. They also like to import old database dumps after > creating new databases with dbcreate. But then they get irritated by the > error messages saying that the dblink functions a

Re: [GENERAL] Please make it easy to drop a database that is in use

Evan Martin wrote: > Like I said in my original post, I understand the workaround. I just > think that: > > 1) The workaround requires extra work for each developer (or at least > each client application) using PostgreSQL, while a fix in PostgreSQL > would solve this once and for all. For a devel

Re: [GENERAL] Fine-grained replication?

Paul Jones wrote: > Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the > lines of the XReader or pgreplay > ? I'm not sure what you want, because "pgreplay" is certa

Re: [GENERAL] full text index / search

Philipp Kraus wrote: > I have created a table with a text field under PG 9.1, that should store source codes. I would like to > search in this text field with regular expressions. I think I need a full-text-index, do I? > How can I create this index, do I need some additional extensions? The PG ser

Re: [GENERAL] parsing SQLERRM ?

david.sahag...@emc.com wrote: > (version == 9.1) > > In my PL/pgSQL stored functions, > I want to be able to distinguish which FK-constraint caused the [foreign_key_violation] exception. > . . . > BEGIN > delete from MY_COOL_TABLE where id = 123 ; > EXCEPTION > WHEN foreign_key_viola

Re: [GENERAL] Daisy chaining replication slaves ?

Rob Cowell wrote: > I'm just wondering if there is a way to slave from a slave server? > > I have a Postgres9.1.3 master serving up data quite happily to the web applications, and I have also > set up a slave via streaming replication. > > I've now been asked by my manager to set up a secondary s

Re: [GENERAL] How to create c language in postgresql database. Thanks.

leaf_yxj wrote: > Thanks for your answers. I really appreciate it. Although I don't understand the whole things you guys > mentioned to me. I think maybe I should do it by myself. I need to do a test. If there is any good > guide/white paper, please give me a link for me to study. I think the docu

Re: [GENERAL] Is there a way to ask PostgreSQL for the name of the computer it's running on?

Rob Richardson wrote: > My customer has 3 computers. The PostgreSQL service could be running on either of two of them. There > is currently no way in our system to determine which one it is running on. The third computer > sometimes needs to know which of the other two computers is active. It wo

Re: [GENERAL] Recovery continually requests new WAL files

Alex Good wrote: > Although pgpool is involved this isn't actually about pgpool, I've been > running through the recovery process manually to try and understand what > needs to be done in order to get onlinve recovery working with pgpool. > Pgpool isn't actually running at the moment. Oh, I see.

Re: [GENERAL] Recovery continually requests new WAL files

Alex Good wrote: > What I expected to see was the server requesting each WAL file up until > the one which was archived during pg_stop_backup and then the server > would consider itself to be recovered. Clearly I have misunderstood > something here. > > These two servers are actually sat behind pg

Re: [GENERAL] Recovery continually requests new WAL files

Alex Good wrote: > I have a simple setup with one master and one backup server. I have an > issue where I have performed a backup and copied it to the data > directory for the slave, written a recovery.conf and copied in the > backup_label file and then started the server, it happily restores > eve

Re: [GENERAL] acessibility for tables

Willy-Bas Loos wrote: > I did something like that some years ago. > Albe, are rules out of grace? Sort of, for many people: http://archives.postgresql.org/pgsql-hackers/2012-04/msg00395.php They are difficult to get right and usually not better than triggers. Ever since there were INSTEAD OF Trig

Re: [GENERAL] acessibility for tables

Kraus Philipp wrote: > I new on this mailing list and I need a little bit help for an idea to create different accesses to a > database with Postgres 9.1. > I'm using this PG version at the time and I have created a database with a scheme "storage". Within > this schema are some > tables, datatypes

Re: [GENERAL] db alias

Dave Gauthier wrote: > Is there a way to alias a db name for purposes of redirecting connections? For example, you have 2 > DBs, DBX and DBY. The users always connect to a DB alias called "USEDB". USEDB points to DBX today, > changed to point to DBY tomorrow, transparent to the users. That can

Re: [GENERAL] Partitioning Advice

Ben Carbery wrote: > I have a postgres server I need to move onto a new OS (RHEL6) on a new VM and > am looking for advice on > how to partition the disks to gain some performance improvement. > > In the current environment I am given a single VHDD which I have not > partitioned at all. The SAN

Re: [GENERAL] Is there a way to start postgresql v907 as non daemon process

Karthik wrote: > I have observed by default posgresql 8.x onwards starts as daemon process. Is there a way to avoid > it. I want it to run as normal process. The parent of the server process should be the process that > starts the server instead of being PID 1 ( init ). Till 7.4.2 ( Which was the

Re: [GENERAL] Concerning about Unicode-aware string handling

Vincas Dargis wrote: > We have problems (currently using 8.4, but also in latest 9.1.3) in > our application with Unicode word symbols in Lithuanian ('ąčęėįšųūž'), > Russian and of course potentially other languages. > > For example, regex_replace('acząčž', E'\\W', '', 'g') removes ąčž. > > lower

Re: [GENERAL] odd intermittent query hanging issue

Aaron Burnett wrote: >>> I run a handful of queries overnight when traffic is at it's lowest on our >>> system. One particular query will run perfectly fine (around 5 seconds0) >>> for several weeks, then suddenly decide to hang indefinitely and never >>> finish. It needs to be killed manually afte

Re: [GENERAL] archive_command and streaming replication

Scott Briggs wrote: > So if you do need to use wal files to catch up a slave, what would > that process be? If you caught up with wal files, how would streaming > replication know what positon to start at? And how would you tell > streaming replication the new position after catching up with wal >

Re: [GENERAL] Replication recovery?

John Mudd wrote: > Sorry if this is a dumb question. Feel free to just point me to a doc. Sure, here: http://www.postgresql.org/docs/current/static/warm-standby-failover.html > I've read a little about Postgres replication and the concept of a > master and one or more slaves. If one db is down th

Re: [GENERAL] vacuum, vacuum full and problems releasing disk space

Horaci Macias wrote: > after tuning the autovacuum settings I can now see the tables vaccumed > and the number of dead tuples dropping whenever an autovacuum happens, > which makes sense. Great. > What I don't see though is the size of the tables ever decreasing, but > I'm not sure I should see t

Re: [GENERAL] archive_command and streaming replication

Scott Briggs wrote: > Hi, can someone please explain the purpose of archive_command on both > the master and slave when it comes to streaming replication? From > what I understand so far, what really matters is how many pg_xlog > files are kept when it comes to reestablishing replication when it >

Re: [GENERAL] Cannot find installers for 9.2 Beta

Basil Bourque wrote: > Today's announcement of 9.2 beta said installers were available... > - > Get PostgreSQL 9.2 beta, including binaries and installers for Windows, Linux and Mac from our > download page: http://www.postgresql.org/download > - > > But I cannot find any installers at all for the

Re: [GENERAL] Sequence scan if "OR Condition" in where statement

Prashant Bharucha wrote: > Could you please help me , index is not using if have "OR condition in where statement" ? > > Always have sequence scan. You need to provide more details, see http://wiki.postgresql.org/wiki/SlowQueryQuestions to get an idea. Yours, Laurenz Albe -- Sent via pgsql-gen

Re: [GENERAL] vacuum, vacuum full and problems releasing disk space

Horaci Macias wrote: > I'm running postgres 9.1 and having disk space problems. > My application captures information 24x7 and stores it into the > database. This includes several bytea and can be ~5M entries a day, so > the size can be an issue after several days. > My application also cleans up e

Re: [GENERAL] config file question between versions 7.4 - 9.1

Randy Johnson wrote: > in the config file for 7.4 we have an entry: > > shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each > > in 9.1 the default is: > > shared_buffers = 32MB > > > max connections is the default 100 > > Do I need to make any adjustments or can I leave it at

Re: [GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

Dmitry Koterov wrote: > But it's quite strange that SQL+STABLE function does not recalculate the plan each time it is called. > Because when I use a bunch of SQL+STABLE functions in e.g. a sub-select of a complex query, I see in > the plan of this complex queries that function calls are "expanded".

Re: [GENERAL] spanish locale question

Tulio wrote: > Let me expand the collate situation. I´m from Perú and I have turned > everything in postgresql.conf as 'es_PE.UTF-8' even the > default_text_search_config = 'pg_catalog.spanish'. Even my Ubuntu 12.04 > works in English I have es_PE locale too. > if I do > > SELECT * FROM pru order

Re: [GENERAL] Significance of Postgres (version 9.1.1) Compilation parameters - Performance

Jayashankar K B wrote: > Could anyone explain me the significance of the following compile time parameters ? > > 1. --disable-integer-datetimes > > 2. --disable-float4-byval > > 3. --disable-float8-byval > > > > Do they have any effect on performance of postgres w.r.t speed of dat

Re: [GENERAL] Question on notifications

Alexander Reichstadt wrote: > From the documentation I was able to build a trigger firing upon deletion of a record a function that > delivers tablename_operation as a notification one needs to subscribe to. So in terminal I can say > LISTEN persons_delete and instantly will receive > > Asyn

Re: [GENERAL] Question on notifications

Alexander Reichstadt wrote: > Thanks, I had checked the example before but couldn't make sense out of it in terms of wrapping it in > Objective-C. I left it in C now and it works fine. > > The trigger I am using now looks like this: > > CREATE FUNCTION notify_trigger() RETURNS trigger AS $$ > >

Re: [GENERAL] postgresql log indicate too many execute S_2:COMMIT

leo xu wrote: > I test postgresql database performance recently,i set > log_min_duration_statement=1000; > i monitor pg_log file,i find too many execue S_2:commit > 10.0.44.21:30170:2012-04-28 11:38:46.340 CSTLOG: duration: 1050.679 ms > execute S_2: COMMIT > > 10.0.44.21:30208:2012-04-28 11:

Re: [GENERAL] problems after restoring from a pg_basebackup

Lonni J Friedman wrote: > I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it > matters) system. I noticed the existence of pg_basebackup starting in > 9.1, and figured I'd try it out and see if it would simplify our > backup & management processes. > > $ pg_basebackup -P -v -D /tmp/b

Re: [GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

Dmitry Koterov wrote: > For example, I have 2 functions like these: > > CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS ... AS > $body$ > ...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3... > $body$ > LANGUAGE 'sql' > STABLE > > > and > > > CREATE OR REPLACE F

Re: [GENERAL] R-tree parallel index creation

Pavel Iacovlev wrote: > Anyone know if this features is supported in PostgreSQL: > "R-tree index creation can be subdivided into smaller tasks that can be > performed in parallel" ? The PostgreSQL backend does not parallelize anything, including index builds. Yours, Laurenz Albe -- Sent via pg

Re: [GENERAL] Money in numeric field

Martín Marqués wrote: > I have a question involving money data stored in a numeric(9,2) field, > and posible errors with there manipulation. > > in short, the table has these columns: > > store: int > amount: int2 > cost: numeric(9,2) > > What I need to find is the total amount of money spent in

Re: [GENERAL] spanish locale question

jbiskofski wrote: > I have a lc_collate problem. Im in Mexico and I need the following three > lastnames to be sorted this > way : > > álvarez ( accent on first a ) > chavez > cota > > Using the default locale on my mac ( en_US ) I end up with : > > chavez > cota > álvarez > > So I switched to

Re: [GENERAL] aggregate arrays

Dmitry E. Oboukhov wrote: > example: > > a query returns a column that contains arrays: > > select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t; >column1 | column2 > -+- > {1,2,3,3,4} | 1 > {1,2,2,3,4} | 2 > (2 rows) > > and then we want

Re: [GENERAL] Unable to createlang

Tom Harkaway wrote: > The command I am using is: > > createlang -U postgres plperl hf-hvpa > > The message I get back is: > > createlang: language installation failed: ERROR: could not load library > "/opt/postgres/lib/postgresql/plperl.so": libperl.so: cannot open shared object file: N

Re: [GENERAL] what happens when concurrent index create

leo xu wrote: > i read documents,i find it that concurrent index create don't lock write.but > need scan table twice.it explain is following as: >It scans the table once to initially build the index, then makes a second > pass to look for things added after the first pass. > > please explain 1

Re: [GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

leaf_yxj wrote: > My bosses ask me to list > > 1)all the users and the roles associated with the users. This will list all roles in the cluster, whether they can login (are users) or not, and the array of all roles they are directly or indirectly members of: WITH RECURSIVE is_member_of(member, r

Re: [GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

Tom Lane wrote: >>> My bosses ask me to list all the users and all the privilege which >> the superuser granted to the >>> users. >>> Then they can double check that I did right thing or not? > >> Unlike Oracle, PostgreSQL does not have a concept of "grantor", >> so it is not possible to find ou

Re: [GENERAL] octet_length operator: what encoding?

Chris Angelico wrote: > We have a number of varchar fields and I'm looking to see what the > greatest data length in any is, after UTF-8 encoding. The two-argument > length function appears (I think) to take a byte array, so it's the > opposite of what I'm looking for (give it a UTF-8 encoded strin

Re: [GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

leaf_yxj wrote: > My bosses ask me to list all the users and all the privilege which the superuser granted to the > users. > Then they can double check that I did right thing or not? Unlike Oracle, PostgreSQL does not have a concept of "grantor", so it is not possible to find out which privilege

Re: [GENERAL] More PG Log

Arvind Singh wrote: > Query 1 > --- > do we have a standard list of following Log Codes > - Command_tag ex. IDLE, SELECT .. See the source code for your version: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/ tcop/utility.c;h=ec36644a492ab69d5306b52294daab0599f332

Re: [GENERAL] PG Log

Arvind Singh wrote: > I have queries regarding columns in Postgres CSV Log. > > Following is a sample Logline > > 2012-03-28 19:25:47.968 IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET", 2012- > 03-28

Re: [GENERAL] Query regarding submission on To Do item for psql client "psql : Allow processing of multiple -f (file) options "

Vikash3 S wrote: > Would like to submit patch on this TO Do list item which deals with psql client, "psql : Allow > processing of multiple -f (file) options ". > > The code base which I am working on is from postgres 9.1.3 release. > But when I diff the code base from git repository, the changes a

Re: [GENERAL] system catalog privilege and create privilege ??? how to control them?? thanks

leaf_yxj wrote: > For oracle, the normal user can't see all the system catalog. but for > postgresql, it looks like all the user can see the system catalog. Should > we limit the user read privilege to system catalog? You can try that, but things may break in unexpected ways. For example, psql's u

Re: [GENERAL] Multiple Slave Failover with PITR

Ken Brush wrote: > I notice that the documentation at: > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial > > Doesn't contain steps in a Multiple Slave setup for re-establishing > them after a slave has become the new master. > > Based on the documentation, here are the most fail-proof

Re: [GENERAL] Facing error while restoring the database

Akshay Joshi wrote: > I am facing issue while restoring the database. I have taken the backup of my database using pg_dump > and then create new database and try to restore it using pg_restore. I am using PostgreSQL 9.0. > I have created one small test case to reproduce the issue, attached is the s

Re: [GENERAL] Parsing PG log usings CSV format

Arvind Singh wrote: > i have sorted out the problem on > " The columns that are not quoted are guaranteed not to contain a comma. " Good. > But i have another query, the structure of PG Log CSV as mentioned in manual and as below has 24 > columns > http://www.postgresql.org/docs/9.1/static/runtim

Re: [GENERAL] Help in Parsing PG log usings CSV format

Arvind Singh wrote: > Help needed in parsing PostgreSQL CSV Log [...] > **However the main problem that is, the Log format is not readable** > A Sample Log data line > > 2012-03-21 11:59:20.640 > IST,"postgres","stock_apals",3276,"localhost:1639",4f697540.ccc,10,"idle ",2012-03-21 11:59:20 > IST

Re: [GENERAL] Cannot store special chars using c lib

Alexander Reichstadt wrote: > I need to be able to store special chars, German Umlaute, in my tables. This > works when using > pgPHPAdmin to store the same value to the same field. But when using the > c-library it doesn't, fields > stored are garbled. > > I checked using \l to see what encodin

Re: [GENERAL] Slow information_schema.views

Oliver Kohll - Mailing Lists wrote: > I'm doing some SELECTs from information_schema.views to find views with dependencies on other views, > i.e. > > SELECT table_name FROM information_schema.views WHERE view_definition ILIKE '%myviewname%'; > > and each is taking about 1/2 a second, which is get

Re: [GENERAL] POSTGRESQL Newbie

"Geek Matter" wrote: >> Skype. And pgsql has some great replication solutions that actually work > any other large sites use postgresql? i need to make right descission coz my decision will affect > business that is related with $ http://archives.postgresql.org/pgsql-advocacy/2002-08/msg5.ph

Re: [GENERAL] Conditionnal validation for transaction

Florent THOMAS wrote: >>> 1 - Is there a way to have conditions for committing transactions like in oracle : >>> http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-trans action-62 >> >> PostgreSQL follows the SQL standard which does not allow anything like that. >> >> Later versio

Re: [GENERAL] WHERE IN (subselect) versus WHERE IN (1,2,3,)

Kevin Goess wrote: > We have a table "contexts" with 1.6 million rows, and a table "articles" with 1.4 million rows, where > an "article" is a particular kind of "context". We want to select from a join on those two tables > like this > > SELECT COUNT(*) > FROM contexts > JOIN article

Re: [GENERAL] how to measure wal_buffer usage

Lonni J Friedman wrote: >>> After reading this interesting article on shared_buffers and wal_buffers: >>> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html >>> >>> it got me wondering if my settings were ideal.  Is there some way to >>> measure wal_buffer usage in real time

Re: [GENERAL] how to measure wal_buffer usage

Lonni J Friedman wrote: > After reading this interesting article on shared_buffers and wal_buffers: > http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html > > it got me wondering if my settings were ideal. Is there some way to > measure wal_buffer usage in real time, so that

Re: [GENERAL] Backups

Richard Harley wrote: > Very simple question - does pg_dump/dumpall hit the server in terms of database performance? We > currently do nightly backups and I want to move to hourly backups but not at the expense of hogging > all the resources for 5 mins. > > Pg_dumpall is currently producing a 1GB

Re: [GENERAL] Frontend/Backend protocol question.

Dmitriy Igrishin wrote: > According to > http://www.postgresql.org/docs/9.1/static/protocol-flow.html#AEN91458 > "is not actually necessary for the frontend to wait for > ReadyForQuery before issuing another command". > > But is it necessary for frontend to wait for ReadyForQuery > before sending

Re: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?

Selena Deckelmann wrote: > On Thursday, March 8, 2012 at 11:40 AM, Stefan Keller wrote: >> I do have a student who is interested in participating at the Google >> Summer of Code (GSoC) 2012 >> Now I have the "burden" to look for a cool project... Any ideas? > > Also those who are on this thread, w

Re: [GENERAL] "invalid memory alloc request size" + "Could not open file "pg_clog/XXXX"

scheu_postgresql wrote: > In my Postgresql 8.4.0 server, since this morning some tables are unavailable, see example below : > > --> pg_dump MY_DB > bkp_MY_DB.dmp > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613 > pg

Re: [GENERAL] Yearly date comparison?

Nick wrote: > What is the best way to find an event with a yearly occurrence? > > CREATE TABLE events ( > start_date DATE, > end_date DATE, > recurring TEXT > ); > INSERT INTO events (start_date, end_date, recurring) VALUES > ('2010-02-28','2010-03-01','yearly'); > > SELECT * FROM eve

Re: [GENERAL] explain and index scan

Andreas wrote: > PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit > > id | integer | not null Vorgabewert nextval('a_id_seq'::regclass) > a | integer | not null > b | integer | not null > Indexe: > "a_pkey" PRIMARY KEY, btree (id) > "a_a_k

Re: [GENERAL] COPY TO File: Using dynamic file name in stored procedure

Carlos Oliva wrote: > What would it be the correct format for using a variable in a stored procedure that uses COPY TO? > > I have the current stored procedure: > CREATE FUNCTION Table_To_File(text) RETURNS void AS $delimeter$ >COPY (SELECT * FROM table_to_xml('table', true, false, '')) TO '$1

Re: [GENERAL] How to enable thread safety on postgresql 8.3.6

ultrayoYO wrote: > I need to enable option --enable-thread-safety after install completed. > Do you have any way for this case. Because I try to remove and reinstall by > enable thread safety. > But not update feature --enable-thread-safety Postgresql config still value > config in the first insta

Re: [GENERAL] Postgresql as main database

Twaha Daudi wrote: > I would like to make Postgresql 8.4 as main database for running three(software) and possible > exchanging data.Is it possible? if yes what is the implication in terms of performance? Please give us more information: - What is three(software)? - Can you specify how and with w

Re: [GENERAL] Comment on extension issues with pg_dump after upgrading to 9.1.2

Timothy Garnett wrote: > We recently upgraded to 9.1.2 from 9.0.3 and ran into some issues with the process we've been using to > dump and restore databases. We typically use a super user (but not the postgres user) to dump and > restore databases, but in moving the 9.1.2 we've run into trouble wi

Re: [GENERAL] ALTER DEFAULT PRIVILEGES target_role doesn't work with group roles

Keith Fiske wrote: > Situation: > I have two roles, alice & bob. Both are members of the dev_user group role. > I have a schema called 'reports' that both of these users would like > to be able to manage. > > I thought I could use the ALTER DEFAULT PRIVILEGES option > (http://www.postgresql.org/do

[GENERAL] RE: [GENERAL] conexão no windows 7

vossistemas wrote: > Instalei o Postgresql 8.3 no windows 7 como servidor. Em uma estação com xp > estou tentando me conectar e ocorre a seguinte mensagem: FATAL: no > pg_hba.conf entry for host "192.168.1.51", user "Vilson", database > "postgres", SSL off . > > No servidor com windows 7 está conf

Re: [GENERAL] Drop big index

Vojtech Rylko wrote: > I need to drop some b-tree indexes because they are not used anymore. > Size of indexes vary between 700 MB and 7 GB. I tried common DROP > INDEX... but this query performed so long and blocked table so I had to > interrupt it. Is there any way how to drop large indexes in no

Re: [GENERAL] High checkpoint_segments

Jay Levitt wrote: > We need to do a few bulk updates as Rails migrations. We're a typical > read-mostly web site, so at the moment, our checkpoint settings and WAL are > all default (3 segments, 5 min, 16MB), and updating a million rows takes 10 > minutes due to all the checkpointing. > > We have

Re: [GENERAL] Database denormalization

JG wrote: > To specify further, the question is, can I count on PostgreSQL to denormalize the database when it > would be better for the performance, or should I always denormalize the database and all the querys > myself. PostgreSQL does not do such things automatically. You'll have to do so your

Re: [GENERAL] 9.1.1 crash

Mike Blackwell wrote: > The following are the relevant log entries from a recent crash of v9.1.1 > running on an older RHEL > Linux box. This is the first crash we've experienced in a lot of years of > running Pg. Any assistance > in how to determine what might have caused this is welcome. > >

Re: [GENERAL] asynchronous api questions

Nulik Nol wrote: > I have 2 questions regarding the asynchronous C api (I am using vers. 8.4): > > 1) To make a connection in non-blocking manner the api provides PGconn > *PQconnectStart(const char *conninfo) function. The parameters are > passed in 'conninfo' variable which is a string so I have

Re: [GENERAL] Large Objects and and Vacuum

Simon Windsor wrote: [pg_largeobject keeps growing] > The data only has to be kept for a few days, and generally the system is > performing well, but as stated in the email, regular use of vacuumlo, vacuum > and autovacuum leaves the OS disc space slowly shrinking. > > As a last resort this week,

Re: [GENERAL] Large Objects and and Vacuum

Please don't send HTML mail to this list. Simon Windsor wrote: > I am struggling with the volume and number of XML files a new application is storing. The table > pg_largeobjects is growing fast, and despite the efforts of vacuumlo, vacuum and auto-vacuum it keeps > on growing in size. Have you c

Re: [GENERAL] Correct syntax to create partial index on a boolean column

Mike Christensen wrote: > For the boolean column Foo in Table1, if I want to index all values of > TRUE, is this syntax correct? > > CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; Yes, that is correct. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

<    2   3   4   5   6   7   8   9   10   11   >