Re: [GENERAL] Pet Peeves?

On Fri, 2009-01-30 at 15:32 -0500, Bruce Momjian wrote: > Octavio Alvarez wrote: > > On Thu, 2009-01-29 at 13:16 +, Gregory Stark wrote: > > > So, what do people say? Is Postgres perfect in your world or does > it > > > do some > > > things which rub you the wrong way? > > > > For me: > > > >

Re: [GENERAL] sql syntax to replace desc

information_schema.table_name contains the tables information..make sure yuo select type BASE TABLE and make sure the schema is not from either catalog or information_schema e.g. SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN

Re: [GENERAL] sql syntax to replace desc

On Fri, Jan 30, 2009 at 6:20 PM, hendra kusuma wrote: > I'm not talking about \d option at psql console since I need that data > to be called from programming language such as PHP and Ruby Here is a way to see what \d is doing: rich...@dell-desktop:~$ psql -E -U richard richard=> \d animals **

[GENERAL] sql syntax to replace desc

Dear all, I need to see my table structure complete with it's constraint (at least primary key, foreign key, not null, unique) In oracle or mysql, I usually use desc table_name to achieve such result But I find from pgsql-doc that desc isn't implemented. Does anyone know some sql syntax to do tha

Re: [GENERAL] Pet Peeves?

On Fri, 30 Jan 2009 20:38:06 + Gregory Stark wrote: > > rhubbell writes: > > > Nope, had to find it in another package called libpq-dev. > > That's on UbuntuHardy. Maybe it's a maintainer problem? > > > > What logic would lead someone to separate pg_config from everything else? > > Do peop

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: > One thing which has *not* been mentioned which i find positively shocking is > VACUUM. This was once our single biggest source of user complaints. Between > Autovacuum improvements and HOT previously and the free space map in 8.4 the > situation will be much improved. The ot

Re: [GENERAL] PGSQL or other DB?

On Fri, Jan 30, 2009 at 1:04 PM, Sam Mason wrote: > On Fri, Jan 30, 2009 at 08:37:02PM +0100, durumdara wrote: > >> - I can add/modify a table, or a field to a table without "full lock" >> on the table (like DBISAM restructure). Like in FireBird, where the "add >> field" change only the table des

Re: [GENERAL] PGSQL or other DB?

Alan Hodgson wrote: And, honestly, if keeping all the files in once place is a major problem for you, then don't use PostgreSQL. It's not like it's going to be changed in this regard. Um, I think it already did: http://www.postgresql.org/docs/8.3/interactive/sql-createtablespace.html C

Re: [GENERAL] PGSQL or other DB?

On Fri, 30 Jan 2009, durumdara wrote: - quick (re)connect - because mod_python basically not store the database connections persistently If this turns out to be a problem, you may want to use some sort of connection pooling software in order to reduce this overhead. http://wiki.postgresql.or

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: > > rhubbell writes: > > > What's your list look like? Or maybe you want everyone else to do your work > > for you and don't have anything to share. > > Heh, fair enough. Perhaps I should just say you should come to FOSDEM though > :) > > The usual bugaboos are things lik

[GENERAL] use log_statement to log only SELECTs?

Hi, I've been looking for a way to use log_statement to log only select statements; is this possible? (I'm using PostgreSQL 8.1.11) I'd like to know if someone tried to read the data back from the database, but don't care to see the inserts or updates since these may have sensitive data like cre

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: "Daniel Verite" writes: Gregory Stark wrote: Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyw

Re: [GENERAL] Pet Peeves?

On Fri, Jan 30, 2009 at 03:32:45PM -0500, Merlin Moncure wrote: > ooh, I forgot my number personal #1 peeve (and 1a) . > *) select (func()).* will execute func once per field in returned > record. This is side effect of > *) '*' being macro expanded into the plan. I've raised this a couple > of t

Re: [GENERAL] Pet Peeves?

Daniel Verite wrote: Gregory Stark wrote: Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works di

Re: [GENERAL] possible pg_dump bug

On Wed, 2009-01-28 at 09:09 -0800, David Miller wrote: > Pg Dump does not include schema name on insert statement generated from > pg_dump with a -d option when exporting data for a particular table using the > -t . in version 8.3. I believe this same bug exists in 8.4 but > have not confirmed i

Re: [GENERAL] Pet Peeves?

