Re: [GENERAL] generating a parent/child relationship in a trigger

As long as your input is well-defined, you could certainly do something like this in a trigger. Instinctively, I would think that plperl might be a little more wieldy than plpgsql for the required parsing, but I'm pretty sure a plpgsql solution could be created. Don't have one handy, though...

Re: [GENERAL] UNION messing up sorting WAS: psql performance

On Fri, Apr 15, 2005 at 12:23:14AM -0400, Joseph Shraibman wrote: > > > Alvaro Herrera wrote: > > >>Incidentally when I did that I only got back one row. What's up with > >>that? > > > > > >Try with "union all" instead of plain union. > > > Talk about serendipity. The problem I've been strugg

Re: [GENERAL] psql performance

On Thu, Apr 14, 2005 at 11:05:37PM -0400, Joseph Shraibman wrote: > Incidentally when I did that I only got back one row. What's up with that? That's PostgreSQL acting according to ANSI SQL. If you want multiple rows, you need UNION ALL. -- Jim C. Nasby, Database Consultant [EMAIL

Re: [GENERAL] psql performance

Uwe C. Schroeder wrote: Don't see a problem pasting this one. Neither to a local nor to a remote ssh (running psql certainly). This is 7.4.7 on redhat and mandrake linux'es I'd suspect it has nothing to do with psql. Can you paste that into a normal ssh / terminal ? It is slow just pasting to the

Re: [GENERAL] psql performance

Joseph Shraibman wrote: So the question what is the difference between konsole and xterm that is causing cpu to be eating up on the server? Scratch that. I wasn't using the same input for both queries. Both of them are slow. I discovered that adding newlines to the query speeds things up a lo

Re: [GENERAL] psql performance

I'm sshing into my redhat 8 server and running psql 7.4.7. My client is fedora core 3. With konsole terminals I have the problem but with xterm I don't. I did a diff between the output of env of both of them and got: -SSH_CLIENT=66.xxx.xxx.124 32943 22 -SSH_TTY=/dev/pts/5 +SSH_CLIENT=66.xxx.xxx.

[GENERAL] UNION messing up sorting WAS: psql performance

Alvaro Herrera wrote: Incidentally when I did that I only got back one row. What's up with that? Try with "union all" instead of plain union. Talk about serendipity. The problem I've been struggling with for the last few hours has been why my query wasn't producing sorted output even though I

Re: [GENERAL] Assigning password to the superuser

Forgot to mention: you might also want to think about setting up a separate superuser account for the stats monitor. Unlike Unix, Postgres doesn't think there can be only one superuser. The advantage of this is (a) you can give it a separate password from what you normally use, and (b) you can ch

Re: [GENERAL] psql performance

Don't see a problem pasting this one. Neither to a local nor to a remote ssh (running psql certainly). This is 7.4.7 on redhat and mandrake linux'es I'd suspect it has nothing to do with psql. Can you paste that into a normal ssh / terminal ? On Thursday 14 April 2005 20:05, Joseph Shraibman w

Re: [GENERAL] Assigning password to the superuser

Justin Hawkins <[EMAIL PROTECTED]> writes: > I'm putting together a system to monitor multiple postgresql > installations and to gain full access to all remote statistics I'd like > to connect as the super user. > To do that I'd add a single IP entry in pg_hba.conf for the monitoring > machine and

Re: [GENERAL] plpgsql default arguments

Pavel Stehule wrote: CREATE OR REPLACE FUNCTION foo(integer, integer) RETURNS integer AS $$ BEGIN RETURN $1 + $2; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION foo(integer) RETURNS integer AS $$ BEGIN RETURN foo($1,10); -- 10 is "default" value END; $$ LANGUAGE plpgsql; Note that if you

[GENERAL] Assigning password to the superuser

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm putting together a system to monitor multiple postgresql installations and to gain full access to all remote statistics I'd like to connect as the super user. To do that I'd add a single IP entry in pg_hba.conf for the monitoring machine

Re: [GENERAL] psql performance

On Thu, Apr 14, 2005 at 08:17:55PM -0700, Joshua D. Drake wrote: > Joseph Shraibman wrote: > >It doesn't matter what the query is. The problem happens before it even > >runs the query. Just try pasting select > >'aaa' union select > >'aaa

Re: [GENERAL] psql performance

On Thu, Apr 14, 2005 at 11:05:37PM -0400, Joseph Shraibman wrote: > It doesn't matter what the query is. The problem happens before it even > runs the query. This is most certainly a problem with the shell or the terminal. I have seen this effect somewhere else, but I can't reproduce it with ps

Re: [GENERAL] psql performance

Joseph Shraibman writes: > It doesn't matter what the query is. The problem happens before it even > runs the query. Hmmm ... I see different misbehavior (psql seems to lock up entirely, it doesn't slow down or eat CPU). But it's still misbehavior. "psql -n" doesn't have a problem, which says

Re: [GENERAL] psql performance

Joseph Shraibman wrote: It doesn't matter what the query is. The problem happens before it even runs the query. Just try pasting select 'aaa' union select 'aaa' union select 'aaa' union

Re: [GENERAL] psql performance

