[GENERAL] Question on INDEX and SQL - stalling my database?

Hi all, I'm having problems with a query that's just "stalling" my database. If someone could help me out - I posted a forum topic on http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html There's just this one integer field, which when searched on, "stalls"

[GENERAL] Problem with pg_hba.conf?

  Hello, All. We have compiled and installed postgreSQL 7.4.17 version on HP-UX without enabling ssl option. Rather we used most of the default option except lib/data directories and -enableThread safety. We are getting couple of problems during initdb and psql'ing. 1) $libdir in one of the

Re: [GENERAL] I get a error msg when i try to connect to the DB

On 8/18/07, Rajaram J <[EMAIL PROTECTED]> wrote: > Hi > Yes I restarted postmaster after that but the problem still persists. OK, Only looked at your file just now. Those are meant to be the IP of your client. You weren't meant to put questionmarks into the file. > Regards > Rajaram Cheers

Re: [GENERAL] Writing most code in Stored Procedures

Steve Manes wrote: Moving all the application-bound inserts into stored procedures didn't achieve nearly the performance enhancement I'd assumed I'd get, which I figured was due to the overhead of the procs themselves. That's the conclusion I'm coming to as well for my app with very high inse

Re: [GENERAL] More Time Zone fun

Naz Gassiep <[EMAIL PROTECTED]> writes: > ERROR: time zone "Asia/Riyadh87" appears to use leap seconds DETAIL: > PostgreSQL does not support leap seconds. > Surely it's a bug to have timezones in the list that result in errors > being thrown? Perhaps, but weren't you the person complaining abou

Re: [GENERAL] More Time Zone fun

Just to further note an oddity, apparently PostgreSQL in fact *does* support leap seconds since 7.4 After scouring the manual for info on this, I discovered on this page: http://www.postgresql.org/docs/8.2/static/release-7-4.html Tom added support for 60 second values in the seconds component

Re: [GENERAL] I get a error msg when i try to connect to the DB

Hi Yes I restarted postmaster after that but the problem still persists. Regards Rajaram - Original Message - From: "Andrej Ricnik-Bay" <[EMAIL PROTECTED]> To: "Rajaram J" <[EMAIL PROTECTED]> Cc: Sent: Saturday, August 18, 2007 4:01 AM Subject: Re: [GENERAL] I get a error msg when i

Re: [GENERAL] Blobs in Postgresql

On 8/18/07, Ron Olson <[EMAIL PROTECTED]> wrote: > The language is Java. I've made some tests and they work very well for 25meg > filesworks exactly the way it should, first time. MySQL had all kinds of > nasty surprises for me when I first started working with blobs, but I can > say that I too

Re: [GENERAL] Blobs in Postgresql

The language is Java. I've made some tests and they work very well for 25meg filesworks exactly the way it should, first time. MySQL had all kinds of nasty surprises for me when I first started working with blobs, but I can say that I took my code, changed the driver, and it all works like a ch

Re: [GENERAL] Writing most code in Stored Procedures

Ron Johnson wrote: Moving all the application-bound inserts into stored procedures didn't achieve nearly the performance enhancement I'd assumed I'd get, which I figured was due to the overhead of the procs themselves. Would that be because the original app was written in a compiled language, b

Re: [GENERAL] Writing most code in Stored Procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/17/07 18:00, Steve Manes wrote: > Guy Rouillier wrote: >> I have a thread I started ages ago over on the PERFORM list that I'm >> sadly just now being able to provide some insight on. I'll be >> replying on that thread in more detail, but the sh

Re: [GENERAL] Blobs in Postgresql

On 8/15/07, Ron Olson <[EMAIL PROTECTED]> wrote: > Hi all- > > I am evaluating databases for use in a large project that will hold image > data as blobs. I know, everybody says to just store pointers to files on the > disk...can't do it here...the images are of a confidential nature and access > to

Re: [GENERAL] Writing most code in Stored Procedures

Guy Rouillier wrote: I have a thread I started ages ago over on the PERFORM list that I'm sadly just now being able to provide some insight on. I'll be replying on that thread in more detail, but the short of it turns out to be that at least in this one application, using stored procs for inse

[GENERAL] Seeking datacenter PITR backup suggestions

