Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 4, 2011 at 7:14 AM, Misa Simic wrote: > > > 2011/4/28 Merlin Moncure >> >> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote: >> *) most tables don't have unique natural keys (let's see em) >> etc >> > > i.e for an Invoice, we

Re: [GENERAL] Bidirectional replication

On Tue, May 3, 2011 at 4:19 AM, Simon Riggs wrote: > On Tue, May 3, 2011 at 7:31 AM, Sim Zacks wrote: > >> I have heard good things about Bucardo, though I haven't tried it myself >> yet. I was warned that it would be risky to have 2 masters that have the >> same tables modified in both because o

Re: [GENERAL] [ADMIN] Can we Flush the Postgres Shared Memory ?

On Tue, May 3, 2011 at 8:55 AM, Cédric Villemain wrote: > 2011/5/3 Merlin Moncure : >> >> no it will not, or at least there is no guarantee it will be.  the >> only way to reset the buffers in that sense is to restart the database >> (and even then they might not be r

Re: [GENERAL] [ADMIN] Can we Flush the Postgres Shared Memory ?

On Tue, May 3, 2011 at 8:30 AM, raghu ram wrote: > On Tue, May 3, 2011 at 6:01 PM, Raghavendra > wrote: >> >> On Tue, May 3, 2011 at 5:37 PM, Simon Riggs wrote: >>> >>> On Tue, May 3, 2011 at 11:54 AM, raghu ram >>> wrote: >>> >>> > It may be a silly question, still out of curiosity I want to k

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Mon, May 2, 2011 at 11:53 PM, Craig Ringer wrote: > On 03/05/11 11:07, Greg Smith wrote: > >> That doesn't mean you can't use >> them as a sort of foreign key indexing the data; it just means you can't >> make them the sole unique identifier for a particular entity, where that >> entity is a pe

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Mon, May 2, 2011 at 10:10 AM, Greg Smith wrote: > On 05/01/2011 06:12 PM, Karsten Hilbert wrote: >> >> Good to know since I'm only a lowly medical doctor not >> having much schooling in database matters beyond this list, >> the PostgreSQL docs, and the Celko book. >> > > This debate exists at a

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Thu, Apr 28, 2011 at 4:07 PM, Karsten Hilbert wrote: > On Thu, Apr 28, 2011 at 03:39:19PM -0500, Merlin Moncure wrote: > >> They are fairly pervasive, and increasingly so, which I find to be >> really unfortunate.  Personally I think rote use of surrogate keys is >> t

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote: > A colleague of mine insists that using surrogate keys is the > common practice by an overwhelming margin in relational databases and > that they are used in 99 percent of large installations.  I agree that many > situations benefit from them, bu

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

On Wed, Apr 27, 2011 at 1:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenanc

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

On Wed, Apr 27, 2011 at 12:00 PM, Adrian Schreyer wrote: > The largest arrays I expect at the moment are more or less sparse > vectors of around 4.8k elements and I have noticed that the > input/output (C/C++ extension) does not scale well with the number of > elements in the array. > > Using a fu

Re: [GENERAL] Best way to construct PostgreSQL ArrayType (_int4) from C int array

On Wed, Apr 27, 2011 at 6:02 AM, Adrian Schreyer wrote: > At the moment I am using the following code to construct a PostgreSQL > array from a C array in my C extension but I am not so sure if this is > really the best solution: > > const int *data = array.data(); // C array > Datum *d = (Datum *)

Re: [GENERAL] 10 missing features

On Mon, Apr 25, 2011 at 3:41 AM, Linos wrote: > Hi all, >        only want to link this blog post > http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , > i think he may have any good points. my take: 1. Query progress Seen a couple of near miss proposals -- good feature,

Re: [GENERAL] Poor performance of btrfs with Postgresql

On Thu, Apr 21, 2011 at 2:22 AM, Toby Corkindale wrote: > I've done some testing of PostgreSQL on different filesystems, and with > different filesystem mount options. > > I found that xfs and ext4 both performed similarly, with ext4 just a few > percent faster; and I found that adjusting the moun

Re: [GENERAL] pg_reorg

On Tue, Apr 19, 2011 at 8:48 AM, Jens Wilke wrote: > On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote: > >> > IIRC "vacuum full" mode rewrites the indexes as well. >> >> Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table. > > Don't be confused with the "vacuum

Re: [GENERAL] Help - corruption issue?

2011/4/18 Phoenix Kiula : > Thanks Filip. > > I know which table it is. It's my largest table with over 125 million rows. > > All the others are less than 100,000 rows. Most are in fact less than 25,000. > > Now, which specific part of the table is corrupted -- if it is row > data, then can I dump

[GENERAL] pg_reorg

...is an amazing tool! merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to silence constraint violation logging for an INSERT

On Fri, Apr 15, 2011 at 10:55 AM, Paul Millar wrote: > Hi all, > > I've a question regarding unique constraints, which I've tried to describe in > general terms, to keep things simple. > > I've working on an application that, amongst other things, may add a row to a > table.  This table has a prim

Re: [BUGS] [GENERAL] PostgreSQL backend process high memory usage issue

On Wed, Apr 13, 2011 at 12:29 AM, Tom Lane wrote: > Merlin Moncure writes: >> I think you may have uncovered a leak (I stand corrected). > >> The number of schemas in your test is irrelevant -- the leak is >> happening in proportion to the number of views (set via \setran

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Tue, Apr 12, 2011 at 12:48 PM, Shianmiin wrote: > > Merlin Moncure-2 wrote: >> >> >> I am not seeing your results.  I was able to run your test on a stock >> config (cut down to 50 schemas though) on a vm with 512mb of memory. >> What is your shared buffers

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 5:07 PM, Shianmiin wrote: > > Merlin Moncure-2 wrote: >> >> On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin <shianm...@gmail.com> >> wrote: >>> Further clarification, >>> >>> if I run two concurrent threads >>> &g

Re: [GENERAL] Why is 8.4 and 9.0 so much slower on some queries?

On Tue, Apr 12, 2011 at 12:58 AM, Uwe Schroeder wrote: > > >> Uwe Schroeder writes: >> > I have a 8.3 database and decided for various reasons to upgrade to 8.4. >> > I also tried 9.0 - same results. On the exactly same hardware with the >> > exactly same configuration, some queries perform a fac

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 2:00 PM, Shianmiin wrote: > Further clarification, > > if I run two concurrent threads > > pgbench memoryusagetest -c 2 -j 2 -T180 -f test.sql > > both backend processes uses 1.5GB and result in 3GB in total. yes. could you please post a capture of top after running the mod

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 10:30 AM, Shianmiin wrote: > > Shianmiin wrote: >> >> Hi Merlin, >> >> I revised the test code with attached files and use pgbench to send the >> test queries. >> >>  http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest >>  http://postgresql.1045698.n5.nabble.

Re: [GENERAL] Using Function returning setof record in JOIN

On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote: > Hi > > Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? yes. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Fri, Apr 8, 2011 at 7:43 AM, Shianmiin wrote: > Hi Merlin, > > I revised the test code with attached files and use pgbench to send the test > queries. > > http://postgresql.1045698.n5.nabble.com/file/n4290723/dotest dotest > http://postgresql.1045698.n5.nabble.com/file/n4290723/initialize.sql >

Re: [GENERAL] PostgreSQL + FreeBSD memory configuration, and an issue

On Fri, Apr 8, 2011 at 3:00 AM, Gipsz Jakab wrote: > After the settings in the postgresql.conf our system is much faster, and no > more error messages in the postgres.log, but If I try to drop a table, or > add a new one, our system is stopping, until I kill the process, which is > dropping or add

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Thu, Apr 7, 2011 at 3:42 PM, Shianmiin wrote: > Hi there, > > We are evaluating using PostgreSQL to implement a multitenant database, > Currently we are running some tests on single-database-multiple-schema model > (basically, all tenants have the same set of database objects under then own > s

Re: [GENERAL] Arrays of arrays

On Thu, Apr 7, 2011 at 4:39 AM, rsmogura wrote: > Hello, > > May I ask if PostgreSQL supports arrays of arrays directly or indirectly, or > if such support is planned? I'm interested about pseudo constructs like: > 1. Directly - (integer[4])[5] - this is equivalent to multidimensional > array, but

Re: [GENERAL] Re: how to insert multiple rows and get the ids back in a temp table (pgplsql)?

On Wed, Apr 6, 2011 at 11:12 PM, abhishek.itbhu2004 wrote: > I am still new to postgres. Can you please tell the exact syntax for this. I > tried different things but was not able to retun the ids of the newly > inserted rows. in 9.1 with wCTE you will have a very direct way to do this. in 9.0 do

Re: [GENERAL] Functions as first-class values

On Wed, Apr 6, 2011 at 9:10 AM, Pavel Stehule wrote: > Hello > > 2011/4/6 Jon Smark : >> Hi, >> >> Is there support in PL/pgSQL for treating functions as first-class values? >> Consider the pseudo-code simple example below, which illustrates how this >> feature can be useful.  I reckon that this b

Re: [GENERAL] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..

On Tue, Apr 5, 2011 at 1:04 PM, John R Pierce wrote: > On 04/05/11 9:40 AM, Merlin Moncure wrote: >> >> On Mon, Apr 4, 2011 at 2:20 PM, John R Pierce  wrote: >>> >>> I only used a few of those adjectives, and prefixed them by hypothetical. >>> to be hone

Re: [GENERAL] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..

On Mon, Apr 4, 2011 at 2:20 PM, John R Pierce wrote: > On 04/04/11 12:07 PM, Martin Gainty wrote: >> >> ..horribly documented, inefficient, user-hostile, impossible to maintain >> interpreted language.. >> to whom might you be alluding to > > I only used a few of those adjectives, and prefixed the

Re: [GENERAL] Plpgsql function to compute "every other Friday"

On Mon, Apr 4, 2011 at 7:12 PM, C. Bensend wrote: > > Hey folks, > >   So, I'm working on a little application to help me with my > budget.  Yeah, there are apps out there to do it, but I'm having > a good time learning some more too.  :) > >   I get paid every other Friday.  I thought, for schedu

Re: [GENERAL] Merged Model for libpq

On Mon, Apr 4, 2011 at 9:31 AM, Tom Lane wrote: > Annamalai Gurusami writes: >> On 2 April 2011 11:17, John R Pierce wrote: >>> what you describe is neither postgres nor SQL >>> perhaps you should look at a storage engine like BerkeleyDB > >> I hope that not everybody dismisses this mail thread

Re: [GENERAL] Merged Model for libpq

On Sun, Apr 3, 2011 at 11:43 PM, Annamalai Gurusami wrote: > On 2 April 2011 11:17, John R Pierce wrote: > >> what you describe is neither postgres nor SQL >> >> perhaps you should look at a storage engine like BerkeleyDB > > I hope that not everybody dismisses this mail thread because of the > a

Re: [GENERAL] Merged Model for libpq

On Fri, Apr 1, 2011 at 4:47 PM, John R Pierce wrote: > On 03/31/11 9:34 AM, Annamalai Gurusami wrote: >> >> Would it be possible to implement the client server protocol into an API >> interface, without involving the TCP/IP network? > > sure, done already.  'domain sockets', the default for local

Re: [GENERAL] pg_restore

On Wed, Mar 30, 2011 at 3:56 PM, Mike Orr wrote: > I'm converting a MySQL webapp to PostgreSQL. I have a backup server > which is refreshed twice daily with mysqldump/mysql and has a > continuously-running copy of the webapp. I want to replicate this with > pg_dump/pg_restore.  Ideally I'd like to

Re: [GENERAL] Merged Model for libpq

On Thu, Mar 31, 2011 at 11:34 AM, Annamalai Gurusami wrote: > Hi All, > > I would like to know about the best approach to take for providing a merged > model of libpq library.  When I say "merged model" it means that the client > and server would be running as a single process.  A single client li

Re: [GENERAL] Stange IO error while working with large objects.

On Wed, Mar 30, 2011 at 7:37 AM, Dmitriy Igrishin wrote: > I've checked the disk with badblocs(8). The results are: > > File /pgsql/9.0/data0/base/16386/11838.5 (inode #3015588, mod time Wed Mar > 30 13:13:13 2011) >   has 50 multiply-claimed block(s), shared with 1 file(s): >     (inode #1, mod

Re: [GENERAL] anonymous record as an in parameter

On Tue, Mar 29, 2011 at 8:07 AM, Maximilian Tyrtania wrote: > Hi there, > > i'd like to write a function (sql or plpgsql) that takes an anonymous record > as an in parameter. You know, kind of like (simplified): > > create function f_tablename (p_anyrecord record) returns text as > $body$ > selec

Re: [GENERAL] Using data for column names in plpgsql

On Fri, Mar 25, 2011 at 6:18 PM, Jake Stride wrote: > Hi > > I'm attempting to do some partitioning in a database and am wondering > if I can use the data being inserted to insert into new schema. > > I have the following in the public schema: > > create table test (id serial, note varchar not nul

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.

On Mon, Mar 28, 2011 at 10:28 PM, Craig Ringer wrote: > On 03/14/2011 09:25 PM, Merlin Moncure wrote: > >>> Unless the point is to guarantee uniqueness of the "long-long value"s. >> >> md5 will do that too: the main thing you lose going to hash indexing &

Re: [GENERAL] can a function have a setof (returned from another function) as input

On Mon, Mar 28, 2011 at 1:55 PM, Terry Kop wrote: > I'm trying to create a function that will take setof results from various > other functions (they all produce the same output format). Is this possible? > if so how do call it. > > ex. > CREATE TYPE emp_t AS ( > ID int, > name   varch

Re: [GENERAL] Deadlock in libpq

On Fri, Mar 25, 2011 at 3:26 AM, Erik Hesselink wrote: >> hm, ISTM (I don't know haskell) that the hdbc driver isn't doing any >> type of synchronization at all unless it is using a non thread safe >> libpq...and in that case it uses a global mutex.  That doesn't look >> correct -- the hdbc driver

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:57 AM, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure wrote: >>> As far as connections getting dropped: yes, this sounds reasonable, >>> but given that both the client and the server are running on the same >>>

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure wrote: >> As far as connections getting dropped: yes, this sounds reasonable, >> but given that both the client and the server are running on the same >> machine, will connections (to 127.0.0.1) really be dropped once every >&g

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:27 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 17:18, Merlin Moncure wrote: >> On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink wrote: >>> On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: >>>> He needs to rule out

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: >> He needs to rule out the most obvious problem first -- PQInitSSL being >> called improperly or at the wrong time.  OP: It's a library wide >> setting and

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 10:00 AM, Tom Lane wrote: > Merlin Moncure writes: >> *something* must be initializing ssl, or you can't make secure >> connections from libpq.  you need to find out which pq ssl init >> function is begin called, when it is being called, and with

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 9:07 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 14:23, Merlin Moncure wrote: >> On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: >>> Hi, >>> >>> We're getting a deadlock in our application (a web application with a

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: > Hi, > > We're getting a deadlock in our application (a web application with a > PostgreSQL backend) which I've traced to libpq. I've started our > application in gdb, and when it hangs, I've inspected the backtraces. > I've found a couple of

Re: [GENERAL] Recursive function that receives a list of IDs and returns all child IDs

On Wed, Mar 23, 2011 at 10:29 AM, Sven Haag wrote: > hello pgsql fans out there, > > i've already created a function that returns a list of IDs of all sub-samples > based on a given sample ID. this works fine. now i like to extend this > function so that it can receive a list of sample IDs. e.g.

Re: [GENERAL] RAID 1 - drive failed - very slow queries even after drive replaced

On Wed, Mar 23, 2011 at 3:33 AM, Merrick wrote: > Hi, > > I am looking for some advice on where to troubleshoot after 1 drive in > a RAID 1 failed. > > Thank you. > > I am running v 7.41, I am currently importing the data to another > physical server running 8.4 and will test with that once I can.

Re: [GENERAL] Weird problems with C extension and bytea as input type

On Wed, Mar 23, 2011 at 9:04 AM, dennis jenkins wrote: > On Wed, Mar 23, 2011 at 5:08 AM, Adrian Schreyer wrote: >> >> you are right, it returns a char *. >> >> The prototype: >> >> char *function(bytea *b); >> >> The actual C++ function looks roughly like this >> >> extern "C" >> char *function(

Re: [GENERAL] PostgreSQL documentation specifies 2-element array for float8_accum but 3-element array expected

On Wed, Mar 23, 2011 at 8:03 AM, Disc Magnet wrote: > I was learning how to create my own aggregate functions from > http://www.postgresql.org/docs/9.0/static/xaggr.html > > I copied the avg() example as > > CREATE TABLE numbers ( >    value integer > ); > > insert into numbers values (2); > inser

Re: [GENERAL] Weird problems with C extension and bytea as input type

On Tue, Mar 22, 2011 at 11:57 AM, Adrian Schreyer wrote: > On Tue, Mar 22, 2011 at 16:07, Merlin Moncure wrote: >> On Tue, Mar 22, 2011 at 8:22 AM, Adrian Schreyer wrote: >>> Hi, >>> >>> I have a weird problem with my custom functions (written in C,C++) >&

Re: [GENERAL] Weird problems with C extension and bytea as input type

On Tue, Mar 22, 2011 at 8:22 AM, Adrian Schreyer wrote: > Hi, > > I have a weird problem with my custom functions (written in C,C++) > that use bytea as input type (cstring works fine). The functions will > work as expected if they are the only function that uses the bytea > column in a query; as

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

On Mon, Mar 21, 2011 at 11:32 AM, wrote: >> Incredible!  Setting enable_nestloop off temporarily for the run of this >> script made it run in less than a minute (had been running in 10 or 11 >> minutes).  I think you have found a solution for many of my slow running >> scripts that use these same

Re: [GENERAL] multi-tenant vs. multi-cluster

On Fri, Mar 18, 2011 at 2:44 PM, Ben Chobot wrote: > > On Mar 18, 2011, at 12:34 PM, Nicholson, Brad (Toronto, ON, CA) wrote: > b) its own postgresql processes (many of them) running in memory >>> >>> I believe this is entirely a function of client connections. >> >> With a single instance, y

Re: [GENERAL] How do I do this in plpgsql ?

On Fri, Mar 18, 2011 at 2:20 PM, Dan S wrote: > Hi ! > > Is there a way to use plpgsql copy type to get an array of a certain type ? > > For example if I have a type sample%TYPE > How can I declare a variable that is an array of sample%TYPE > I can't get it to work, is there a way to do it ? I do

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

On Fri, Mar 18, 2011 at 10:42 AM, Davenport, Julie wrote: > This helped, is now down from 14.9 min to 10.9 min to run the entire script.   > Thanks. can you try disabling nestloop and see what happens? In the session, before running the query, isssue: set enable_nestloop = false; merlin -- S

Re: [GENERAL] SOCK_wait_for_ready function call caused a query to get stuck

On Fri, Mar 18, 2011 at 11:39 AM, tamanna madaan wrote: > Thanks for your reply Merlin . > > If I am getting you correctly, you mean to say that I should check for > waiting queries in pg_stat_activity table > while my application is hung at SOCK_wait_for_ready function call  . Right > ?? correct

Re: [GENERAL] SOCK_wait_for_ready function call caused a query to get stuck

On Fri, Mar 18, 2011 at 4:31 AM, tamanna madaan wrote: > Hi All > >  I am using postgres-8.4.0 and psqlodbc-08.03.0400-1 and > unixODBC-2.2.14-000.01 driver to connect > to the database. One of the queries I executed from my application have got > stuck for an > indefinite amount of time causing m

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

ocale for time formatting > > Am I looking in the wrong place?  Thanks much, > Julie > > > Julie A. Davenport > julie.davenp...@ctcd.edu > > > > > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: Wednesday, March 16, 201

Re: [GENERAL] query taking much longer since Postgres 8.4 upgrade

On Wed, Mar 16, 2011 at 10:49 AM, Davenport, Julie wrote: > When I run the following query in Postgres 8.0, it runs in 61,509.372 ms > > > > When I run it in Postgres 8.4, it runs in 397,857.472 ms > > > > Here is the query: > > > > select > > course_id AS EXTERNAL_COURSE_KEY, > > user_id AS EXTER

Re: [GENERAL] Saving bytes in custom data type

On Wed, Mar 16, 2011 at 12:19 PM, Daniele Varrazzo wrote: > Hello, > > I'm writing a variable size custom datatype in C. The variable part is > an array of unsigned long, and it needs to be aligned. I further need > to store a few flags, for which a single byte would be more than > enough (I would

Re: [GENERAL] equivalent of mysql's SET type?

On Thu, Mar 10, 2011 at 3:54 PM, Merlin Moncure wrote: > On Thu, Mar 10, 2011 at 3:21 PM, Reece Hart wrote: >> On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure wrote: >>> >>> create type validation_flags as >>> ( >>>  cluster bool, >>>  fr

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

> On 15.03.2011 17:24, Merlin Moncure wrote: >> >> >> well, regardless of the version, you're doing a gazillion sequential >> scans on relation tags. This looks like the primary culprit (I had to >> look up the ~~* operator...it's 'ilike'): &

Re: [GENERAL] PostgreSQL for Holdem Manager could not be installed.

On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian wrote: > > The larger question is whether these forums should be used to help > people get support for commercial products that embed Postgres. I have no particular philosophical objection to helping people using Holdem Manager -- I'd venture most p

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

On Mon, Mar 14, 2011 at 9:48 AM, Jo wrote: > I set the work_mem to 100MB and the shared buffers are 2 GB > > The query plans are long and complex. I send the beginning of the > two plans. Hope this helps to understand the differences. > I assume the join strategy in 8.3 differs from the one in 8.4

Re: [GENERAL] PostgreSQL for Holdem Manager could not be installed.

On Tue, Mar 15, 2011 at 10:55 AM, Alphadion wrote: > I tried to install any of the versions but it always gives that message > "Could not connect to the database server, please check port 5432 and > 127.0.0.1" or  something like that. I've opened a range of ports on my > router's page, and also op

Re: [GENERAL] Create a view with variable amount of columns depending on the rows of a table

2011/3/14 Stefan Gündhör : > Hi, > If I have following tables for example: > # Main Table: > id(id/pk) | geometry > --- > 1           | ... > # Additional Attribute Table: > name(id/pk) | value > --- > date_added | 20.12.1988 > name          | Vienna

Re: [GENERAL] Select for update with offset interferes with concurrent transactions

On Tue, Feb 1, 2011 at 11:18 AM, Tom Lane wrote: > "Yngve Nysaeter Pettersen" writes: >> To avoid having the processes trample each other's queries (the first >> attempt was to select the first matching entries of the table, which >> caused one to block all other transactions), one of the steps I

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.

On Sun, Mar 13, 2011 at 4:37 PM, Rob Sargent wrote: > > > Brian Hirt wrote: >> >> On Mar 13, 2011, at 12:05 PM, Dmitriy Igrishin wrote: >> >>> Hey Viktor, >>> >>> 2011/3/13 Viktor Nagy >> > >>> >>>    hi, >>> >>>    when trying to insert a long-long value, I get the

Re: [GENERAL] How do you change the size of the WAL files?

On Fri, Mar 11, 2011 at 11:58 AM, runner wrote: > We are doing continuous archiving and we have thousands of these 16 Mb > archive files on disk. > > My boss is used to using Oracle where you can set the size of the files. > > He'd rather have fewer but larger archive files. What advantage is the

Re: [GENERAL] equivalent of mysql's SET type?

On Thu, Mar 10, 2011 at 4:13 PM, Dmitriy Igrishin wrote: > 2011/3/9 John R Pierce >> >> On 03/08/11 5:06 PM, Reece Hart wrote: >>> >>> I'm considering porting a MySQL database to PostgreSQL. That database >>> uses MySQL's SET type. Does anyone have advice about representing this type >>> in Postg

Re: [GENERAL] equivalent of mysql's SET type?

On Thu, Mar 10, 2011 at 3:21 PM, Reece Hart wrote: > On Wed, Mar 9, 2011 at 9:16 AM, Merlin Moncure wrote: >> >> create type validation_flags as >> ( >>  cluster bool, >>  freq bool >> ); > > Wow. That solution is nearly sexy, and far and away better

Re: [GENERAL] Using bytea field...

On Thu, Mar 10, 2011 at 2:09 AM, Sim Zacks wrote: > >> > The question is, if it screws up and says that an image already exists >> > and then returns a different image when querying for it, how bad would >> > that be. >> > >> >> >> It'll never happen: >> >> >> http://stackoverflow.com/questions/86

Re: [GENERAL] 9.1 Trigger question

On Wed, Mar 9, 2011 at 8:24 PM, Michael Black wrote: > The following from 9.1 documentation on triggers > > "SQL allows you to define aliases for the "old" and "new" rows or tables for > use in the definition of the triggered action (e.g., CREATE TRIGGER ... ON > tablename REFERENCING OLD ROW

Re: [GENERAL] Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

On Wed, Mar 9, 2011 at 4:09 PM, Vlad Romascanu wrote: > Hello, > > I need to perform "conversions" (transcoding) between BYTEA and TEXT > columns in a UTF-8 database.  I searched for existing solutions and > was unable to find one for 8.x or 9.x, so I cam up with something I'd > like to validate w

Re: [GENERAL] equivalent of mysql's SET type?

On Wed, Mar 9, 2011 at 10:59 AM, Reece Hart wrote: > On Tue, Mar 8, 2011 at 9:41 PM, John R Pierce wrote: >> >> why not just have a set of booleans in the table for these individual >> on/off attributes?   wouldn't that be simplest? > > I like that approach, but I think it's unlikely to fly in th

Re: [GENERAL] equivalent of mysql's SET type?

On Tue, Mar 8, 2011 at 11:41 PM, John R Pierce wrote: > On 03/08/11 5:06 PM, Reece Hart wrote: >> >> I'm considering porting a MySQL database to PostgreSQL. That database uses >> MySQL's SET type. Does anyone have advice about representing this type in >> PostgreSQL? >> >> MySQL DDL excerpt: >> CR

Re: [GENERAL] Why count(*) doest use index?

On Mon, Mar 7, 2011 at 4:26 PM, Glenn Maynard wrote: > On Mon, Mar 7, 2011 at 4:35 PM, Merlin Moncure wrote: >> >> SELECT COUNT(*) FROM table WHERE expr; >> >> will use index (assuming expr is optimizable and is worth while to >> optimize).  Your case might be

Re: [GENERAL] PG and dynamic statements in stored procedures/triggers?

On Mon, Mar 7, 2011 at 3:16 PM, Bill Thoen wrote: > On 3/7/2011 7:55 AM, Adrian Klaver wrote: >> >> On Monday, March 07, 2011 6:45:11 am Durumdara wrote: >>> >>> Hi! >>> >>> Thanks! >>> >>> How do I create "cursor" or "for select" in PGSQL with dynamic way? >>> >>> For example >>> >>> :tbl = GenTe

Re: [GENERAL] Why count(*) doest use index?

On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard wrote: > On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure wrote: >> >> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote: >> > That's often perfectly fine, with read-heavy, single-writer workloads. >> > >

Re: [GENERAL] Why count(*) doest use index?

On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote: > On Sun, Mar 6, 2011 at 5:41 AM, Martijn van Oosterhout > wrote: >> >> If it's really really important there are ways you can use trigger >> tables and summary views to achieve the results you want. Except it's >> expensive and when people are

Re: [GENERAL] pg_dump slow with bytea data

On Mon, Mar 7, 2011 at 8:52 AM, Merlin Moncure wrote: > Well, that's a pretty telling case, although I'd venture to say not > typical.  In average databases, I'd expect 10-50% range of improvement > going from text->binary which is often not enough to justify the >

Re: [GENERAL] pg_dump slow with bytea data

On Mon, Mar 7, 2011 at 7:28 AM, chris r. wrote: > Merlin, first of all, thanks for your reply! > >> hm.  where exactly is all this time getting spent?  Are you i/o bound? >> cpu bound? Is there any compression going on? > Very good questions. pg_dump -F c compresses per default "at a moderate > le

Re: [GENERAL] Tracking table modifications / table stats

On Thu, Mar 3, 2011 at 11:00 AM, Derrick Rice wrote: > Hey folks, > > I was looking through the contrib modules with 8.4 and hoping to find > something that satisfies my itch. > http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the > closest. > > I'm inheriting a database which

Re: [GENERAL] how to avoid repeating expensive computation in select

On Tue, Mar 1, 2011 at 2:51 AM, Orhan Kavrakoglu wrote: >> I would like to know if there is a way in PostgreSQL to avoid repeating an >> expensive computation in a SELECT where the result is needed both as a >> returned value and as an expression in the WHERE clause. > > I think I've seen it said

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 9:06 AM, Pierre Racine wrote: > Is EXECUTE slower than a direct assignment call? It is going to be slower, but how much slower and if it justifies the mechanism is going to be a matter of your requirements, definition of 'slow', and willingness to experiment. merlin -- S

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 8:54 AM, Dmitriy Igrishin wrote: > 2011/3/2 Merlin Moncure >> Lane...http://postgresql.1045698.n5.nabble.com/improvise-callbacks-in-plpgsql-td2052928.html >> (for whom nary a day goes by that I am not thankful for his tireless >> efforts). > > A

Re: [GENERAL] Dynamic binding in plpgsql function

On Wed, Mar 2, 2011 at 8:34 AM, Dmitriy Igrishin wrote: > 2011/3/2 Merlin Moncure >> On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule >> wrote: >> > Hello >> > >> > 2011/3/2 Pierre Racine : >> >> Hi, >> >> >> >> I woul

Re: [GENERAL] pg_dump slow with bytea data

On Wed, Mar 2, 2011 at 2:35 AM, chris r. wrote: > Dear list, > > As discussed extensively in the past [1], pg_dump tends to be slow for > tables that contain bytea columns with large contents. Starting with > postgres version 8.5 the COPY format of bytea was changed from escape to > hex [1], givin

Re: [GENERAL] Dynamic binding in plpgsql function

On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule wrote: > Hello > > 2011/3/2 Pierre Racine : >> Hi, >> >> I would like to write a generic plpgsql function with a text parameter being >> a callback function name so that my general function can call this callback >> function. e.g.: >> >> CREATE OR RE

Re: [GENERAL] Binary params in libpq

On Tue, Mar 1, 2011 at 8:19 AM, Kelly Burkhart wrote: > On Mon, Feb 28, 2011 at 8:42 AM, Merlin Moncure wrote: >> On Sun, Feb 27, 2011 at 7:39 PM, Craig Ringer >>> AFAIK, the `timestamp' type moved from a floating-point to an integer >>> representation interna

Re: [GENERAL] regexp problem

On Thu, Feb 24, 2011 at 2:12 PM, Gauthier, Dave wrote: > Yup, that did it.  And you're right, you don't need to escape the '.'. > > So the extra \ is needed because of the single quotes string. > A.  :-) Yes...highly advise dollar quoting whenever dealing with regex. merlin -- Sent

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sun, Feb 27, 2011 at 1:57 PM, Sean Laurent wrote: > Right. I read all of that. I guess I just assumed it was possible to create > a snapshot on the standby so that a longer running on the standby could > complete. In particular, I was really hoping to run database dumps against > the standby, n

Re: [GENERAL] Binary params in libpq

On Sun, Feb 27, 2011 at 7:39 PM, Craig Ringer wrote: > On 28/02/2011 7:48 AM, Merlin Moncure wrote: > >>> How stable is the binary representation for the PostgreSQL types? We >>> may just pass bytea data in binary format and pass everything else as >>> text parame

Re: [GENERAL] Binary params in libpq

On Sun, Feb 27, 2011 at 1:13 PM, Daniele Varrazzo wrote: > Hello, > > I'm thinking about adding support for PQexecParams and PQprepare in > Psycopg. I've posted more details yesterday on the Psycopg mailing > list . I > have a few prelim

<    6   7   8   9   10   11   12   13   14   15   >