It doesn't matter what the query is. The problem happens before it even runs the query. Just try pasting select 'aaa' union select 'aaa' union select 'aaa' union select 'aa

Re: [GENERAL] psql performance

The cpu on the remote server is being used up by psql, and my shell is local. Paul Tillotson wrote: I am not sure if this is relevant, but I think it may depend on what kind of shell you are using. I use putty on windows XP, and it seemed to me that when I had the encoding set to Latin-1, it a

Re: [GENERAL] psql performance

I am not sure if this is relevant, but I think it may depend on what kind of shell you are using. I use putty on windows XP, and it seemed to me that when I had the encoding set to Latin-1, it always pasted very fast, but when I had it set to UTF8, the paste would be slow. I concluded (perha

Re: [GENERAL] psql performance

What is the query? What is the schema for the tables in the query? What is the cardinality of the tables? What does the planner say, when you do this: explain explain analyze http://www.faqs.org/docs/ppbook/r26943.htm -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

[GENERAL] psql performance

How come when I paste a large query into psql it starts off fast but then slows to a crawl eating up cpu just echoing the query back to me? I'm using psql 7.4.7 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appro

Re: [GENERAL] Catching dangling LOBs?

Vitaly Belman <[EMAIL PROTECTED]> writes: > Is there a way for me to catch these unused LOBs? contrib/vacuumlo might help, IIRC. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Catching dangling LOBs?

I have quite a large database with lobs being referenced from a few different table. While I made triggers/store procedures to unlink the lob once the item is deleted, I am afraid there might be a glitch somewhere in the code that leaving the LOBs dangling with no use (except wasting the HD). Is t

Re: [GENERAL] Help with process list II

On Thu, 14 Apr 2005, Cristian Prieto wrote: > Hello, I have a couple of bussy pgsql 8 servers running in my office, in > MySQL I use SHOW PROCESSLIST to show the actual process running in the > MySQL server (idle time, resources and other statistical data) and a > little perl app named mytop to sh

[GENERAL] pgsql config flag update: apple rendezvous renamed bonjour

hi all, fyi: "Apple's Rendezvous renamed 'Bonjour'" since it's now official, might it be time to add a conditional for: --with-rendezvous || --with-bonjour in configure? sorry if its 'old news' =) chee

Re: [GENERAL] use SAVEPOINT function

On Thu, Apr 14, 2005 at 04:10:18PM -0400, Ying Lu wrote: > I'd like to know is it possible to use "SAVEPOINT" in a function? In the > following example, I got an error when calling this function: No; exceptions in plpgsql are implemented internally using savepoints, so the function will automati

Re: [GENERAL] use SAVEPOINT function

The error msg is: SPI_execute_plan failed executing query "SAVEPOINT savepoint1": SPI_ERROR_TRANSACTION I'd like to know is it possible to use "SAVEPOINT" in a function? In the following example, I got an error when calling this function: CREATE OR REPLACE function test() returns trigger as $te

[GENERAL] use SAVEPOINT function

Greetings, I'd like to know is it possible to use "SAVEPOINT" in a function? In the following example, I got an error when calling this function: CREATE OR REPLACE function test() returns trigger as $test$ BEGIN SAVEPOINT savepoint1; ... ... COMMIT; RETURN NEW; EXCEPTION when RAI

Re: [GENERAL] Help with process list

On Thu, 14 Apr 2005, Cristian Prieto wrote: > Hello, I have a couple of bussy pgsql 8 servers running in my office, in > MySQL I use SHOW PROCESSLIST to show the actual process running in the > MySQL server (idle time, resources and other statistical data) and a > little perl app named mytop to sh

Re: [GENERAL] About ERROR: could not convert UTF-8 character 0x00e9

Hello ! I was out today ... In order to translate from heterogeneous Databases, you must have to change the client side encoding : I've done between an SCO UNIX 3.2 v4.2 using PROGRESS V6 and a Windows 2003 server using PostgreSQL 8.0. If you are using PGadmin 3 use : SET CLIENT_ENCODING TO 'xxx'

[GENERAL] Help with process list

Hello, I have a couple of bussy pgsql 8 servers running in my office, in MySQL I use SHOW PROCESSLIST to show the actual process running in the MySQL server (idle time, resources and other statistical data) and a little perl app named mytop to show the data like the top unix utility... There such w

[GENERAL] Some Advice needed with historical data

Hi, I need to implement a solution where I need to save changes of a record and was wondering whats the best way to implement it. I have 2 tables, A Subscription which holds the original information and table B Changes which should log the changes. (something like this) Table A Subscript --

Re: [GENERAL] plpgsql default arguments

> I am running postgres on a Linux system and have been using plpgsql to > write functions. Is there a way to specify default arguments? Hello, there is no possibility define default arguments. But you can write functions with different count of arguments. CREATE OR REPLACE FUNCTION foo(int

Re: [GENERAL] pgpool with PostgreSQL 7.4

Thanks for the tip.  The client_encoding and lc_messages values were different on the two databases.  I fixed both settings, and it works now!   KevinTatsuo Ishii <[EMAIL PROTECTED]> wrote: What are client_encoding values on these two servers? It seems they donot match...--Tatsuo Ishii> Sure, here

[GENERAL] plpgsql default arguments

Hello- I am running postgres on a Linux system and have been using plpgsql to write functions. Is there a way to specify default arguments? For example, I would like to offer the user to specify a date to use in my function but if no date is specified I would like to use the current date. Ea

[GENERAL] File execution

Title: RE: [GENERAL] Convert Cursor to array Hi,   Is anyone can say me if it's possible to execute a sql file by the console of pgadmin ?   I want to do a system similar to SQL. For example i have a file with the creation of table and i want to execute like this "@createTable" .   Thanks.

[GENERAL] (S)RPMs for PostgreSQL 8.0.2 are ready.

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - - PostgreSQL New RPM Sets 2005-04-14 Version: 8.0.2 Set labels: 8.0.2-1PGDG - - - -

Re: [GENERAL] grant all privileges to all tables in a database

Hugo <[EMAIL PROTECTED]> writes: > is it possible to have nested groups in postgres Not at the moment. There are plans to have 'em for 8.1, though I can't promise for sure that it will get done in time. regards, tom lane ---(end of broadcast)-

[GENERAL] Postgresql RHEL RPMs

I'm trying to install the 8.0.2-1 RPMs for RedHat EL3, but on trying to install postgresql-libs-8.0.2-1PGDG I get the following error error: Failed dependencies: libecpg.so.4 is needed by postgresql-libs-8.0.2-1PGDG libpgtypes.so.1 is needed by postgresql-libs-8.0.2-1PGDG

Re: [GENERAL] pgpool with PostgreSQL 7.4

What are client_encoding values on these two servers? It seems they do not match... -- Tatsuo Ishii > Sure, here is the debug output when I run the following command.. > > -bash-2.05b$ psql -p -c 'show pool_status' template1 > psql: server closed the connection unexpectedly > This p

Re: [GENERAL] pgpool with PostgreSQL 7.4

Sure, here is the debug output when I run the following command..   -bash-2.05b$ psql -p -c 'show pool_status' template1psql: server closed the connection unexpectedly    This probably means the server terminated abnormally    before or while processing the request.   Debug output from

Re: [GENERAL] pgpool with PostgreSQL 7.4

Can you show me debug messages by starting pgpool with -d option? -- Tatsuo Ishii > Hello, > > I'm having trouble getting pgpool to work in master/slave mode. I'm planning > on using something like Slony-I for replication, and use pgpool only to load > balance the queries across the master an

[GENERAL] pgpool with PostgreSQL 7.4

Hello,   I'm having trouble getting pgpool to work in master/slave mode.  I'm planning on using something like Slony-I for replication, and use pgpool only to load balance the queries across the master and slave, as well as provide failover if the master goes down.   I configured the settings so r

Re: [GENERAL] grant all privileges to all tables in a database

is it possible to have nested groups in postgres like in Adaptive server anywhare , I couldn't find anything about it in the help thanks Hugo ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscr

Re: [GENERAL] psql vs perl prepared inserts

Just to add some clarity to the Dawid's fine comments: On 4/14/05, Dawid Kuroczko <[EMAIL PROTECTED]> wrote: > On 4/13/05, Matt Van Mater <[EMAIL PROTECTED]> wrote: > I believe that COPY handles things like unique constraints and > referer integerity just fine (of the latter I am not sure) -- well

Re: [GENERAL] Checking cursor's state in plpgsql

thankz :-) On 4/10/05, Bruce Momjian wrote: > Sibtay Abbas wrote: > > Hello all > > > > Can we check whether a cursor is OPEN or not in plpgsql. > > Like in oracle you can check a CURSOR'S state with %ISOPEN > > > No, but we do have a TODO item: > > o Allow pooled connections to list all

Re: [GENERAL] PostgreSQL 8.0.2 Now Available

Marc G. Fournier wrote: > For those already running 8.x on your production servers, please note > that this upgrade does *NOT* require a dump restore, but due to a > bump in the major version number for the client library (libpq), it > *WILL* require all client applications to be recompiled at the

Re: [GENERAL] psql vs perl prepared inserts

On 4/13/05, Matt Van Mater <[EMAIL PROTECTED]> wrote: > > The intent of prepared statements is to reduce the overhead of running > > the parser, rewriter and planner multiple times for a statement that is > > executed multiple times. For an INSERT query without any sub-selects > > that is not rewri

Re: [GENERAL] Encoding Issue with UNICODE

On Tue, Apr 12, 2005 at 03:39:45AM -0700, Fritz Bayer <[EMAIL PROTECTED]> wrote a message of 53 lines which said: > I mean unicode itself is 16 bit long. This is completely false. Unicode itself is just a table and, since it contains more than 100,000 characters, you cannot index them with 16

[GENERAL] Can't install pltcl on Windows 8.0

Originally posted in win32-hackers, but could not get a solution. Maybe someone here has an idea? I am currently trying to install pltcl on my Win32 8.0 version of PG, and am receiving the incredibly generic message: createlang: language installation failed: ERROR: could not load library "C:/Pro g