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

Marc Balmer writes: > My SQL is PostgreSQL PostgreSQL really is YeSQL! -- you can check http://yesql.org too -- Dimitri Fontaine06 63 07 10 78 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mail

Re: [GENERAL] pg_dump -s dumps data?!

a is true/false) constraint to filter the data. So I sure would appreciate being able to call that data rather than config, and to mark any table at once. If that doesn't need any pg_dump stretching I think providing that in 9.2 would be great. Regards, -- Dimitri Fontaine

Re: [GENERAL] Using Postgresql as application server

been existing in PostgreSQL land for a long time already, no need to resort to other proprietary architectures here. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] Dump a database excluding one table DATA?

s as for -n. -N can be given more than once to exclude schemas matching any of several patterns. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] Using Postgresql as application server

not thinking of implementing as it is It's been around for a long time already: http://asmith.id.au/mod_libpq.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] Using Postgresql as application server

e queue and send emails. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] pgloader hangs with an invalid filename

o the development now happens at github. http://packages.debian.org/search?keywords=pgloader http://github.com/dimitri/pgloader Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] Adapting existing extensions to use CREATE EXTENSION

gfincore recently, and will probably do some more of it later. You can look at those links for the time being: http://tapoueh.org/blog/2011/06/29-multi-version-support-for-extensions.html https://github.com/klando/pgfincore/tree/master/debian http://packages.debian.org/sid/postgresql-9.0-p

Re: [GENERAL] pgloader hangs with an invalid filename

ly due to current threading implementation in pgloader, I guess the control thread is still waiting for the load to terminate, which will never happen. It seems to be fixed in current version though. See if you can upgrade. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Exp

Re: [GENERAL] [HACKERS] PostgreSQL Core Team

Thom Brown writes: > Excellent! Magnus is a very valuable contributor to the PostgreSQL > community and I think the community can only benefit from this addition to > the core team. +1 Congrats, Magnus! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation e

Re: [GENERAL] Partitioning an existing table

the "name". Later on you still can rejigger your data around if you wish. With time based partitioning it's best to wait until the old partition is not the target of INSERTs or UPDATEs any more. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation e

Re: [GENERAL] converting databases form SQL_ASCII to UTF8

erting, you might be interested into those two blog entries: http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_1.html http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation

Re: [GENERAL] Multiple instances with same version?

ges: http://manpages.debian.net/cgi-bin/man.cgi?query=pg_lsclusters http://manpages.debian.net/cgi-bin/man.cgi?query=pg_createcluster Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To mak

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

is tied to real active connection, I don't see what it would solve. Still, if you're searching ideas… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] How to Create Table from CSV

BLE http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html http://developer.postgresql.org/pgdocs/postgres/sql-createforeigndatawrapper.html http://developer.postgresql.org/pgdocs/postgres/file-fdw.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation

Re: [GENERAL] FTS and words near one another

ntation at pgcon here: http://www.pgcon.org/2009/schedule/events/119.en.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] Covert database from ASCII to UTF-8

ose blog articles of mine might be of interest to you: http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_1.html http://tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Supp

Re: [GENERAL] Finding Errors in .csv Input Data

ns. Did you try pgloader yet? http://pgloader.projects.postgresql.org/ http://pgfoundry.org/projects/pgloader/ https://github.com/dimitri/pgloader http://packages.debian.org/sid/pgloader Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Suppor

Re: [GENERAL] How to extract a value from a record using attnum or attname?

n array_lower(keycols, 1)..array_upper(keycols, 1) loop > select quote_ident(attname) from pg_catalog.pg_attribute > where attrelid = tg_relid and attnum = keycols[i]::oid Beware of attisdropped, which I've not fixed in the published URL before (the tapoueh.org one). Regards,

Re: [GENERAL] How best to load modules?

ou can even clone the git repository where the development occurs (branches named "extension" and "upgrade") and try it for yourself, then maybe send a mail about your findings (we call that a review): http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary http:

Re: [GENERAL] Updated Latch Patch

Shoma S Achar writes: > I would like to know where I could find the latest available Latch patch. If > anyone knows, please share this information. I guess you would find it there: http://git.postgresql.org/gitweb?p=postgresql.git&a=search&h=HEAD&st=commit&s=latch

Re: [GENERAL] Have any tricks not to recreate a standby server to switch to the former primary?

com/greg2ndQuadrant/repmgr http://groups.google.com/group/repmgr Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Full Text Partial Match at begining