Greetings, We have several web applications with Pg 8.2.x running on isolated servers (~25). The database size on each machines (du -h pgdata) is ~2 GB. We have been using nightly filesystem backup (stop pg, tar backup to ftp, start pg) and it worked well. We would like to move to PITR backups si

Re: [GENERAL] Writing most code in Stored Procedures

Guy Rouillier wrote: Steve Manes wrote: I'm fairly hardcore about keeping as much business logic as I can in the database. In fact, I only do SELECTs from the application, and usually via Views. All inserts, updates and deletes are via procs. ... And, yes, it's faster. Particularly if busin

Re: [GENERAL] Enterprise Wide Deployment

On 8/14/07, john_sm <[EMAIL PROTECTED]> wrote: > Hey guys, for an enterprise wide deployment, what will you > suggest and why among - Red Hat Linux, Suse Linux and > Ubuntu Linux, also, do you think, we can negotiate the > support pricing down? For all it's worth: my personal experiences with RH s

Re: [GENERAL] I get a error msg when i try to connect to the DB

On 8/18/07, Rajaram J <[EMAIL PROTECTED]> wrote: > Hi > > I tred this option but still i get the same message. Any more options that i > can try. And after that change you restarted the postmaster? > Regards > Rajaram J Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make

Re: [GENERAL] Writing most code in Stored Procedures

Steve Manes wrote: I'm fairly hardcore about keeping as much business logic as I can in the database. In fact, I only do SELECTs from the application, and usually via Views. All inserts, updates and deletes are via procs. ... And, yes, it's faster. Particularly if business logic decisions ha

Re: [GENERAL] Enterprise Wide Deployment

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Scott Marlowe wrote: >> On 8/14/07, john_sm <[EMAIL PROTECTED]> wrote: >>> Hey guys, for an enterprise wide deployment, what will you suggest and why >>> among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we >>> can negotiate the