On Fri, 30 Jan 2009 20:46:22 + Gregory Stark wrote: > > rhubbell writes: > > > What's your list look like? Or maybe you want everyone else to do your work > > for you and don't have anything to share. > > Heh, fair enough. Perhaps I should just say you should come to FOSDEM though > :)

Re: [GENERAL] complex custom aggregate function

Oops, I meant group by floor(h_q-x) instead of group by (h_q-x)/1 Marc Mamin > select H,A > FROM > ( > select min(h_q) as H, avg(x) as A > group by h_q/1 > union all > select min(h_q), avg(x) > group by (h_q-0.25)/1 > union all > select min(h_q), avg(x) > group by (h_q-0.50)/1 > union a

Re: [GENERAL] PGSQL or other DB?

durumdara wrote: - quick (re)connect - because mod_python basically not store the database connections persistently You should consider using a Python connection pool with something like Pylons (http://pylonshq.com), I use dbutils: http://www.webwareforpython.org/DBUtils/Docs/UsersGuide.

Re: [GENERAL] Pet Peeves?

rhubbell writes: > What's your list look like? Or maybe you want everyone else to do your work > for you and don't have anything to share. Heh, fair enough. Perhaps I should just say you should come to FOSDEM though :) The usual bugaboos are things like having to do a dump/restore to upgrade,

Re: [GENERAL] complex custom aggregate function

Hello, not very smart, but something like following should do the job: h_mv 1.00x 1.25x 1.50x 1.75x 2.00x 2.25x 2.50x 2.75x 3.00x 3.25x 3.50x 3.75x 4.00x 4.25x 4.50x 4.75x ... select H,A FROM ( select min(h_q) as H, avg(x) as

Re: [GENERAL] Pet Peeves?

rhubbell writes: > Nope, had to find it in another package called libpq-dev. > That's on UbuntuHardy. Maybe it's a maintainer problem? > > What logic would lead someone to separate pg_config from everything else? > Do people often just install the server and nothing else? Then what? This is act

Re: [GENERAL] PGSQL or other DB?

durumdara wrote: Another important thing that I don't understand (what as I saw) that the PostGreSQL is store the databases in one, unseparatable file set, in a directory named data. In another databases, like DBISAM, FireBird, MySQL, the databases are separated to another directories/files.

Re: [GENERAL] Pet Peeves?

Octavio Alvarez wrote: > On Thu, 2009-01-29 at 13:16 +, Gregory Stark wrote: > > So, what do people say? Is Postgres perfect in your world or does it > > do some > > things which rub you the wrong way? > > For me: > > Lack of column-level privileges. It just doesn't help scalability at > all.

Re: [GENERAL] Pet Peeves?

On 1/30/09, Merlin Moncure wrote: > On 1/29/09, Gregory Stark wrote: > > > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > > FOSDEM 2009 this year. I have a pretty good idea what some them are of > course, > > > Here are couple of mine. Had to dig a bit. There

Re: [GENERAL] PGSQL or other DB?

On Jan 30, 2009, at 2:37 PM, durumdara wrote: Dear PG Users! I don't want to make a heated debate with this subject, but I wanna ask about your experiences because we need to make a new special site and we wanna know, which DB is the best for this... This website will supports up to 200

Re: [GENERAL] PGSQL or other DB?

> > This "one datadir" is seems to be not too good for us. We used DBISAM > > in our clients, and many times when we got some filesystem error, we > > can simply recover the tables - from the files. > pg_dump files with the custom format can be used to selectively restore tables. It's really easy

Re: [GENERAL] Pet Peeves?

hubert depesz lubaczewski wrote: > 1. pg_dump -d - do i need to explain? I wonder if we should just rename the -d behavior to something else and throw a proper error (or ignore the flag if it is before the dbname). -- Bruce Momjian http://momjian.us EnterpriseDB

Re: [GENERAL] PGSQL or other DB?

On Fri, Jan 30, 2009 at 08:37:02PM +0100, durumdara wrote: > This website will supports up to 200 corporations. > The functions are similar, but because of many differents of these corps > (datas to keep, way to working them), we wanna make many databases (one > DB for one corp). Databases or sc

[GENERAL] PGSQL or other DB?

Dear PG Users! I don't want to make a heated debate

Re: [GENERAL] Pet Peeves?

On 1/30/09, Scott Ribe wrote: > Having to spell out params just to drop a procedure (especially when I'm > trying things out, realize params need to be changed, and so can't just use > "create or replace function"). use psql tab completion. merlin -- Sent via pgsql-general mailing list (pgsq

Re: [GENERAL] Pet Peeves?

On Fri, Jan 30, 2009 at 6:09 PM, rhubbell wrote: > On Thu, 29 Jan 2009 13:16:17 + > Gregory Stark wrote: > >> >> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at >> FOSDEM 2009 this year. I have a pretty good idea what some them are of >> course, > > I'll ask again.

Re: [GENERAL] Pet Peeves?

Having to spell out params just to drop a procedure (especially when I'm trying things out, realize params need to be changed, and so can't just use "create or replace function"). Perhaps "drop function foobar" could drop the function if there is only 1 function named foobar? Perhaps "drop functi

Re: [GENERAL] Pet Peeves?

Nope, had to find it in another package called libpq-dev. That's on UbuntuHardy. Maybe it's a maintainer problem? What logic would lead someone to separate pg_config from everything else? Do people often just install the server and nothing else? Then what? You've got a server can you have a worki

Re: [GENERAL] Pet Peeves?

On Thu, 29 Jan 2009 13:16:17 + Gregory Stark wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, I'll ask again. You say "I have a pretty good idea what some them are of course

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without the need to know in advance the number of columns: http://msdn.microsoft.com/en-us/library/bb208956.aspx That's puzzling. I wonder what they do about clients requesting info about the res

Re: [GENERAL] Pet Peeves?

On Thu, Jan 29, 2009 at 01:16:17PM +, Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it > do some things which rub you the wrong way? * "anonymous records" are a tad annoying to use SELECT (1,2,3) ORDER BY 1; SELECT ARRAY[(1,2,3)]; SELECT MIN

Re: [GENERAL] Pet Peeves?

Guy Rouillier wrote: > Back in March 2005, I started an email thread titled "Debugging > deadlocks". Most of the experienced PGers participated in that thread. > The basic issue at that time was that inserting a row into a table with a > foreign key placed an exclusive row-level lock (SELECT

Re: [GENERAL] Pet Peeves?

You can however pull it from a -Fc backup with pg_restore. Just FYI. Joshua D. Drake Or strip it from a pg_dump/pg_dumpall with sed. Or write your own function-dumper based on ideas gleaned from various notes/comments on the web (my approach). I had not thought of using the -Fc approac

Re: [GENERAL] Pet Peeves?

Peter Eisentraut wrote: > * Difficult to trace nested things, e.g., nested PL/pgSQL calls, > cascaded foreign key actions. +1 That's a really good point. It'd be particularly nice to have some way to get EXPLAIN ANALYZE to report on details of the execution of called SQL functions. -- Craig Rin

Re: [GENERAL] Pet Peeves?

On 1/29/09, Gregory Stark wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of > course, Here are couple of mine. Had to dig a bit. There may be some duplication with others: *) In place

[GENERAL] Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling

Hannu Krosing wrote: On Fri, 2009-01-30 at 14:09 +0300, Igor Katson wrote: As far as I understand, it is a known problem of using plproxy, that it cannot be rolled back if used inside transactions. But I need something similar to this functionality. I have some data, that is duplicated acr

Re: [GENERAL] Call volume query

In article <1233269836.13476.10.ca...@ubuntu>, Mike Diehl writes: > Hi all. > I've encountered an SQL problem that I think is beyond my skills... > I've got a table full of records relating to events (phone calls, in > this case) and I need to find the largest number of events (calls) > occurrin

Re: [GENERAL] database/table snapshot

Hello We have script called cube_dispatcher in SkyTools that we we use for similar sounding purpose. We must provide daily snapshots of changes to online tables for Business Intelligence team. So we create trigger on every table that needs to be handled that writes changed records into queue and t

Re: [GENERAL] encoding of PostgreSQL messages

Hi, This topic seems to be related to the bug report [ODBC] Localized error messages, wrong charset . Bruce Momjian wrote: Added to TODO: Improve encoding of connection startup messages sent to the client Currently some authentication error messages are sent in

Re: [GENERAL] Runaway postgres process?

2009/1/30 Laurent Wandrebeck : > 2009/1/30 A B : >> Hi. > Hello, >> I just discovered three "postmaster" processes running and really >> eating all the cpu. >> I'm not sure why? >> So, what steps do I take for finiding the queries that is causing this? > select * from pg_catalog.pg_stat_activity;

Re: [GENERAL] Runaway postgres process?

Well, it stoped. And in the log I can see: LOGG: statement: UPDATE VList SET updated=true WHERE id=985 LOGG: duration: 1129993.209 ms and a few lines below (with queries in between) LOGG: statement: UPDATE VList SET updated=false WHERE id=985 LOGG: duration: 0.400 ms So, something was block

[GENERAL] Runaway postgres process?

Hi. I just discovered three "postmaster" processes running and really eating all the cpu. I'm not sure why? So, what steps do I take for finiding the queries that is causing this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://ww

Re: [GENERAL] Pet Peeves?

"Daniel Verite" writes: > Gregory Stark wrote: > >> Is it the hierarchical query ability you're looking for or pivot? >> The former we are actually getting in 8.4. >> >> AFAIK even in systems with pivot you still have to >> declare a fixed list of columns in advance anyways. >> Do you see

[GENERAL] database/table snapshot

Hey folks, I wonder, how would you guys approach table snapshots. So the thing is, I have table X (I have few more tables, but lets simplify that). That table is being replicated to X' on other server. Now I need an ability to see changes, say every interval T. So, the simplest solution would be t

Re: [GENERAL] need contact in Nepal

I have no idea how to directly help you with that one, but I would certainly contact linux/freebsd/openbsd user groups in that country, and try to arrange something that way. hth. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://w

Re: [GENERAL] Pet Peeves?

On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please wrote: > * LIMIT and OFFSET don't accept non-constant (or non function) values > no longer true with 8.4 gjaskie=# create table foo(a serial, b varchar); gjaskie=# insert into table foo(b) select generate_series(1,1, (random()*100)::int); gjas

Re: [GENERAL] Pet Peeves?

On Friday 30 January 2009 14:26:45 Peter Eisentraut wrote: > Gregory Stark wrote: > > So, what do people say? Is Postgres perfect in your world or does it do > > some things which rub you the wrong way? > > * No offer of anything-but-CVS on pgfoundry.org > > * Lack of REINDEX CONCURRENTLY > > * Too

Re: [GENERAL] Pet Peeves?

On Fri, Jan 30, 2009 at 1:26 PM, Peter Eisentraut wrote: > * Lack of REINDEX CONCURRENTLY +1 > * Too many obscure configuration options: memory management, autovacuum > management, freespace map, background writer, WAL. +100 > * Writing robust scripts with psql is difficult and bizarre. Well, to

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works differently? MS

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: So, what do people say? Is Postgres perfect in your world or does it do some things which rub you the wrong way? * No offer of anything-but-CVS on pgfoundry.org * Lack of REINDEX CONCURRENTLY * Too many obscure configuration options: memory management, autovacuum managem

Re: [GENERAL] PGSQL and Javascript

On Friday 30. January 2009, Reg Me Please wrote: >Hello all. > >Is there a way to directly access PGSQL from a Javascript application? >With no application server intervention, I mean. >Just like libq allows access from C/C++. The usual way to handle it is to let the JavaScript code call a server

Re: [INTERFACES] [GENERAL] PGSQL and Javascript

On Fri, Jan 30, 2009 at 7:43 AM, Reg Me Please wrote: > I already have a JS interpreter (spidermonkey) but there is no PGSQL > interfacing API! > This'd be why I'm asking. > Ah. Sorry again. No idea if this will be useful, but you might take a look at: http://www.whitebeam.org/ Sean > > On

Re: [INTERFACES] [GENERAL] PGSQL and Javascript

Well you can add you own objects to Spidermonkey via its c api. So you can implement something like Database object in c using libq and access it from your script. Regards, Miha Reg Me Please pravi: I already have a JS interpreter (spidermonkey) but there is no PGSQL interfacing API! This'd b

Re: [GENERAL] PGSQL and Javascript

look at apache cocoon for serverside javascript (rhino engine) you can direct access java classes. write all db stuff in java and access it from js var dao = Packages.my.own.package.DAOController.findAll(); Packages.java.lang.System.out.println(dao.myproperty); regards thomas Reg Me Please s

[GENERAL] arrays and block size

Hi, I need to store a lot of int8 columns (2000-2500) in a table. I was thinking about using int8[], and I would like to know: 1) is there a max size for arrays? I guess I could have 1 GB "worth" of values, but I would like a confirmation 2) there won't be any updates, only inserts and selects;

Re: [GENERAL] complex custom aggregate function

Gregory Stark wrote: > From what I read of your > description you want to produce one record per input record. Exactly. > 8.4 Will have OLAP Window functions which can implement things like > moving averages. Using 8.3: could I do it caching all the values "somewhere" in a custom aggregation

Re: [INTERFACES] [GENERAL] PGSQL and Javascript

I already have a JS interpreter (spidermonkey) but there is no PGSQL interfacing API! This'd be why I'm asking. On Friday 30 January 2009 13:24:59 Sean Davis wrote: > On Fri, Jan 30, 2009 at 7:09 AM, Reg Me Please wrote: > > I'd like to write part of the application in Javascript, server side, no

Re: [INTERFACES] [GENERAL] PGSQL and Javascript

On Fri, Jan 30, 2009 at 7:09 AM, Reg Me Please wrote: > I'd like to write part of the application in Javascript, server side, not > in > the web page. > I missed your point. Do a google search for javascript interpreters in the language of your choice. For example, there is Rhino for java. Sea

Re: [GENERAL] PGSQL and Javascript

I'd like to write part of the application in Javascript, server side, not in the web page. On Friday 30 January 2009 12:53:05 Allan Kamau wrote: > May be Javascript + JDBC not sure. > > But the question is why would you want to do so? > Javascript can be read easily by the user having the javascr

Re: [GENERAL] [INTERFACES] PGSQL and Javascript

No. I'm not sure how that could be done given the security concerns. Sean On Fri, Jan 30, 2009 at 6:40 AM, Reg Me Please wrote: > Hello all. > > Is there a way to directly access PGSQL from a Javascript application? > With no application server intervention, I mean. > Just like libq allows acces

[GENERAL] Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling

On Fri, 2009-01-30 at 14:09 +0300, Igor Katson wrote: > As far as I understand, it is a known problem of using plproxy, that it > cannot be rolled back if used inside transactions. But I need something > similar to this functionality. > > I have some data, that is duplicated across the DB partit

Re: [GENERAL] PGSQL and Javascript

May be Javascript + JDBC not sure. But the question is why would you want to do so? Javascript can be read easily by the user having the javascript running on their browser. JDBC or any other database connecting client will want to some how authenticate the user in most cases the username, passwor

[GENERAL] PGSQL and Javascript

Hello all. Is there a way to directly access PGSQL from a Javascript application? With no application server intervention, I mean. Just like libq allows access from C/C++. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgre

Re: [GENERAL] complex custom aggregate function

Scara Maccai writes: > It would be very easy if the input to the custom aggregate function was > ordered (because I would keep 4 internal counters), but I guess there's > no way of "forcing" the ordering of the input to the function, right? You can with a subquery. Something like SELECT agg

[GENERAL] Plproxy functions inside transactions and Pl/pgsql exception handling

As far as I understand, it is a known problem of using plproxy, that it cannot be rolled back if used inside transactions. But I need something similar to this functionality. I have some data, that is duplicated across the DB partitions, and to be exact, there is i.e. a plproxy-partitioned DB,

Re: [GENERAL] Pet Peeves?

Octavio Alvarez writes: > Now, last time I checked, Postgresql lacked an SQL extension to create > crosstab queries. Even though contrib have some helper functions, they > are not official and work only if the number of columns is actually > predefined. For instance if you want to create a query

Re: [GENERAL] Pet Peeves?

On Thu, 2009-01-29 at 13:16 +, Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it > do some > things which rub you the wrong way? For me: Lack of column-level privileges. It just doesn't help scalability at all. You end up having different tables each

Re: [GENERAL] ssl to more than one server

On Thu, 29 Jan 2009 21:56:05 +0100 Ivan Sergio Borgonovo wrote: > On Thu, 29 Jan 2009 12:53:20 -0500 > Tom Lane wrote: > > > Ivan Sergio Borgonovo writes: > > > I succeded to connect to one postgresql server with ssl. > > > Now it's the time of the second... but postgresql clients > > > (pgsql

[GENERAL] complex custom aggregate function

Hi all, I have a table like: value int, quarter timestamp I need an aggregate function that gives back the maximum "value" using this algorithm: AVG of the first hour (first 4 quarters) (AVG0) same as above, but 1 quarter later (AVG1) same as above, but n quarters later (AVGn) result: th

Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

On Fri, Jan 30, 2009 at 7:55 AM, Scott Marlowe wrote: > On Thu, Jan 29, 2009 at 12:41 PM, rhubbell wrote: >> On Thu, 29 Jan 2009 11:34:00 -0800 (PST) >> Jeff Frost wrote: >> >>> On Thu, 29 Jan 2009, rhubbell wrote: >>> >>> > Umm, because md5 doesn't work and trust does work. >>> >>> Generally th