AI Rumman writes: > Is it possible to match "%text' in Postgresql 9 Full Text. See http://www.sai.msu.su/~megera/wiki/wildspeed -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] Adding data from mysql to postgresql periodically

odule reformat feature, and the mysql to PostgreSQL timestamp that you have to use sometime (depends on the MySQL minor version, if memory serves). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Can you check in SQL if a fields can be encoded using specified charset

n't be converted, but you could have a plpgsql function catching the exception for you and returning false. Won't be very fast, but will do the job. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing lis

Re: [GENERAL] finding the other statement causing a sharelock

docs/8.3/static/explicit-locking.html#ADVISORY-LOCKS http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgre

Re: [GENERAL] Need magic to clean strings from unconvertible UTF8

process the source files before importing them? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Cloning database without dump/restore

I'm wouldn't expect it to be "fast" though. -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Group by and limit

Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] index in desc order

that how to model that is still unclear and undone in the cost estimations. You will have to crawl the pgsql-performance list yourself, though… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] Replication

and the fact that running queries are not canceled at the time you flick the switch to have your standby a master. The ongoing read-only traffic is not affected. That's hot. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via p

Re: [GENERAL] how to get the height of index tree?

sts for btree, but I guess it would be very useful. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] pg_get_procdef ?

dl/sql/function.body.sql The Omiti version will output a single file with the objects in there, the other version will split the objects each in its own file in directories, to be svn / git friendly. With the python version: ./getddl.py -f -F fun_dir -d db -h host -p port -U user Regards, -- Dimitri Fo

Re: [GENERAL] Visualize GiST Index

Oleg Bartunov writes: > We never expected gevel will be used by users :-) It's very very useful when developing custom GiST indexes! -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gene

Re: [GENERAL] Good candidate query for window syntax?

Ketema Harris writes: > My goal is: To find the maximum number of concurrent rows over an > arbitrary interval. My guess is that the following would help you: http://wiki.postgresql.org/wiki/Range_aggregation -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] table - sequence dependent informatio

Peter Geoghegan writes: > On 31 August 2010 18:02, akp geek wrote: >> Hi all - >>         Is there a way I can tell table - sequence dependent information. >>  which sequences are being used by which table? >> thanks for the help > > Take a look at the post "Finding orphaned sequences" on this b

Re: [GENERAL] PostgreSQL and distributed transactions

Jayadevan M writes: > But the initial setup for the client is done by 'Admin' and in that > work-flow, we need distributed transactions. The transaction will start > from the 'Admin" server, do some inserts on the 'Client' server and then > either rollback or commit on both the servers. Is it p

Re: [GENERAL] Integral PG DB replication

"Gauthier, Dave" writes: > Will DB replication be integral in v9? If so, when (approx) will that > be out? Integral meaning all the cluster at a time, yes. You can help have it out sooner by testing it and reporting your findings. I think the goals are to have 9.0.0 out by mid august or about, s

Re: [GENERAL] Converting BYTEA from/to BIGINT

Vincenzo Romano writes: > Now, why doing this? > I am using a plain SEQUENCE to create a (kind of) "session ID". That > is simple but predictable. > The idea is to use this function in conjunction with encrypt (from > pgcrypto) and the blowfish algorithm > to make that sequence numbers somehow unp

Re: [GENERAL] copy/duplicate database schemas