Re: [GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

On 18/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > [Please reply to the list so that others may benefit from and > participate in the discussion.] > > >> If you're including - in a range as a character, doesn't it have to > >> go first? > >> Try this: > >> > >> WHERE t_code ~ $re$[^-A-Za-

Re: [GENERAL] I get a error msg when i try to connect to the DB

HiSorry I forgot to give the pg_hba.conf detail Error message - == removing existing temp installation    creating temporary installation    initializing database system   starting postma

Re: [GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

[Please reply to the list so that others may benefit from and participate in the discussion.] On Aug 17, 2007, at 12:50 , Phoenix Kiula wrote: On 18/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Aug 17, 2007, at 10:58 , Phoenix Kiula wrote: What would be the SQL to find data o

Re: [GENERAL] SELECT question

On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: * * Do not Cc: me, because I am on THIS list, if I write here.* You might want to consider changing your mailing list subscription settings to "eliminatecc", e.g.,

Re: [GENERAL] Enterprise Wide Deployment

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Scott Marlowe wrote: > On 8/14/07, john_sm <[EMAIL PROTECTED]> wrote: >> Hey guys, for an enterprise wide deployment, what will you suggest and why >> among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we >> can negotiate the suppo

Re: [GENERAL] Non-superuser creating a flat file

On Aug 13, 2007, at 10:07 AM, Terri Reid wrote: I have data that is being updated in a table that I need to export to a flat file via a database trigger on insert or update. The user performing the update will not be a superuser. I’ve tried to use COPY TO, but that doesn’t work for non-sup

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

> Isn't the 'try' statement rather similar to a 'savepoint' command? I > realize it would be difficult to override the behaviour of try {...} > catch (...) {...}, but it shouldn't be too hard to wrap it somehow for > exceptions in database code. Yes, but I believe the OP was getting two levels of

[GENERAL] More Time Zone fun

I'm making (slow) progress in my timezone system, and I just noticed this little behavioral nugget, which surely is a bug. In the system view pg_timezone_names is a few timezones that use leap seconds. An example which I tested is Asia/Riyadh87. When I attempt to SET TIME ZONE using this timezo

Re: [GENERAL] Enterprise Wide Deployment

On 8/14/07, john_sm <[EMAIL PROTECTED]> wrote: > > Hey guys, for an enterprise wide deployment, what will you suggest and why > among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we > can negotiate the support pricing down? It's more about your skill set and familiarity than w

Re: [GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

On Fri, 17 Aug 2007, Michael Glaesemann wrote: On Aug 17, 2007, at 10:58 , Phoenix Kiula wrote: What would be the SQL to find data of this nature? My column can only have alphanumeric data, and the only symbols allowed are "-" and "_", so I tried this regexp query: select id, t_code

Re: [GENERAL] Help creating a function

On Aug 16, 2007, at 9:35 AM, Madison Kelly wrote: Note: This is being sent again (in case it shows up later). It never seemed to have made it to the list. Hi all, I'm using ulogd with PostgreSQL which stores IP addresses as 32bit unsigned integers. So when I select some data I get somethi

Re: [GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

On Aug 17, 2007, at 10:58 , Phoenix Kiula wrote: What would be the SQL to find data of this nature? My column can only have alphanumeric data, and the only symbols allowed are "-" and "_", so I tried this regexp query: select id, t_code from traders where t_code ~ '[^A-Za-z1-9\-]'

Re: [GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

"Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > select id, t_code > from traders > where t_code ~ '[^A-Za-z1-9\-\_]' > limit 100; > > This gives me an error: "ERROR: invalid regular expression: invalid > character range". Put the dash at the start of the character class: [^-A-Za-

[GENERAL] FInding "corrupt" values in UTF-8 tables (regexp question, I think)

I'm noticing that some of my data has been imported as junk text: For instance: klciã«" What would be the SQL to find data of this nature? My column can only have alphanumeric data, and the only symbols allowed are "-" and "_", so I tried this regexp query: select id, t_code from

Re: [GENERAL] why it doesn't work? referential integrity

> > looks strange to me too, but i never had foreign keys to the same table. > it works if you define your chekced_by FK deferrable with > > checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL > DEFERRABLE INITIALLY DEFERRED, > > it seams that postgresql does its job in a proc

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Sorry for top posting - but this is an annoying of this web interface to email. :-( Isn't what you're doing here a misuse of the idea of a transaction. I don't claim to be an expert in this, but I thought the idea of a transaction was that you bundle a group of statements together that m

Re: [GENERAL] Where are the Server error logs

On 17/08/2007 15:47, Rajaram J wrote: where can the server log files be found. if i need to set some parameter in which file do i do that. The server logging method and log files are set in postgresql.conf - this is well documented in the file itself, as well as the PostgreSQL docs. Ray. -

[GENERAL] Where are the Server error logs

Hi when i try to use psql i get message bash-2.01# /opt/sfmdb/pgsql/bin/psql -U sfmdb psql: FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. where can the server log files be found. if i need to set some parameter in which file do i do that. Regards Rajaram J

Re: [GENERAL] PostgreSQL clustering (shared disk)

On Fri, Aug 17, 2007 at 04:19:57PM +0200, Hannes Dorbath wrote: > On 17.08.2007 15:59, Tom Lane wrote: > >On the other side of the coin, I have little confidence in DRBD > >providing the storage semantics we need (in particular guaranteeing > >write ordering). So that path doesn't sound exactly ri

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On 17/08/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > On Fri, Aug 17, 2007 at 10:22:55PM +0800, Phoenix Kiula wrote: > > Wow, smartest advice of the day! Yes, a lot of our data in that column > > has dots and numbers (800,000 compared to 6 million), so I wanted to > > get only to the

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On Fri, Aug 17, 2007 at 10:22:55PM +0800, Phoenix Kiula wrote: > Wow, smartest advice of the day! Yes, a lot of our data in that column > has dots and numbers (800,000 compared to 6 million), so I wanted to > get only to the stuff that was pure alphabets, but just didn't think > of how. what i rea

Re: [GENERAL] PostgreSQL clustering (shared disk)

Hi, > On the other side of the coin, I have little confidence in DRBD > providing the storage semantics we need (in particular guaranteeing > write ordering). So that path doesn't sound exactly risk-free either. DRBD seems to enforce strict write ordering on both sides of the link according to

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On 17/08/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote: > > I have dropped all indexes/indicises on my table, except for the > > primary key. Still, when I run the query: > > UPDATE mytable SET mycolumn = lower(mycolumn)

Re: [GENERAL] Repeat posts

"Raymond O'Donnell" <[EMAIL PROTECTED]> writes: > Is it just me? :-) from time to time I get repeat broadcasts from > various PG mailing lists - posts that I've already received several days > previously are sent again. There are people on the lists with broken mail software that resubmits old t

Re: [GENERAL] PostgreSQL clustering (shared disk)

On 17.08.2007 15:59, Tom Lane wrote: On the other side of the coin, I have little confidence in DRBD providing the storage semantics we need (in particular guaranteeing write ordering). So that path doesn't sound exactly risk-free either. To my understanding DRBD provides this. I think a discu

Re: [GENERAL] I get a error msg when i try to connect to the DB

Hi I tred this option but still i get the same message. Any more options that i can try. Regards Rajaram J - Original Message - From: "Brad Nicholson" <[EMAIL PROTECTED]> To: "Rajaram J" <[EMAIL PROTECTED]> Cc: Sent: Thursday, August 16, 2007 9:35 PM Subject: Re: [GENERAL] I get a e

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On Fri, Aug 17, 2007 at 09:50:42PM +0800, Phoenix Kiula wrote: > > How big is the actual table itself (in bytes). > Where should I find this? select pg_relation_size('mytable'); depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote: > I have dropped all indexes/indicises on my table, except for the > primary key. Still, when I run the query: > UPDATE mytable SET mycolumn = lower(mycolumn); can you please check this: select count(*) from mytable; select count(

Re: [GENERAL] Accessing pg_timezone_names system view

On Fri, Aug 17, 2007 at 11:51:52PM +1000, Naz Gassiep wrote: > I was wondering if there is any reason that accessing the system view > pg_timezone_names is extremely slow relative to other queries. The > following query: > >SELECT * FROM pg_timezone_names; > > Executes in between 29ms and 3

Re: [GENERAL] PostgreSQL clustering (shared disk)

"Mikko Partio" <[EMAIL PROTECTED]> writes: > This was my original intention. I'm still quite hesitant to trust the > fencing devices ability to quarantee that only one postmaster at a time is > running, because of the disastrous possibility of corrupting the whole > database. Making that guarantee

Re: [GENERAL] Repeat posts

On 17/08/2007 13:48, Martijn van Oosterhout wrote: AIUI, posts from non-subscribers can get held for moderation. Because they CC the other people the thread kept going. Later on the moderator approves the messages and they get sent out again. Ah - I see. As I said, it wasn't a problemjust

[GENERAL] Accessing pg_timezone_names system view

I was wondering if there is any reason that accessing the system view pg_timezone_names is extremely slow relative to other queries. The following query: SELECT * FROM pg_timezone_names; Executes in between 29ms and 32ms on my server. It takes about the same when I put a WHERE name =

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On 17/08/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote: > > However, I am a little frustrated by the amount of time PGSQL takes to > > complete tasks. Just to accommodate these tasks, my conf file has the > > following: > > > >

[GENERAL] SELECT question

* * Do not Cc: me, because I am on THIS list, if I write here.* * Keine Cc: an mich, bin auf DIESER Liste wenn ich hier schreibe. * * Ne me mettez pas en Cc:, je suis sur CETTE liste, si j'ecris ici. * ***

Re: [GENERAL] Vacuum problem

Kari Lavikka <[EMAIL PROTECTED]> writes: > However, database wide vacuum prevents user_online vacuum from deleting > old row versions and the table gets incredibly bloated. Update to 8.2. There was a fix put in for this specific issue. regards, tom lane

Re: [GENERAL] Repeat posts

On Fri, Aug 17, 2007 at 01:13:33PM +0100, Raymond O'Donnell wrote: > Hi all, > > Is it just me? :-) from time to time I get repeat broadcasts from > various PG mailing lists - posts that I've already received several days > previously are sent again. AIUI, posts from non-subscribers can get hel

Re: [GENERAL] Some frustrations with admin tasks on PGSQL database

On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote: > However, I am a little frustrated by the amount of time PGSQL takes to > complete tasks. Just to accommodate these tasks, my conf file has the > following: > > autovacuum = off > wal_buffers=64 > checkpoint_segments=1000

Re: [GENERAL] Repeat posts

Raymond O'Donnell wrote: > Hi all, > > Is it just me? :-) from time to time I get repeat broadcasts from > various PG mailing lists - posts that I've already received several days > previously are sent again. > > It's not a major problem, nor even annoying in any wayI was just > wondering if

[GENERAL] tsearch2 query question

Hi, SELECT contentid, title, (rank(to_tsvector(body),q) + rank(to_tsvector(title),q) + rank(to_tsvector(subtitle),q)) AS Score FROM content, to_tsquery('parkyeri') AS q WHERE statusid = 1 AND ispublished = 1 AND (to_tsvector(body) @@ q OR to_tsvector(title) @@ q OR to_tsvector(sub

Re: [GENERAL] Finding my database

On 17/08/2007 13:32, Raymond O'Donnell wrote: Was the previous installation of Postgres also 8.2? If not - if it was an earlier version - I'd put the old version back, point it at the data directory, then use pg_dump to export the data if you want to upgrade at that point. I meant to add tha

Re: [GENERAL] Finding my database

On 17/08/2007 05:44, [EMAIL PROTECTED] wrote: needed to move it to the D Drive. After completely messing everything up, I am now unable to get it working. I have uninstalled everything and now have v8.2 installed on the D drive, and I put the data directory back within that I know it's not a

[GENERAL] Repeat posts

Hi all, Is it just me? :-) from time to time I get repeat broadcasts from various PG mailing lists - posts that I've already received several days previously are sent again. It's not a major problem, nor even annoying in any wayI was just wondering if anyone else has noticed it. Ray.

Re: [GENERAL] copy command - date

Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane <[EMAIL PROTECTED]> wrote: > Paul Lambert <[EMAIL PROTECTED]> writes: > >> novice wrote: > >>> db5=> \copy

Re: [GENERAL] Trigger Procedure Error: NEW used in query that is not in a rule

Javier Fonseca V. wrote: Hello. I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing. I think that it's working alright except for the next line: EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT new.*'; PostgreSQL keeps telling me: "ERROR: NEW us

Re: [GENERAL] memory optimization

In article <[EMAIL PROTECTED]>, Sabin Coanda <[EMAIL PROTECTED]> wrote: [...] % So, what is better from the postgres memory point of view: to use temporary % objects, or to use common variables ? Temp tables can cause serious bloat in some of the system catalog tables. -- Patrick TJ McPhee Nor

[GENERAL] Finding my database

Hi I initially had the Windows version of PostgreSQL v8.0 running on my C drive and was connected to a database in the data subdirectory below PostgreSQL. I needed to move it to the D Drive. After completely messing everything up, I am now unable to get it working. I have uninstalled everyth

[GENERAL] Enterprise Wide Deployment

Hey guys, for an enterprise wide deployment, what will you suggest and why among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we can negotiate the support pricing down? -- View this message in context: http://www.nabble.com/Enterprise-Wide-Deployment-tf4265690.html#a12139899

Re: [GENERAL] Compound Indexes

"Phoenix Kiula" writes: I have a table with ten columns. My queries basically one column as the first WHERE condition, so an index on that column is certain. But the columns after that one vary depending on end-user's choice (this is a reporting application) and so does the sorting order. In MyS

Re: [GENERAL] Bytea question with \208

Decibel! wrote: On Thu, Aug 09, 2007 at 04:16:15PM -0400, Woody Woodring wrote: My bad, the table I was looking (8.7) at had the first column as the decimal representation and I did notice that the numbers changed as they moved right. Is there a way for bytea to take a hex number, or do I need

Re: [GENERAL] Persistent connections in PHP

Hannes Dorbath schrieb: On 15.08.2007 10:53, Torsten Zühlsdorff wrote: If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Uhm, but how does TSea

[GENERAL] 8.3 performance

I came upon this article http://www.internetnews.com/dev-news/article.php/3647376 The last 2 paragraphs caught my eyes: "Among the improvements expected in PostgreSQL 8.3 are further performance gains. "'The most exciting of these is an optimization that would improve performance on OLTP system

[GENERAL] Emigrar SqlServer a Posgres

Bueno me pueden dar una mano para emigrar una bases de dato de sql a posgres. Slds Ivan Rivera Tel#(505)8074075 Managua, Nicaragua __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! Regístrate ya - http://correo.e

Re: [GENERAL] Writing most code in Stored Procedures

On 8/16/07, Steve Manes <[EMAIL PROTECTED]> wrote: > On 8/15/07, Rohit <[EMAIL PROTECTED]> wrote: > > Another is because I typically do my web application programming in PHP5 > but the offline scripts in Perl. Both can call the same stored > procedures so I don't have multiple copies of database c

Re: [GENERAL] Blobs in Postgresql

On Wednesday 2007-08-15 05:52, Gregory Stark wrote: > "Ron Olson" <[EMAIL PROTECTED]> writes: > > Hi all- > > > > I am evaluating databases for use in a large project that will hold image > > data as blobs. I know, everybody says to just store pointers to files on > > the disk... > > Well not every

[GENERAL] Re: Best practice for: ERROR: invalid byte sequence for encoding "UTF8"

Phoenix Kiula wrote: I am not advocating what others should do. But I know what I need my DB to do. If I want it to store data that does not match puritanical standards of textual storage, then it should allow me to... Instead you want it to store tyrannically-chosen alternatives to the user's

Re: [GENERAL] Persistent connections in PHP

Hannes Dorbath schrieb: On 14.08.2007 23:13, Dmitry Koterov wrote: Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on ea

[GENERAL] Help creating a function

Hi all, I'm using ulogd with PostgreSQL which stores IP addresses as 32bit unsigned integers. So when I select some data I get something like: ulogd=> SELECT id, ip_saddr, ip_daddr, raw_pktlen, ip_totlen, tcp_window FROM ulog LIMIT 20; id | ip_saddr | ip_daddr | raw_pktlen | ip_totlen

[GENERAL] Transaction auto-abort causes grief with Spring Framework

Hi, I know this issue has been discussed at length before, but postgresql's behaviour of forcing a rollback when any error occurs is making life very difficult for me. We use Spring's transaction proxies, which are applied to methods in web controllers. In the backend code, if a runtime excep

[GENERAL] Some frustrations with admin tasks on PGSQL database

I'm loving the fact that while I am doing some one-time updates to the DB, users can still SELECT away to glory. This is a major boon in comparison to my experience with another major opensource database. However, I am a little frustrated by the amount of time PGSQL takes to complete tasks. Just t

[GENERAL] Help creating a function

Note: This is being sent again (in case it shows up later). It never seemed to have made it to the list. Hi all, I'm using ulogd with PostgreSQL which stores IP addresses as 32bit unsigned integers. So when I select some data I get something like: ulogd=> SELECT id, ip_saddr, ip_daddr, raw_p

[GENERAL] Help with this query (some join stuff I think)

I've got a bunch of companies that are associated with several videos. The videos have different statuses. I want to select all the companies in the database, and order them by videos that have a complete status. Here's what I have so far SELECT companies.id, companies.name, companies.nic

Re: [GENERAL] Writing most code in Stored Procedures

Rohit wrote: (4) Is it faster to work at application level or at the database level? Richard Huxton wrote: Probably faster in the database, assuming you have only one machine. If you have more than one machine then you can have each machine designed for its purpose. Of course, faster to run m

[GENERAL] How can I avoid PGPool as a single point of failure?

My application currently has a single PG 8.2 database server, and I'm bringing more boxes online to mitigate the risk of a single point of failure. I'm interested in using PGPool to do the load balancing, and it was suggested that I put one server running PGPool in front of two database servers. T

Re: [GENERAL] Confusing performance of specific query

On Aug 9, 10:47 pm, [EMAIL PROTECTED] (Tom Lane) wrote: > Do you have comparable work_mem settings on both machines? Another > thing to look at, if any of the sort key columns are textual, is whether > the lc_collate settings are the same. work_mem is commented out in both postgresql.conf files:

Re: [GENERAL] SQL question: checking all required items

? Try Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) Or something like that. That's the idea. Probe it and tell us. (May be the sintaxis it's not corr

[GENERAL] Non-superuser creating a flat file

I have data that is being updated in a table that I need to export to a flat file via a database trigger on insert or update. The user performing the update will not be a superuser. I've tried to use COPY TO, but that doesn't work for non-superusers. Is there some other functionality that can write

[GENERAL] Postgres : Close cursor / PerformPortalClose

Hello, I have a question about the PerformPortalClose function associated with the SQL command "Close Cursor". Sorry if you have already answered it but I didn't find the answer : - With releases < 7.4, the function listed above produced only a warning message when the cursor to be closed didn

Re: [GENERAL] non superuser creating flat files

Tom, Thank you very much, that works now. Terri Reid 01925 732359 -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 13 August 2007 15:50 To: Terri Reid Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] non superuser creating flat files "Terri Reid" <[EMAIL PRO

Re: [GENERAL] Are these two creation commands functionally identical?

On Aug 11, 5:21 am, [EMAIL PROTECTED] (Ragnar) wrote: > no these 2 are not fuctionally identical, because the second one > does not have a NOT NULL constraint on the foreign keys, > allowing you to insert: > INSERT INTO sales (saleid,userid,parent_saleid) > VALUES (100,null,100); > OK thank yo

[GENERAL] Problem Designing Index

Hello, I'm doing some select statements on my table that look like: SELECT * FROM table WHERE prod_num = '1234567' AND transaction_timestamp > '2007-07-18 21:29:57' OR prod_num > '1234567' ORDER BY prod_num ASC, transaction_timestamp ASC LIMIT 1; I've added two indices one for prod_num and anoth

Re: [GENERAL] DML value format

On Aug 10, 6:56 am, [EMAIL PROTECTED] (Alejandro Torras) wrote: > -- English -- > Hi, > > Is there some way to put values in a INSERT statement > without taking care of apostrophes? > > In example: > INSERT INTO persons VALUES ('Harry', 'O'Callaghan'); > ^^

[GENERAL] Windows XP installation problem

Hi! I downloaded the postgresql-8.2.4-1.zip and install it to win xp service pack 2 but i'm encountering this error: -- This installation package cannot be opened. Verify that the packag

Re: [GENERAL] why it doesn't work? referential integrity

On Saturday 11 August 2007 12:28:45 Pavel Stehule wrote: > Hello > > I found strange postgresql's behave. Can somebody explain it? > > Regards > Pavel Stehule > > CREATE TABLE users ( > id integer NOT NULL, > name VARCHAR NOT NULL, > PRIMARY KEY (id) > ); > > INSERT INTO users VALUES (1, 'Joz

Re: [GENERAL] serial grows on failed requests

rihad <[EMAIL PROTECTED]> schrieb: > When I do an insert that fails (like FK inconsistency, illegal value, etc.) > the users.id grows nonetheless... This is unacceptable for my current normal behavior. > needs. Any way to prevent that while still maintaining ease of use? Using > PostgreSQL 8.

[GENERAL] serial grows on failed requests

Hi, my table is defined as: CREATE TABLE users ( id integer NOT NULL, ... ); CREATE SEQUENCE users_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER SEQUENCE users_id_seq OWNED BY users.id; Although it's just a more verbose way to say create table users (id serial

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Webb Sprague wrote: > I am not sure how you can insert into a log even with savepoints, > unless you put the logging statement first and then follow it with the > insert. and delete it after success? Alternatively you could use one connection for your normal queries, and another for auditing. You

[GENERAL] Postmaster does not shut down

A 'bad' thing happened yesterday. Postgresql 8.1.X FreeBSD 6.0 At some point in the day, ran out of space on the root filesystem. (db is elsewhere) Took about 10 minutes to clear enough space to make processes stop freaking out and to slow my heart-rate down to below 200 beats per minute. Everyt

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Tyson Lloyd Thwaites wrote: > I am not opposed to introducing checkpoints to our API, but it would be > nicer if I didn't have to. At the moment I have resigned myself to > turning off spring declarative txns for certain methods, and handling > them manually by doing multiple txn blocks. In the abo

Re: [GENERAL] PostgreSQL clustering (shared disk)

On 8/17/07, Hannes Dorbath <[EMAIL PROTECTED]> wrote: > > On 17.08.2007 11:12, Mikko Partio wrote: > > Maybe I'm just better off using the more simple (crude?) method of drbd > + > > heartbeat? > > Crude? Use what you like to use, but you should keep one thing in mind: > If you don't know the softw

Re: [GENERAL] PostgreSQL clustering (shared disk)

On 17.08.2007 11:12, Mikko Partio wrote: Maybe I'm just better off using the more simple (crude?) method of drbd + heartbeat? Crude? Use what you like to use, but you should keep one thing in mind: If you don't know the software you are running in each and every detail, how it behaves in each

Re: [GENERAL] PostgreSQL clustering (shared disk)

On 8/16/07, Douglas McNaught <[EMAIL PROTECTED]> wrote: > > Devrim GÜNDÜZ <[EMAIL PROTECTED]> writes: > > >> What I'm pondering here is that is the cluster able to keep the > >> postmasters synchronized at all times so that the database won't get > >> corrupted. > > > > Keep all the $PGDATA in the

  1   2   >