Merlin Moncure writes: > On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee wrote: >> I have an application in a schema and now i need to create other schemas b/c >> the app needs to support different languages,  is there an easy way to copy >> an entire schema to a new one (tables, contents, trigges,

Re: [GENERAL] No PL/PHP ? Any reason?

"Joshua D. Drake" writes: >> Any chance that the Parrot runtime could be used for PHP and other >> languages? I read that some folks are working on PL/Parrot. I'd really like >> to have PHP and Lisp for PL languages :). > > http://plscheme.projects.postgresql.org/ > > Not exactly lisp, but

Re: [GENERAL] libpython - cannot open shared object file

Tom Wilcox writes: > Next problem: "$libdir/fuzzystrmatch": No such file or directory. I guess I > need to install the /share stuff separately.. Looks like apt-get install postgresql-contrib-8.4 should do the trick. http://packages.debian.org/lenny-backports/postgresql-contrib-8.4 Regards, --

Re: [GENERAL] High Availability with Postgres

John R Pierce writes: > yeah. generally when money is involved in the transactions, you gotta stick > to the 'no committed data lost ever'. there's plenty of other use cases for > that too. Well, it's a cost/benefit/risk evaluation you have to make. It'd be bad news that the cost for covering y

Re: [GENERAL] Hot Standby switchover

Hi, Shoaib Mir writes: > Now Hot-Standby is setup like this... > > A --> B  > A --> C > > Now if I will like to do a switch so as to make B the new master and have my > replication look like this: > > B --> C  > B --> A > > Where are the steps I need for doing so? First, you need a common archi

Re: [GENERAL] High Availability with Postgres

John R Pierce writes: > failure modes can > include things like failing fans (which will be detected, resulting in a > server shutdown if too many fail), power supply failure (redundant PSUs, but > I've seen the power combining circuitry fail). Any of these sorts of > failures will result in a f

Re: [GENERAL] What type of index should I use?

Mike Christensen writes: > I have a varying(200) text column that I need to be able to do lookups > on very fast (WHERE col = 'foo') Btree is what to use here. GIN covers cases where you index arrays. > I estimate the table will hold around 5,000 rows, never any more. It could be that you're

Re: [GENERAL] High Availability with Postgres

John R Pierce writes: >>> Two DB servers will be using a common external storage (with raid). > > This is also one of the only postgres HA configurations that won't lose > /any/ committed transactions on a failure. Most all PITR/WAL > replication/Slony/etc configs, the standby storage runs severa

Re: [GENERAL] hi,i write a function in postgresql source code, how to register this function?

sunpeng writes: > hi,i write a function in postgresql source code, how to register this > function? See src/include/catalog/pg_proc.h But you should *really* consider making it a loadable module. That's the way it makes sense for any code you want to add in the server unless you're preparing a

Re: [GENERAL] Monitoring activities of PostgreSQL

Allan Kamau writes: > How can I monitor the actual DB activities during such times so I may > better understand what the situation truly is. I have seen some users > on this list posting some complex log/outputs, this are the kind of > outputs I would like to capture and view. Where are they? The

Re: [GENERAL] Is there a way to backup Postgres via SQL commands?

Frank Church writes: > Are there SQL commands that can do a backup over a client connection, > rather than from the command line like pgsql etc? That's pg_dump ? > By that I mean some kind of SELECT commands that can retrieve the > database's content as SQL commands that can be replayed to a ser

Re: [GENERAL] Cognitive dissonance

Dave Coventry writes: > Formatted text, whether PDF, HTML or (heaven forbid!) Word Documents, > is easier to read than unformatted plain text, and those of us without > the OP's very admirable proficiency in vi remain at the mercy of the > various readers and their associated search functions. > >

Re: [GENERAL] Random Weighted Result Ordering

Eliot Gable writes: > I have a set of results that I am selecting from a set of tables which I want > to return in a random weighted order for each priority group returned. Each > row has a > priority column and a weight column. I sort by the priority column with 1 > being highest priority. Th

Re: [GENERAL] pgbouncer

Mario Ignacio Rodríguez Cortés writes: > Well, i have a question if you know about this, i have a webserver in a > server and i have a database server, the question is: where should I > install the pgbouncer? in webserver, in database server or is the same? > whats your experience. I always insta

Re: [GENERAL] libreadline and Debian 5 - not missing just badly named

"J. Bagg" writes: > I've just had the common problem with not finding the readline library while > compiling/linking 8.4.4 on a new linux (Debian 5 - lenny). Tried: apt-get build-dep postgresql-8.4 That command will install all what you need to compile your own PostgreSQL. Some will add "and

Re: [GENERAL] INSERTing lots of data

on.org/library/multiprocessing.html My plan was to go with http://docs.python.org/library/subprocess.html but it seems multiprocessing is easier to use when you want to port existing threaded code. Thanks Greg! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support --

Re: [GENERAL] Documentation availability as a single page of text

Alvaro Herrera writes: > There's a texinfo output that could perhaps be useful. Try > "make postgres.info" in the doc/src/sgml directory; while it's tagged > "experimental" and outputs a boatload of warnings, it does work for me and the > text it produces is plain enough. It's pretty good indeed

Re: [GENERAL] PostgreSQL vs. Microsoft SQL server

Merlin Moncure writes: > On Sat, May 1, 2010 at 2:47 PM, Thomas Løcke > wrote: >> Anybody know of any recent comparisons made between the two? >> >> I'm in the process of buying a new telephony related software suite, > > if you are writing stuff in C/C++, doing significant coding INSIDE the >

Re: [GENERAL] Fuzzy string matching of product names

Leif Biberg Kristensen writes: > On Monday 5. April 2010 22.00.41 Peter Geoghegan wrote: >> similar they sound. How can that actually be applied to get the >> functionality that I've described? > > I've got a similar problem in my 18th century research, when clerks usually > took pride in being a

Re: [GENERAL] optimizing import of large CSV file into partitioned table?

Rick Casey writes: > So, I am wondering if there is any to optimize this process? I have been > using Postgres for several years, but have never had to partition or optimize > it for files > of this size until now.  > Any comments or suggestions would be most welcomed from this excellent forum.

Re: [GENERAL] pgreplay log file replayer released

"Albe Laurenz" writes: > It seems that Tsung currently only supports "basic queries", but I > assume that this can be improved. In fact from the time when PostgreSQL support was added, some more Erlang drivers have appeared and some of them covers the entire protocol. So it should be possible to

Re: [GENERAL] pgreplay log file replayer released

Greg Stark writes: > Do you have a multi-threaded model that tracks which transactions each > query belonged to and runs them concurrently like they were in the > original setup? That's what I've been looking for. Tsung does that and has been doing it for… quite some time. It even comes with a re

Re: [GENERAL] stopping processes, preventing connections

Scott Marlowe writes: > It was a few posts back, but our discussion point was minor point > upgrades and the fact that OP was running 8.3.1 and not sure there > were updates to 8.3.9 (or latest) out there for debian. I'm quite > sure debian has 8.3.9 out by now. Yes: http://packages.debian.o

Re: [GENERAL] Postgres 8.4 Segfault on xpath

Frank jansen writes: > I tried postgres 8.4.0 and 8.4.2 self compiled with elf compiled contrib > directory, tried Debians 8.4 backport package with the 8.4 contrib package > and all do the same: Segfault and terminated by Signal 11 :-( Try 8.4.3, which has some XML crash related fix: http://

Re: [GENERAL] make available C extensions to others

Ivan Sergio Borgonovo writes: > I've finished to write an extension to manipulate tsvectors and > tsquery in C. > > I think it could be useful for someone else and I think I may take > advantage at someone else looking at the code too. > > What would be the right place where to publish the code an

Re: [GENERAL] accessing the words in a full text index

"Massa, Harald Armin" writes: > I want to provide a "suggest word as you type" feature in an application > (like google suggest).  > All the documents are - of course - stored within a PostgreSQL > database, within TEXT columns. See pg_trgm. http://www.postgresql.org/docs/8.4/static/pgtrgm.h

Re: [GENERAL] What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?

"Wang, Mary Y" writes: > Because the current value is 6818, during the restore process, it > complained about "duplicate key value violates unique constraint > "bug_pkey, because the value of bug_pk_seq for a insert has been > already been used. So what is the best way to resolve this? Should I

Re: [GENERAL] One solution for Packages in Postgre

venkatra...@tcs.com writes: > I am new to Postgre. We are migrating an oracle db to postgre. In > oracle we have used so many packages. As per my understanding, there > is no oracle package like functionality in postgre. I was just trying > to find some way to migrate ocale packages to postgre. T

Re: [GENERAL] COPY FROM wish list

Scott Bailey writes: > PgFoundry has http://pgfoundry.org/projects/pgloader/ > It is a step in the right direction but definitely not as powerful as > sql*loader. Yeah, it's only offering what I needed and what I've been requested to add. So far there's support for INFORMIX UNLOAD files, mysqldum

Re: [GENERAL] Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

Tom Lane writes: > Peter Geoghegan writes: >> Aren't my requirements sufficiently common to justify developing a >> mechanism to report progress back to client applications during batch >> operations and the like? > > Have you experimented with RAISE NOTICE? Using it this way is a bit of > a hac

Re: [GENERAL] using new Hstore in PG8.4

Marek Lewczuk writes: > there are a lot of improvements in hstore, that is planned for PG8.5 - > as far I remember it compiles fine with PG8.4 so is it save to use it with > PG8.4 ? See hstore-new, which delivers the same code to be found in 9.0 as a module against 8.3 and 8.4. http://pgfound

Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...

Alvaro Herrera writes: > For example, perhaps there could be a new pair of functions > pg_read_hba_file/pg_write_hba_file that would work even if the files are > placed in other directories, but they (Debian) would need to propose > it. I don't remember they had to provide those GUCs: http://w

Re: [GENERAL] more docs on extending postgres in C

Ivan Sergio Borgonovo writes: > - installing postgresql-server-dev-[version] > - apt-get source postgresql-server-dev-[version] > - copy from there a contrib dir in my ~ (or wherever you prefer) > - export USE_PGXS=1; make Don't forget apt-get build-dep postgresql-[version] > I didn't have postg

Re: [GENERAL] Is there a messaging system for Postresql

"Romeliz Valenciano Jr." writes: > We're evaluating Postgresql for a possible transition from MS > SqlServer. We have 100s of MS Sql servers and one way of replicating > data changes is to use MQ Series Queue management system, some servers > who were recently changed to Sql 2005 are using Service

Re: [GENERAL] C: extending text search: from where to start

Ivan Sergio Borgonovo writes: > Any other resource that will help me to write my own contrib? You could try out the following, but it deals a lot with GiST specifics… http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-g

Re: [GENERAL] more docs on extending postgres in C

Ivan Sergio Borgonovo writes: > I haven't been able to find anything better than the online manual > and pg source code to learn how to write extensions. Maybe this will help: http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf http://github.com/dimitri/prefix Regards, -- dim -- S

Re: [GENERAL] C: extending text search: from where to start

Ivan Sergio Borgonovo writes: > I'd appreciate any pointer that will quickly put me on the right > track. I'd guess you begin here: http://wiki.postgresql.org/wiki/Developer_FAQ -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

Re: [GENERAL] Avoid transaction abot if/when constraint violated

"Gauthier, Dave" writes: > I have a long list of records I want to insert into a table in such a way as > I can trap and report any/all constraint violations before rolling back (or > opting to commit). > Unfortunately, after I hit the first constraint violation, it aborts the > transaction, a

Re: [GENERAL] replication from multiple "master" servers to a single read-only slave

Omar Mehmood writes: > I really don't want to use separate schemas for each master to > logically partition the data. I ensure that the data on each master > will not clash with each other (in terms of any DB level contraints > such as PK), so I'd much prefer they all reside in a single schema. >

Re: [GENERAL] Pre-calculate hash join

war...@warrenandrachel.com writes: > When joining two large tables [common in warehousing], a hash join is > commonly selected. Calculating hash values for the merge phase is CPU > intensive. Is there any way to pre-calculate value hashes to save that > time? Would it even grant any performance to

Re: [GENERAL] dynamic insert in plpgsql

Grzegorz Jaśkiewicz writes: > Is there any nice way to do something like that in plpgsql: > > EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||''; See http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers -- dim -- Sent via pgsql-general mailing list (pgsql-general@post

Re: [GENERAL] PostgreSQL Write Performance

Greg Smith writes: > If you're OK with the possibility of losing a measurement in the case of a > system crash Then I'd say use synchronous_commit = off for the transactions doing that, trading durability (the 'D' of ACID) against write performances. That requires 8.3 at least, and will not fsync

Re: [GENERAL] PostgreSQL Write Performance

Tim Uckun writes: > Is there a command like COPY which will insert the data but skip all > triggers and optionally integrity checks. pg_bulkload does that AFAIK. http://pgbulkload.projects.postgresql.org/ Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] converting tables to XML and back

shulkae writes: > Another requirement is to convert the stored XML file back to the > original tables. This helps us to clone a system. I was thinking to > use Perl XML Simple module to generate XML files. What about using pg_dump and pg_restore for the cloning, or maybe a replication solution?

Re: [GENERAL] pg_dump excluding tables content but not table schema

Ivan Sergio Borgonovo writes: > It seems that you have to actually restore the 2 backup separately. > > pg_restore -1 -d mydb < nearly_full.bak > pg_restore -1 -d mydb < schema_only.bak > > I can't think of any other way to restore both in one transaction > unless I backup in plain text. But that

Re: [GENERAL] Visual DATA editor for PostgreSQL?

Dmitry Koterov writes: > Is there a GUI utility to visually edit Postgres DATA (not a database > schema!), which allows at least: > - insert/update rows using screen windowed forms (possibly ugly > auto-generated forms, but - still forms) > - insert foreign key references by selecting them from

Re: [GENERAL] Extracting SQL from logs in a usable format

Le 21 déc. 2009 à 15:24, Chris Ernst a écrit : > Ouch! You're right. And that's would be a deal killer for me. About > 90% of the traffic is prepared queries that are run over and over with > different parameters. The driver project and code are now there it seems: http://frihjul.net/pgsql

Re: [GENERAL] Extracting SQL from logs in a usable format

Le 19 déc. 2009 à 16:20, Chris Ernst a écrit : > Hmm.. That does look very interesting. The only thing that concerns me > is where it says it supports "Basic Queries (Extended queries not yet > supported)". I'm not sure what is meant by "Extended queries". Any idea? I think it refers to the Ex

Re: [GENERAL] Justifying a PG over MySQL approach to a project

"Gauthier, Dave" writes: > I am more concerned with getting a robust DB replication system up and > running. Bucardo looks pretty good, but I've just started looking at > the options. Any suggestions? Master Slave replication? Meaning no writes on the "sister site". If yes, consider Londiste

Re: [GENERAL] Table Partitioning Advice Request

Vincenzo Romano writes: > Is there any performance study for the trigger-based implementation? Consider that if you use RULE to partition, when you DROP a partition the INSERTs are locked out because the query depends on the table being droped. That alone could lead you to stop considering RULEs

Re: [GENERAL] How to remove non-UTF values from a table?

Phoenix Kiula writes: > Is there any SQL possibility to find these columns and replace them > with utf-8 equivalents using some postgresql commands? Couldn't find > anything in the "Strings functions" (chapter 9 of manual). I've bookmarked this for later: http://archives.postgresql.org/pgsql-g

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

Hi, Le 11 déc. 2009 à 01:43, Bruce Momjian a écrit : >> Would you be up for writing the extension facility? > > Uh, well, I need to help with the patch commit process at this point --- > if I find I have extra time, I could do it. I will keep this in mind. If you ever find the time to do it, t

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

Tom Lane writes: > It's not impossible that we'll have to tweak pg_dump a bit; it's > never had to deal with languages that shouldn't be dumped ... Ah, the best would be to have extensions maybe. Then you could do this in initdb, filling in template0: CREATE EXTENSION plpgsql ...; Then at crea

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

Tom Lane writes: > Why not? If they really want to prohibit use of a feature the upstream > project has decided should be standard, that's their privilege. Well, I guess they could also automate their database creation to fix the privileges and assign the ownership of the language to the owner o

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

Tom Lane writes: > Right, just like every other thing that's pre-installed. If a > particular installation wishes to let individual DB owners control this, > the superuser can drop plpgsql from template1. It's not apparent to me > why we need to allow non-superusers to override the project's dec

Re: [GENERAL] pgsql 'prefix' error

Hi, Dimitri Fontaine writes: > Can you test with this version and maybe better data set? [...] > Of course changing that will discard any btree containing a prefix_range > column, so that's going to be 1.1.0 if workable. http://github.com/dimitri/prefix http://github.com/

Re: [GENERAL] pgsql 'prefix'

Hi, Le 23 nov. 2009 à 17:04, Harald Fuchs a écrit : > SELECT id, record > FROM myrecords > WHERE record @> '127' > ORDER BY length(record::text) DESC > LIMIT 1; In prefix 1.0.0 you can say ORDER BY length(record) DESC directly... -- Dimitri Fontaine PostgreSQL DB

Re: [GENERAL] pgsql 'prefix' error

Hi, Bino Oetomo writes: > ERROR: duplicate key value violates unique constraint "myrecords_pkey" > CONTEXT: COPY myrecords, line 2: "12" I think I should add the following code comment to the documentation, if not already done: /* * We invent a prefix_range ordering for convenience, but that's

Re: [GENERAL] schema proxying virtual database

Rajesh Kumar Mallah writes: > my question is , is it a feasible idea to have some special kind of database > in the postgresql cluster that mimics a schema of an existsing > database. Try abusing pgbouncer to this effect. Configure several pgbouncer databases pointing to the same physical one, w

Re: [GENERAL] strange error occurs when adding index

hubert depesz lubaczewski writes: > On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote: >> When I tried to add the following index, I get some strange error. Does >> anyone know what these errors mean and how to fix it? >> Here is the index query: >> create index idx_product_items_digi

Re: [GENERAL] PL/Perl 64-bit and sending emails

Hi, Steve Atkins writes: > On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: >> Or, does someone know of another way to get the >> backend to send an email? > > Have a queue table in the database you put your emails into and an external > process that polls the table, sends the email and deletes

  1   2   >