Re: [GENERAL] multi line text data/query ?bug?
The difference between a Tab and a newline is that tab is a universally recognized single ascii character while newline is in flux. Aside from this, a tab is a quasi-viewable character as the cursor will not go to the middle of the tab. Meaning if the tab takes up the space of 10 characters, you could not scroll to the place where the 5th character would be if it were in fact 10 spaces. You cannot highlight half of a tab in editors that allow text highlighting. I would therefore say that a tab is as visible as a space and can be easily differentiated. On the other hand, it is impossible to determine which binary charcters the editor stuck in at the end of a newline without looking at the binary/hex code. I understand the complexity of dealing with multiple operating systems, but seriously, how many non-viewable characters can be embedded in text that actually make a difference between operating systems? Are there any besides newline? Sim "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sim Zacks" <[EMAIL PROTECTED]> writes: > > A query written on any client should return the same result. The query being > > the visible appearance on the screen. > > This is presupposing the answer to the question at hand. I do not agree > with the above premise; it would seem to imply, for example, expanding > tabs to spaces so that "where foo = ''" yields the same result as > "where foo = ' '" for some appropriate number of spaces. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing constraints to deferrable
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Wed, Mar 23, 2005 at 12:13:33PM -0500, Greg Stark wrote: > > > > Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :) > > Shouldn't ALTER TABLE DROP CONSTRAINT followed by ALTER TABLE ADD > CONSTRAINT work? It does for me in simple tests. It's a little > more work than a single ALTER TABLE ALTER CONSTRAINT would be, but > it's less hackish than updating the system catalogs directly. Or > am I missing something? But I want to do *all* constraints. If I tried to do that manually for hundreds of constraints I'm certain to get at least some of them wrong. It would also take a long time to readd all those constraints. And there's really no reason to have to recheck a constraint to make it deferrable. Similarly, there's no reason to have to recheck a constraint to change its behaviour ON DELETE and ON UPDATE. There could be some tricky bits around making a deferrable constraint not deferrable. And disabling a constraint would be nice too, reenabling it would require rechecking but at least it would eliminate the error-prone manual process of reentering the definition. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Simple query takes a long time on win2K
Thank you all for your input, and thanks about the tip on ethereal - cool program. Here's what I've found out from today's testing: The total size of the recordset that is being served up is about 500kb. When serving across a network, the time to deliver the records to the client is largely dependent on the hardware doing the serving (as expected) and regardless of which OS (Win 2K pro or XP pro). The thing that really threw me off, and still does is when the same data is served up locally. In this case, all test win2K machines (Celeron 400 up to pIII 800) retrieved the data from disk in under 100ms but took an additional 4000ms to send to the local client. This is observed even if QoS packet scheduler is installed. By contrast, Win XP serves up locally nice and fast, even when installed on exactly the same hardware that was used for the win2K test. Thanks to all for your help and insight on this one, although I'm still puzzled by the behaviour on the win2K boxes serving locally. Cheers. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 12:04 PM To: Tom Lane; A. Mous Cc: Richard Huxton; pgsql-general@postgresql.org; Joshua D. Drake; Lincoln Yeoh; [EMAIL PROTECTED] Subject: RE: [GENERAL] Simple query takes a long time on win2K > > The confusing thing for me is that so far, the only > consistent pattern > > here is that machines running win2k Pro take roughly 4 seconds to > > deliver the data to the client, while win XP machines perform much > > better (<200ms to deliver recordset). I've tried installing QoS > > packet scheduler on win2K pro machines to no avail. > > I really thought the QoS thing would be it. Maybe there was > some other thing to do, configuration-wise, to make that do > its thing properly? > > If you could reproduce this behavior across the network I > would tell you to get out a packet analyzer (ethereal or some > such) and sniff the traffic to learn more. Are there any > programs that can sniff local TCP traffic on win2k? Microsoft ships a network monitor with Win2k Server. Otherwise, just get ethereal, it works perfectly on win2k. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Another help needed on Window client
Vernon <[EMAIL PROTECTED]> writes: > C:\Program Files\PostgreSQL\8.0>runas /user:user01 > cmd.exe > Enter the password for user01: > Attempting to start cmd.exe as user "\user01" ... > A new command prompt window is popup. In the new > command prompt window, I have the following: > > C:\PROGRA~1\PostgreSQL\8.0>bin\psql template1 > Password: > psql: FATAL: password authentication failed for user > "user01" > > Here I type into the same password as in the previous > command and that is the password used during the > installation. It sounds like you are assuming that the Windows system password for user01 is the same as the Postgres password for user01. This is not necessarily true. You might want to go in and directly do an ALTER USER WITH PASSWORD command to be sure you have the right Postgres password set. (If you can't get in to do that, temporarily set auth method "trust" instead of "md5" in the pg_hba.conf file.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Another help needed on Window client
You need to set your pg_hba.conf file to allow trusted authentication for 127.0.0.1 i.e. hostall all 127.0.0.1/32 trust Vernon wrote: Here is my situation: C:\Program Files\PostgreSQL\8.0>runas /user:user01 cmd.exe Enter the password for user01: Attempting to start cmd.exe as user "\user01" ... ~~~ A new command prompt window is popup. In the new command prompt window, I have the following: C:\PROGRA~1\PostgreSQL\8.0>bin\psql template1 Password: psql: FATAL: password authentication failed for user "user01" Here I type into the same password as in the previous command and that is the password used during the installation. C:\PROGRA~1\PostgreSQL\8.0>bin\createdb mydb Password: createdb: could not connect to database template1: FATAL: password authentication failed for user "user01" I have tried to find out any document on the subject (that is the Window client) without success. I am wondering whether a file contains the readable authentication information or not. Thanks, Vernon __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Another help needed on Window client
Here is my situation: C:\Program Files\PostgreSQL\8.0>runas /user:user01 cmd.exe Enter the password for user01: Attempting to start cmd.exe as user "\user01" ... ~~~ A new command prompt window is popup. In the new command prompt window, I have the following: C:\PROGRA~1\PostgreSQL\8.0>bin\psql template1 Password: psql: FATAL: password authentication failed for user "user01" Here I type into the same password as in the previous command and that is the password used during the installation. C:\PROGRA~1\PostgreSQL\8.0>bin\createdb mydb Password: createdb: could not connect to database template1: FATAL: password authentication failed for user "user01" I have tried to find out any document on the subject (that is the Window client) without success. I am wondering whether a file contains the readable authentication information or not. Thanks, Vernon __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plperl doesn't release memory
Sven Willenberger <[EMAIL PROTECTED]> writes:
> I have been experiencing an issue with plperl and PostgreSQL 8.0.1 in
> that after calling a plperl function memory does not get released.
AFAICT the result of spi_exec_query gets released fine, as soon as it's
no longer referenced within perl. Perhaps your perl code is written in
such a way that a reference to the hash result value remains live after
the function exit?
I tried this:
create or replace function nrows(text) returns int as $$
my ($tabname) = @_;
my $rv = spi_exec_query("select * from $tabname");
return $rv->{processed};
$$ LANGUAGE plperl;
and ran it repeatedly against a large table. The memory usage went
up as expected, but back down again as soon as the function exited.
If you think it's actually a plperl bug, please show a self-contained
example.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] inherited table and rules
On Mar 23, 2005, at 2:42 PM, Klint Gore wrote: Rows inserted into inherited tables are visible to the parent. It's effectively the same as having a union all on the 2 tables. Using the only qualifier is how you stop the "union" happening. This explains it. Thanks! Scott ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] plperl doesn't release memory
I have been experiencing an issue with plperl and PostgreSQL 8.0.1 in
that after calling a plperl function memory does not get released. Two
different systems and each show different symptoms:
1) system: FreeBSD 5.3-Stable i386 with 1 GB RAM, dual Xeon P4
processors.
script: plperl issues an SPI_EXEC_QUERY('select rows from table where
condition');
this loads the result set into memory (to the tune of some 600MB based
on top output). The function iterates through each row to grab some
totals information and spits back a number.
On the 2nd iteration of this function the connection is lost :
Out of memory during request for 1012 bytes, total sbrk() is 291207168
bytes!
Callback called exit.
LOG: server process (PID 12672) exited with exit code 12
LOG: terminating any other active server processes
LOG: received immediate shutdown request
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2005-03-23 17:17:23 EST
LOG: checkpoint record is at 2/4D7F206C
LOG: redo record is at 2/4D7F206C; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 4913594; next OID: 60798748
LOG: database system was not properly shut down; automatic recovery in
progress
2) system: FreeBSD 5.3-Stable amd64 with 8GB RAM, dual opteron
processors
script: plperl issues an SPI_EXEC_QUERY('select rows from table where
condition') which fetches roughly 18k tuples of rather small size. Each
row is acted up and if criteria are met, a reference to the row is
pushed onto an array (reference).
after several iterations of this script (a dozen or so), a file is
COPYed into the database consisting of some 38k rows and each row is
acted upon by a trigger (plpgsql) -- this process normally takes just
under a minute, but after running the plperl function a dozen or so
times, the run time for the COPY exceeds 3 minutes. Restarting the
PostgreSQL backend (restart) brings the COPY time back down to
sub-minute range.
Is it normal for plperl to *not* release any memory? Or perhaps plperl
is not pfreeing or SPI_FINISHing cleanly?
Sven Willenberger
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] inherited table and rules
On Wed, 23 Mar 2005 11:48:46 -0800, Scott Frankel <[EMAIL PROTECTED]> wrote: > > Close. Thanks for the very helpful suggestions! > > As I read the doco on rules and dissect the rule I've constructed, one > issue > remains: the UPDATE in my rule causes additional rows to be added to > the parent table. How is that possible? How can it be suppressed? Rows inserted into inherited tables are visible to the parent. It's effectively the same as having a union all on the 2 tables. Using the only qualifier is how you stop the "union" happening. > Here's what my sample code (below) yields: > > cs_test=# SELECT * FROM people; you need to put the only on this query. Do you really want inheritance or do you just need an table with the same/similar structure? Maybe people_history should use like instead of inherits. klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgres oracle emulation question
On Wed, 23 Mar 2005 12:30:29 -0800, Dann Corbit <[EMAIL PROTECTED]> wrote: > 1. Excellent stability Not in my experience. > 2. Excellent scalability Well, its an 800lb gorilla, so it starts off with enough momentum. > 3. Superb toolset Used sqlplus lately? > 4. After-market support products abound I suppose, if you like spending money on that sort of thing. > 5. Stable, debugged access tools like the ODBC/OLEDB/.NET drivers > > On the downside: > 1. $$$!!! > 2. Installation is bad, even for a simple client. Actually the installation is one of the few things that isn't too painful. Sure it ain't as simple as postgres, but its miles easier than DB2. > 3. Reinstallation/upgrade is always a nightmare. > 4. God help you if your Oracle home should ever get moved or you move > to a different disk drive. > 5. Multiple OCI DLLs are sure to bring lots of intriguing surprises. No DLLs on my Linux or Solaris boxen, but i've had problems aplenty with oracle's hackjobs pretending to be shared objects. > On the whole, it's not terrible if you can afford it. For what they charge (and that's ignoring support fees) it should & could be a hell of a lot better. But this thread is a touch off topic around here. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question about function body checking and 8.1
On Tue, Mar 22, 2005 at 09:04:42PM -0600, Tony Caduto wrote: > > CREATE OR REPLACE FUNCTION mytest(); > RETURNS VOID as > $$ > DECLARE > mytestvar varchar; > mytestvar2 integer; > BEGIN > mytestvarr = 'bla'; > select testfield from nonexistanttable where testfield = 2 > INTO mytestvar2; > --The table does not exits, yet postgresql does not complain. > END; > $$ > LANGUAGE 'plpgsql' VOLATILE; This is at most a warning. Just because the table doesn't exist now doesn't mean it won't exixt when the function is run. Need to be careful here otherwise when restoring a dump you'll end up with lots of useless errors because the tables were created after the functions... Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpdlMdFbubup.pgp Description: PGP signature
Re: [GENERAL] postgres oracle emulation question
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lonni J Friedman Sent: Wednesday, March 23, 2005 6:06 AM To: Joshua D. Drake Cc: Randy Samberg; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres oracle emulation question On Tue, 22 Mar 2005 20:38:13 -0800, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Lonni J Friedman wrote: > > >On Tue, 22 Mar 2005 17:51:06 -0800, Randy Samberg > ><[EMAIL PROTECTED]> wrote: > > > > > >> > >>Does anyone know if there is a way in Postgres to emulate Oracle, in other > >>words make Postgres think it is an Oracle database? If so, do you have any > >>idea what percentage of people are doing this, and how successful they are > >>with this? Also, how is this done? Do you know of any good links that > >>discuss this. My manager is thinking about replacing a couple of Oracle > >>databases with Postgres, and would like to know the answer to this question. > >> > >> > > > >You mean make postgres slow, bloated & buggy? > > > > > I am going to assume that was a joke, but it is not helpful. No, it wasn't a joke. I'm forced to maintain Oracle databases every day, and I've yet to find any redeeming qualities, so it baffles me why anyone would want to emulate them. >> 1. Excellent stability 2. Excellent scalability 3. Superb toolset 4. After-market support products abound 5. Stable, debugged access tools like the ODBC/OLEDB/.NET drivers On the downside: 1. $$$!!! 2. Installation is bad, even for a simple client. 3. Reinstallation/upgrade is always a nightmare. 4. God help you if your Oracle home should ever get moved or you move to a different disk drive. 5. Multiple OCI DLLs are sure to bring lots of intriguing surprises. << On the whole, it's not terrible if you can afford it. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] debug_print_plan
Aha you are right. I tried this but set log min error statement instead and so *that* didn't work. Setting the right variable worked and I can now see the plan. Thanks!! --elein On Wed, Mar 23, 2005 at 01:05:30PM -0700, Michael Fuhr wrote: > On Wed, Mar 23, 2005 at 11:38:21AM -0800, elein wrote: > > > > I cannot get debug_print_plan to print the query plan to the > > log. I have set it in the postgresql.conf file and bounced > > the server. The output is simply not there. show shows > > that the value is set, but no output. > > What's your log_min_messages setting? According to the documentation, > debug_print_* needs DEBUG1 or lower. > > http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-LOGGING-WHAT > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] debug_print_plan
[EMAIL PROTECTED] (elein) writes: > I cannot get debug_print_plan to print the query plan to the > log. /* * Print plan if debugging. */ if (Debug_print_plan) elog_node_display(DEBUG1, "plan", plan, Debug_pretty_print); Looks like you also need to have server_min_messages <= DEBUG1. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Changing constraints to deferrable
On Wed, Mar 23, 2005 at 12:13:33PM -0500, Greg Stark wrote: > > Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :) Shouldn't ALTER TABLE DROP CONSTRAINT followed by ALTER TABLE ADD CONSTRAINT work? It does for me in simple tests. It's a little more work than a single ALTER TABLE ALTER CONSTRAINT would be, but it's less hackish than updating the system catalogs directly. Or am I missing something? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] inherited table and rules
try select * from ONLY people.
also check out this query
select relname,people.* from people join pg_class on
people.tableoid=pg_class.oid;
and
select relname,people.* from ONLY people join pg_class on
people.tableoid=pg_class.oid;
Jim
-- Original Message ---
From: Scott Frankel <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Wed, 23 Mar 2005 11:48:46 -0800
Subject: Re: [GENERAL] inherited table and rules
> Close. Thanks for the very helpful suggestions!
>
> As I read the doco on rules and dissect the rule I've constructed, one
> issue
> remains: the UPDATE in my rule causes additional rows to be added to
> the parent table. How is that possible? How can it be suppressed?
>
> i.e.: My rule specifies that when the parent table is updated, the
> inherited table
> receives an INSERT. There is nothing that I see that explicitly calls
> for a new
> row to be added to the parent table.
>
> I've tried fiddling with INSTEAD; but my attempts haven't yielded the
> results
> I'm looking for. (Though the rule docs are quite opaque on the subect
> ...)
>
> Thanks again!
> Scott
>
> Here's what my sample code (below) yields:
>
> cs_test=# SELECT * FROM people;
> usr_pkey | usr_name | color | timestamp
> --+--+-+
> 2 | carol| green | 2005-03-23 11:12:49.627183
> 3 | ted | blue| 2005-03-23 11:12:49.637483
> 1 | bob | black | 2005-03-23 11:12:49.616602
> 1 | bob | red | 2005-03-23 11:12:49.616602
> 1 | bob | cyan| 2005-03-23 11:12:49.616602
> 1 | bob | magenta | 2005-03-23 11:12:49.616602
> 1 | bob | yellow | 2005-03-23 11:12:49.616602
> (7 rows)
>
> cs_test=# SELECT * FROM people_history;
> usr_pkey | usr_name | color | timestamp | hist_pkey
> |hist_tstamp
> --+--+-+
> +---+
> 1 | bob | red | 2005-03-23 11:12:49.616602 | 1
> | 2005-03-23 11:13:17.04928
> 1 | bob | cyan| 2005-03-23 11:12:49.616602 | 2
> | 2005-03-23 11:22:21.374629
> 1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
> | 2005-03-23 11:23:49.253014
> 1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
> | 2005-03-23 11:23:53.924315
> (4 rows)
>
> Here's what I'm looking for:
>
> cs_test=# SELECT * FROM people;
> usr_pkey | usr_name | color | timestamp
> --+--+-+
> 2 | carol| green | 2005-03-23 11:12:49.627183
> 3 | ted | blue| 2005-03-23 11:12:49.637483
> 1 | bob | black | 2005-03-23 11:12:49.616602
> (3 rows)
>
> cs_test=# SELECT * FROM people_history;
> usr_pkey | usr_name | color | timestamp | hist_pkey
> |hist_tstamp
> --+--+-+
> +---+
> 1 | bob | red | 2005-03-23 11:12:49.616602 | 1
> | 2005-03-23 11:13:17.04928
> 1 | bob | cyan| 2005-03-23 11:12:49.616602 | 2
> | 2005-03-23 11:22:21.374629
> 1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
> | 2005-03-23 11:23:49.253014
> 1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
> | 2005-03-23 11:23:53.924315
> (4 rows)
>
> sample code:
>
> CREATE TABLE people (
> usr_pkey SERIALPRIMARY KEY,
> usr_name text UNIQUE DEFAULT NULL,
> colortext DEFAULT NULL,
> timestamptimestamp DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE TABLE people_history (
> hist_pkeySERIALNOT NULL PRIMARY KEY,
> hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> ) INHERITS (people);
>
> CREATE RULE
> people_upd_history AS ON UPDATE TO people
> DO INSERT INTO
> people_history
> SELECT * FROM ONLY people WHERE usr_pkey = old.usr_pkey;
>
> -- populate table
> INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
> INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
> INSERT INTO people (usr_name, color) VALUES ('ted', 'blue');
>
> -- update table (1)
> UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1;
>
> -- update table (2)
> UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1;
>
> -- update table (3)
> UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1;
>
> -- update table (4)
> UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1;
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
--- End of Original Message ---
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] postgresql max memory (pl/Perl)
Title: postgresql max memory (pl/Perl) hi in my application i select data from my database and i store it into a pl/perl variable $myClob. the quantity of data is very big (around 250Mo, growing to 800Mo). and next i write this data into a file. with Oracle, it works, but with Oracle i get an exception saying that postgresql use a memory adress which is not allowed (or not in write mode). my database is install on windows2000NT, with PIV 2.8Ghz and 1Go RAM. does this error comes from the configuration of my database, the limits of postgresql, or does i must write the file in several times ? an other question is that the application will run on others plateform : does i will get an error with a SUN station : biprocessor, 4Go RAM and solaris 2.8 ? regards Will
Re: [GENERAL] debug_print_plan
On Wed, Mar 23, 2005 at 11:38:21AM -0800, elein wrote: > > I cannot get debug_print_plan to print the query plan to the > log. I have set it in the postgresql.conf file and bounced > the server. The output is simply not there. show shows > that the value is set, but no output. What's your log_min_messages setting? According to the documentation, debug_print_* needs DEBUG1 or lower. http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-LOGGING-WHAT -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] tsearch2 installation question
I’m trying to install the version of tsearch2 distributed with pg 8.0.1. “make” and “make install” runs with no apparent problems, but “make installcheck” fails. Looking at the diffs, I would guess that the differences are inconsequential. But I would like to be sure before proceeding. Is this a known issue, or do I really have a problem? If I really have a problem, any suggestions? output of “diff results/tsearch2.out expected/tsearch2.out” 2092,2093c2092,2093 < headline < --- > headline > --- 2109,2110c2109,2110 < headline < -- --- > headline > -- 2126,2127c2126,2127 < headline < -- --- > headline > --- 2146,2147c2146,2147 < headline < -- --- > headline > -
Re: [GENERAL] Simple query takes a long time on win2K
OK, I've taken the PII 233 that had win2K pro on it, and installed winXP home. Reloaded psql 8.0.1 and the database and ran the exact same query. Recall with win2K it took 4000ms to get the data to the client (about 1500 rows) and on XP it takes 290ms the first time, and about 250ms every time thereafter. Results of EXPLAIN ANALYZE SELECT * FROM foo are: Seq Scan on foo (cost=0.00..40.72 rows=1472 width=134) (actual time=0.000..50.000 rows=1472 loops=1) By the way, this table only has 7 columns...why is the width described as 134? I've tried installing the QoS packet scheduler with win2K pro and it did absolutely nothing. I will now download ethereal and install on one of the other win2K pro machines that has high latency, however, what exactly am I looking for? You know, among the win2K machines that I've tested so far, if the only slow ones were the pII 233 and the Celeron 400 I could accept the fact that it's ancient hardware. But the pIII800 was also dead slow to return the data to the client app, and this most recent test clearly shows that the OS is the problem. Ug. I'd greatly appreciate any input on this! Thanks. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 12:04 PM To: Tom Lane; A. Mous Cc: Richard Huxton; pgsql-general@postgresql.org; Joshua D. Drake; Lincoln Yeoh; [EMAIL PROTECTED] Subject: RE: [GENERAL] Simple query takes a long time on win2K > > The confusing thing for me is that so far, the only > consistent pattern > > here is that machines running win2k Pro take roughly 4 seconds to > > deliver the data to the client, while win XP machines perform much > > better (<200ms to deliver recordset). I've tried installing QoS > > packet scheduler on win2K pro machines to no avail. > > I really thought the QoS thing would be it. Maybe there was > some other thing to do, configuration-wise, to make that do > its thing properly? > > If you could reproduce this behavior across the network I > would tell you to get out a packet analyzer (ethereal or some > such) and sniff the traffic to learn more. Are there any > programs that can sniff local TCP traffic on win2k? Microsoft ships a network monitor with Win2k Server. Otherwise, just get ethereal, it works perfectly on win2k. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] inherited table and rules
Close. Thanks for the very helpful suggestions!
As I read the doco on rules and dissect the rule I've constructed, one
issue
remains: the UPDATE in my rule causes additional rows to be added to
the parent table. How is that possible? How can it be suppressed?
i.e.: My rule specifies that when the parent table is updated, the
inherited table
receives an INSERT. There is nothing that I see that explicitly calls
for a new
row to be added to the parent table.
I've tried fiddling with INSTEAD; but my attempts haven't yielded the
results
I'm looking for. (Though the rule docs are quite opaque on the subect
...)
Thanks again!
Scott
Here's what my sample code (below) yields:
cs_test=# SELECT * FROM people;
usr_pkey | usr_name | color | timestamp
--+--+-+
2 | carol| green | 2005-03-23 11:12:49.627183
3 | ted | blue| 2005-03-23 11:12:49.637483
1 | bob | black | 2005-03-23 11:12:49.616602
1 | bob | red | 2005-03-23 11:12:49.616602
1 | bob | cyan| 2005-03-23 11:12:49.616602
1 | bob | magenta | 2005-03-23 11:12:49.616602
1 | bob | yellow | 2005-03-23 11:12:49.616602
(7 rows)
cs_test=# SELECT * FROM people_history;
usr_pkey | usr_name | color | timestamp | hist_pkey
|hist_tstamp
--+--+-+
+---+
1 | bob | red | 2005-03-23 11:12:49.616602 | 1
| 2005-03-23 11:13:17.04928
1 | bob | cyan| 2005-03-23 11:12:49.616602 | 2
| 2005-03-23 11:22:21.374629
1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
| 2005-03-23 11:23:49.253014
1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
| 2005-03-23 11:23:53.924315
(4 rows)
Here's what I'm looking for:
cs_test=# SELECT * FROM people;
usr_pkey | usr_name | color | timestamp
--+--+-+
2 | carol| green | 2005-03-23 11:12:49.627183
3 | ted | blue| 2005-03-23 11:12:49.637483
1 | bob | black | 2005-03-23 11:12:49.616602
(3 rows)
cs_test=# SELECT * FROM people_history;
usr_pkey | usr_name | color | timestamp | hist_pkey
|hist_tstamp
--+--+-+
+---+
1 | bob | red | 2005-03-23 11:12:49.616602 | 1
| 2005-03-23 11:13:17.04928
1 | bob | cyan| 2005-03-23 11:12:49.616602 | 2
| 2005-03-23 11:22:21.374629
1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
| 2005-03-23 11:23:49.253014
1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
| 2005-03-23 11:23:53.924315
(4 rows)
sample code:
CREATE TABLE people (
usr_pkey SERIALPRIMARY KEY,
usr_name text UNIQUE DEFAULT NULL,
colortext DEFAULT NULL,
timestamptimestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE people_history (
hist_pkeySERIALNOT NULL PRIMARY KEY,
hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
) INHERITS (people);
CREATE RULE
people_upd_history AS ON UPDATE TO people
DO INSERT INTO
people_history
SELECT * FROM ONLY people WHERE usr_pkey = old.usr_pkey;
-- populate table
INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
INSERT INTO people (usr_name, color) VALUES ('ted', 'blue');
-- update table (1)
UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1;
-- update table (2)
UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1;
-- update table (3)
UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1;
-- update table (4)
UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1;
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] debug_print_plan
I cannot get debug_print_plan to print the query plan to the log. I have set it in the postgresql.conf file and bounced the server. The output is simply not there. show shows that the value is set, but no output. If someone can respond Right Now I'd very much appreciate it. I cannot get onto irc from this client site. --elein ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] postgres oracle emulation question
Thanks to everyone for your comments on postgres oracle emulation. It was very helpful. Randy -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 8:38 PM To: Lonni J Friedman Cc: Randy Samberg; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres oracle emulation question Lonni J Friedman wrote: >On Tue, 22 Mar 2005 17:51:06 -0800, Randy Samberg ><[EMAIL PROTECTED]> wrote: > > >> >>Does anyone know if there is a way in Postgres to emulate Oracle, in >>other words make Postgres think it is an Oracle database? If so, do >>you have any idea what percentage of people are doing this, and how successful they are >>with this? Also, how is this done? Do you know of any good links that >>discuss this. My manager is thinking about replacing a couple of >>Oracle databases with Postgres, and would like to know the answer to this question. >> >> > >You mean make postgres slow, bloated & buggy? > > I am going to assume that was a joke, but it is not helpful. There is no real way to "emulate" Oracle. The best you could do is to have some level of software proxy that would understand the Oracle protocol and then translate that to PostgreSQL but you are looking at a huge mess. You would have to consider all the queries and such. What I can tell you is that it doesn't take much to port Oracle to PostgreSQL. There are some major differences in feature set but those can usually be solved programmatically. Sincerely, Joshua D. Drake > > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] pl/perl problem
yes, it works
exactly what i needed, thanks a lot
-Message d'origine-
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : mardi 22 mars 2005 12:41
À : FERREIRA William (COFRAMI)
Cc : 'Sean Davis'; pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem
FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
> RETURNS int4 AS
> $BODY$
> my $var = '->>>';
> &concat($var);
>
> sub concat {
> $var .= 'tagada';
> }
> elog NOTICE, $var;
> return 4;
>
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
>
> first execution : ->>>tagada
> second execution : ->>>
In the example above $var in sub concat is NOT an argument provided to
the function. What you've done there is create a named closure (if I'm
getting my terms right) in which the inner $var is allocated on first
call but not afterwards. The second time you run totoTest() the outer
$var (my $var) is a new variable, whereas the inner one still refers to
the original.
If you actually want to return a concatenated string you'd want
something like:
sub concat {
my $var = shift;
return $var . 'tagada';
}
If you want to affect an outer variable you'll want something like
sub concat {
my $var_ref = shift;
$$var_ref .= 'tagada';
}
Does that help?
--
Richard Huxton
Archonet Ltd
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] pl/perl problem
thanks a lot
with your example and the example of Richard it works fine
-Message d'origine-
De : Sean Davis [mailto:[EMAIL PROTECTED]]
Envoyé : mardi 22 mars 2005 12:51
À : FERREIRA William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem
On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same
> comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
> RETURNS int4 AS
> $BODY$
> my $var = '->>>';
> &concat($var);
>
>
> sub concat {
> $var .= 'tagada';
> }
> elog NOTICE, $var;
> return 4;
>
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
>
> first execution : ->>>tagada
> second execution : ->>>
Here is a slightly modified version of your code that does what you
want, I think. A couple of things:
1) If you want to pass arguments to a subroutine, what you do above
won't work.
2) You have to be careful in perl when you modify variables that you
know the scope of the variables (where they will be seen versus not)
that you are modifying.
3) If you want a subroutine to modify the value of a variable passed
to it, you need to pass a REFERENCE to that variable, not the value of
the variable.
CREATE OR REPLACE FUNCTION adoc.totoTest2() RETURNS int4 AS
$BODY$
use strict; #see below for explanation
my $var = '->>>';
concat(\$var); #use a reference to the variable
elog NOTICE, $var;
return 4;
sub concat {
my $ref=shift; #get a REFERENCE to the variable
${$ref} .= 'tagada'; #this dereferences the variable and modifies it
}
$BODY$
LANGUAGE 'plperl' VOLATILE;
>
> (for my second problem, i not able to reproduce iti deleted the
> source code)
> but what means 'use strict' ?
>
>
See this article
http://perl.about.com/od/perlforbeginners/l/aa081701a.htm
Sean
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
Re: [GENERAL] Simple query takes a long time on win2K
> > The confusing thing for me is that so far, the only > consistent pattern > > here is that machines running win2k Pro take roughly 4 seconds to > > deliver the data to the client, while win XP machines perform much > > better (<200ms to deliver recordset). I've tried installing QoS > > packet scheduler on win2K pro machines to no avail. > > I really thought the QoS thing would be it. Maybe there was > some other thing to do, configuration-wise, to make that do > its thing properly? > > If you could reproduce this behavior across the network I > would tell you to get out a packet analyzer (ethereal or some > such) and sniff the traffic to learn more. Are there any > programs that can sniff local TCP traffic on win2k? Microsoft ships a network monitor with Win2k Server. Otherwise, just get ethereal, it works perfectly on win2k. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [GENERAL] contrib module intagg crashing the backend
I wrote: > Grumble ... I seem to have managed to promote intagg from > broken-on-64bit-platforms to broken-on-every-platform ... > will look into a fix tomorrow. Ron's problem is essentially a double-free bug. In this patch: 2005-01-27 16:35 tgl * contrib/intagg/: int_aggregate.c, int_aggregate.sql.in (REL7_3_STABLE), int_aggregate.c, int_aggregate.sql.in (REL7_4_STABLE), int_aggregate.c, int_aggregate.sql.in (REL8_0_STABLE), int_aggregate.c, int_aggregate.sql.in: Fix security and 64-bit issues in contrib/intagg. This code could stand to be rewritten altogether, but for now just stick a finger in the dike. I modified intagg to declare its transition data type as int4[] (which is what it really is) rather than int4. Unfortunately that means that nodeAgg.c is now aware that the transition value is pass-by-reference, and so it thinks it needs to manage the memory used for it; which intagg.c is also trying to do; so they both free the same bit of memory. There is already a "proper" fix for this problem in CVS tip, but it's too invasive to consider back-patching; not least because nodeAgg's memory management strategy has changed since 7.3 and the fix would probably not work that far back. What I'm thinking I have to do is revert intagg in the back branches to lie about its transition data type, but still have it pull the pointer out of the passed Datum with DatumGetPointer (as opposed to the old, definitely 64-bit-broken method of DatumGetInt32 and then cast to pointer). This should work because nodeAgg doesn't inquire into the actual contents of any Datum it doesn't think is pass-by-reference; so it will never discard the upper bits of the pointer. Ugh. Glad we have a cleaner solution to go forward with. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simple query takes a long time on win2K
"A. Mous" <[EMAIL PROTECTED]> writes: > The confusing thing for me is that so far, the only consistent pattern here > is that machines running win2k Pro take roughly 4 seconds to deliver the > data to the client, while win XP machines perform much better (<200ms to > deliver recordset). I've tried installing QoS packet scheduler on win2K pro > machines to no avail. I really thought the QoS thing would be it. Maybe there was some other thing to do, configuration-wise, to make that do its thing properly? If you could reproduce this behavior across the network I would tell you to get out a packet analyzer (ethereal or some such) and sniff the traffic to learn more. Are there any programs that can sniff local TCP traffic on win2k? regards, tom lane PS: I've added pgsql-hackers-win32 to the cc list. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Simple query takes a long time on win2K
For the sake of further comparison, I added another computer to the
comparison. Another Celeron 2400, running win XP again, but only 256MB ram.
All records returned to client in 200ms (slightly longer than the 2400
machine with 500MB ram). Recall that the win2K box with 256MB Ram (PIII
1.8) took 4 seconds.
The confusing thing for me is that so far, the only consistent pattern here
is that machines running win2k Pro take roughly 4 seconds to deliver the
data to the client, while win XP machines perform much better (<200ms to
deliver recordset). I've tried installing QoS packet scheduler on win2K pro
machines to no avail.
For kicks, I'll repeat the tests on the PII 233 machine with WinXP
installed. Thank you Tom for pointing out that the EXPLAIN result shows the
data is being fetched in under 100ms, but why is it taking so darn long to
move that data into the client (be it psql or pgAdminIII)?
I'll let you all know how the winXP test on the PII goes. Thank you all for
your insights.
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: March 23, 2005 9:51 AM
To: A. Mous
Cc: 'Richard Huxton'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple query takes a long time on win2K
"A. Mous" <[EMAIL PROTECTED]> writes:
> None of these tests were run over a network - all local.
Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.
Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.
I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] multi line text data/query ?bug?
"Sim Zacks" <[EMAIL PROTECTED]> writes: > A query written on any client should return the same result. The query being > the visible appearance on the screen. This is presupposing the answer to the question at hand. I do not agree with the above premise; it would seem to imply, for example, expanding tabs to spaces so that "where foo = ''" yields the same result as "where foo = ' '" for some appropriate number of spaces. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] backend process
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Wed, 2005-03-23 at 10:11, Edson Vilhena de Carvalho wrote: >> active backend server processes. I would like to know >> what is a active backend server processes. > In PostgreSQL every connection spawns a new backend that operates on the > database semi-independently, cooperating with the other backends by > means of shared memory. See http://www.postgresql.org/docs/8.0/static/tutorial-arch.html http://www.postgresql.org/docs/8.0/static/connect-estab.html regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Delay INSERT
Dawid Kuroczko <[EMAIL PROTECTED]> writes: > PostgreSQL doesn't have such issues with blocking, so only difference > between INSERT and INSERT DELAYED from PostgreSQL's standpoint > would be waiting and not for the result... With the right client-side code you can transmit multiple queries before receiving the result from the first one. I don't think libpq in its current incarnation really supports this, but in principle it's doable. The interesting questions have to do with error handling: if the "delayed" insert fails, what happens and what is the impact on subsequent queries? I have no idea how MySQL defines that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql unicode lower/upper problem
Sergey Levchenko <[EMAIL PROTECTED]> writes: > I am not able to get work lower and upper functions on postgresql > v8.0.1 and 8.1b(current cvs copy). I use Debian SID i686 GNU/Linux. > Locale: ru_RU.KOI8-R > createdb -E UNICODE test I think the problem is you selected a database encoding that doesn't match what the locale expects. You can't really mix-and-match if you expect locale-specific stuff like upper/lower to work. For that locale you must use -E KOI8. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Changing constraints to deferrable
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > Greg Stark wrote: > > I want all my foreign key constraints to be deferrable. They were all > > created > > with the default (not deferrable). > > Is it enough to just do update pg_constraint set condeferrable = 't' where > > contype = 'f'; > > Try an additional "update pg_trigger set isdeferrable=true where > pgisconstraint > = true", and it should work.. Thanks. That works. Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :) -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] I'm OWNER of the db but I get `permission denied` when
Michael Fuhr wrote: On Wed, Mar 23, 2005 at 04:07:59PM +, Richard Huxton wrote: Don't forget pg_class isn't in your database, it's shared by all. Each database has its own pg_class: You're quite right Michael, I'm talking rubbish. Why is it always when I don't bother to read what I'm writing that I spout nonsense? Ho hum. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Extracting object source code from database to store in CVS...
Adrianna Pinska <[EMAIL PROTECTED]> writes: > More specifically, I've been looking for a way to persuade postgresql > to output the create script for a single object - without much > success. It seems that pg_dump can output a dump of the entire > database schema or a dump of a single table, but not of a different > kind of object like a function or view. The fact that pg_dump has restrictive switches for selecting tables but not other kinds of objects isn't fundamental; it's just that no one has gotten around to it. Perhaps your best approach in the long term is to implement such switches. If you can do that and get it accepted into the code base, then you won't have to worry about keeping your code up-to-date with future system catalog changes. Based on past history, I'd say that trying to maintain your own pg_dump subset is a losing proposition. We whack the catalogs around a lot ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple query takes a long time on win2K
Yeah, thanks. I did see that post about the QoS and it doesn't help in this
case.
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: March 23, 2005 9:51 AM
To: A. Mous
Cc: 'Richard Huxton'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple query takes a long time on win2K
"A. Mous" <[EMAIL PROTECTED]> writes:
> None of these tests were run over a network - all local.
Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.
Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.
I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Table audit system
I use a modified form of option 3 with an ON UPDATE RULE the update rule copies the row to an inherited table... CREATE TABLE dm_user ( id SERIAL NOT NULL PRIMARY KEY, lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id), dm_user_address INTEGER NOT NULL DEFAULT 0, dm_user_email INTEGER NOT NULL DEFAULT 0, f_name VARCHAR(50) NOT NULL, m_name VARCHAR(50) NOT NULL, l_name VARCHAR(50) NOT NULL, uname VARCHAR(20) NOT NULL, upwd VARCHAR(20) NOT NULL, pwd_change_reqd BOOLEAN DEFAULT FALSE, login_allowed BOOLEAN DEFAULT TRUE, lost_passwd BOOLEAN DEFAULT FALSE, create_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_id INTEGER NOT NULL DEFAULT 0, active_flag BOOLEAN NOT NULL DEFAULT TRUE ) WITH OIDS; CREATE TABLE dm_user_history ( history_id SERIAL NOT NULL PRIMARY KEY, hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW() ) INHERITS (dm_user); CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO dm_user_history SELECT * FROM dm_user WHERE id = old.id; CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE dm_user SET active_flag = FALSE WHERE id = old.id; "Scott Frankel" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Is there a canonical form that db schema designers use > to save changes to the data in their databases? > > For example, given a table with rows of data, if I UPDATE > a field in a row, the previous value is lost. If I wanted to > track the changes to my data over time, it occurs to me that > I could, > > 1) copy the whole row of data using the new value, thus > leaving the old row intact in the db for fishing expeditions, > posterity, &c. > -- awfully wasteful, especially with binary data > > 2) enter a new row that contains only new data fields, requiring > building a full set of data through heavy lifting and multiple > queries > through 'n' number of old rows > -- overly complex query design probably leading to errors > > 3) create a new table that tracks changes > -- the table is either wide enough to mirror all columns in > the working table, or uses generic columns and API tricks to > parse token pair strings, ... > > 4) other? > > Thanks > Scott > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > "josue" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello list, > > I need to define an audit system that would be easyli include or exclude > certain tables, the process is a purchase order where many users changes > the info in diferent ways, the requerimient is to log the stamp and user > of the change on a table and additionaly log a snapshot of the the order > at the time it was change, that must include any child table too, > generally the order document includes the order header main table, the > order detail child table, the order costs child table and the order > comment history child table. So given the need to log a full snapshot not > only the change of a column I ask you for ideas or suggestion to get this > properly done in Postgresql. > > Thanks in advance, > > > -- > Sinceramente, > Josué Maldonado. > > ... "Toda violación de la verdad no es solamente una especie de suicidio > del embustero, sino una puñalada en la salud de la sociedad humana." Ralph > Waldo Emerson. Filósofo, ensayista, poeta y político EE.UU. > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Delay INSERT
Dawid Kuroczko <[EMAIL PROTECTED]> writes: > PostgreSQL doesn't have such issues with blocking, so only difference > between INSERT and INSERT DELAYED from PostgreSQL's standpoint > would be waiting and not for the result... An insert can be blocked if there's a UNIQUE constraint and another transaction has an insert or update pending for the same key. If the other transaction commits you get a unique constraint violation, if it aborts your insert succeeds. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Command Prompt on Window version
> Just installed the 8.0 version for Windows. As a Unix guy, I > would like to do things through the command prompt. I have > trouble to bring up it. After bringing up the "psql to > template1" as the same user of the installation > configuration, it disappears right after I type in the password. > > How to bring up the command prompt correctly? Also, is a way > to bring it up with a different user from the user in configuration? Start->All Progams->Accessories->Command Prompt Then just do "cd":s into the pgsql directory. To bring it up as a different user use: runas /user: cmd.exe //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Question about function body checking and 8.1
Sean Davis <[EMAIL PROTECTED]> writes: > On Mar 22, 2005, at 10:04 PM, Tony Caduto wrote: >> Also if you happen to use PLperl or any of the other ones, do they >> actually do better checking than PLpgsql? Last time I used a PLperl >> function it didn't do any checking at creation either. > I think (from experience rather than knowledge) that that is still the > case as of 8.0.1. I know Tom Lane and I had a brief discussion on one > of the lists on the subject a month or two ago, but I can't seem to > find the emails. CVS-tip createlang still thinks that plpgsql is the only standard PL that has a validator procedure; therefore the others don't do any checking at CREATE FUNCTION whatsoever. It would be reasonable for someone to step up and improve this ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple query takes a long time on win2K
Queries are issued from, and time values are report in pgAdminIII. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 9:31 AM To: A. Mous; Richard Huxton Cc: pgsql-general@postgresql.org Subject: RE: [GENERAL] Simple query takes a long time on win2K > None of these tests were run over a network - all local. > Given that the hardware is very different, however, I did > find it strange that all win2k (Pro, not server) served up > the records in almost exactly the same time, while the 2.4 > celeron is 80ms! Note that the granularity of the performance counters in EXPLAIN and in psql have a very bad resolution on Win32 in 8.0.1. The EXPLAIN counters has been fixed for 8.1, not sure if it'll be backpatched to 8.0.2. What are you getting your time values from? If it's based on either of these it's entirely possible that the speed difference is completely inside the margin of error. Also, you are not likely to be very CPU-bound for a simple query like that. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] I'm OWNER of the db but I get `permission denied` when
On Wed, Mar 23, 2005 at 04:07:59PM +, Richard Huxton wrote: > Don't forget pg_class isn't in your database, it's shared by all. Each database has its own pg_class: SELECT relname, relkind, relisshared FROM pg_class WHERE relname = 'pg_class'; relname | relkind | relisshared --+-+- pg_class | r | f (1 row) You can query pg_class to see that its contents are different in different databases, and you can use "ls -li" on the on-disk files to see that they have different inode numbers and (usually) different sizes and modified times. Here are the shared objects in an 8.0.1 database (excluding indexes): SELECT relname, relkind FROM pg_class WHERE relkind <> 'i' AND relisshared IS TRUE ORDER BY relname; relname| relkind ---+- pg_database | r pg_group | r pg_shadow | r pg_tablespace | r pg_toast_1260 | t pg_toast_1261 | t pg_toast_1262 | t pg_xactlock | s (8 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Existence of tuples in relations and a multicolumn index.
If I have a relation and create a multicolumn index on all the attributes, would the index be bigger than the relation itself? would it be more efficient to keep the relation ordered on all the attribute if I have a lot of additions? I have relations that are only incremental in time and they are huge, i.e. exponential in the size of the rest of the database. The problem arises when I have to check for existence of a tuple in those type of relations. Regards, tzahi. WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple query takes a long time on win2K
"A. Mous" <[EMAIL PROTECTED]> writes:
> None of these tests were run over a network - all local.
Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.
Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.
I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Delay INSERT
Dawid Kuroczko wrote: On Wed, 23 Mar 2005 14:50:47 +, Richard Huxton wrote: ON.KG wrote: Does PostgreSQL have something like "INSERT DELAYD" - like it is used in MySQL? or any other way to delay inserting? What precisely does this do? It adds an insert into a 'do this' queue and returns. From PostgreSQL-s point of view it would be equivalent of issuing INSERT and not waiting for the result. OK - thanks. The MySQL has this mainly because when other statement such as SELECT or UPDATE is in progress, the INSERT would be blocked. PostgreSQL doesn't have such issues with blocking, so only difference between INSERT and INSERT DELAYED from PostgreSQL's standpoint would be waiting and not for the result... Well, if you don't actually care whether it got inserted or not, just throw the data away! That's got to be the quickest of all. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Command Prompt on Window version
Just installed the 8.0 version for Windows. As a Unix guy, I would like to do things through the command prompt. I have trouble to bring up it. After bringing up the "psql to template1" as the same user of the installation configuration, it disappears right after I type in the password. How to bring up the command prompt correctly? Also, is a way to bring it up with a different user from the user in configuration? Thanks, Vernon __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple query takes a long time on win2K
So, does this lend evidence to the theory that the difference is due to insufficient RAM in all of the win2K pro machines? -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 9:29 AM To: A. Mous; pgsql-general@postgresql.org Subject: RE: [GENERAL] Simple query takes a long time on win2K > You're right, the Celeron 2400 is much faster than the 200, > but not that much more than the 1800, and all win2k > (professional) machines are serving up the records in exactly > the same amount of time. > > Across a network (issuing the query from the 2400 celeron win > XP to the 233 PII win2k) the records are served up in about > 300ms! Much faster than simply performing the exact same > query locally on the PII. > > Now I'm really confused! > > Any ideas? Yes, I suspect it has to do with context switching. When you run it remotely the server can do it's job without much interference. When you run the client on the same machine, it keeps flipping back and forth between the server and the client. You might get better performance on Win2k server, as that would increase the timeslice for each piece of work. Also, try tweaking the "Performance boost for foreground application" on the performance tab of the system properties. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simple query takes a long time on win2K
Yes, it is quite a range of hardware, and even within the win2k pro machines there is quite a range of hardware which is why I would expect all of them to produce slightly different latency times to serve up the records. Yet, all are serving in 4 seconds! If I saw a pattern that suggested that the slowest, most inept machine produced the slowest results I'd be satisfied that it was strictly hardware, but I'm not seeing that. The slowest machine serves the records up in the same time as the second fastest machine! The hardware between all of these machines is as widely varied as you can get. The drive on the Celeron 400 was just defragmented and it made no difference at all. Fresh reboot on Celeron 2400: 1st query took 561ms, 2nd and thereafter takes 70ms. Fresh reboot on PII 233: 1st query took 4300ms, 2nd and thereafter took 4000ms. -Original Message- From: Lincoln Yeoh [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 7:51 AM To: A. Mous; pgsql-general@postgresql.org Subject: Re: [GENERAL] Simple query takes a long time on win2K They are quite different hardware. How long does it take for the _first_ time you do the query on the Celeron machine? The first time. Wait until everything has started up first and the machine is quiescent. How long does it take for the _second_ and _third_ times? Do the same for all the machines. Are the drives on the machine very different? How about you analyze the disks on each machine and compare how fragmented the database files are on the various machines? 128MB RAM is not very much for a Win2K machine. Not very far from swapping. Win2K pro or Win2K server? Performance optimized for server or desktop/applications? Regards, Link. At 02:57 AM 3/23/2005 -0700, A. Mous wrote: >Hi, > >I have a table with about 1500 records. My query is very basic: SELECT * >FROM foo; > >With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the >results in about 80ms. The same query on the same database, tested on three >different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K >machines are as follows: > >1) PIII 800, 256MB RAM >2) Celeron 400, 128MB RAM >3) PII 233, 128MB RAM > >All machines are currently using the default settings upon install. I've >tried adjusting shared_buffers and work_mem but nothing seems to make any >difference. > >EXPLAIN ANALYZE on WinXP machine gives: > >Seq Scan on foo (cost=0.00..65.71 rows=1471 width=95) (actual >time=0.000..0.000 rows=1472 loops=1) > >Same on #3 Win2K machine gives: > >Seq Scan on foo (cost=0.00..40.72 rows=1472 width=95) (actual >time=0.000..80.000 rows=1472 loops=1) > >All queries are executed locally on the server. Can anyone please explain >the profound performance difference here (which appear to be related to the >OS)? > >Much thanks in advance! > > >---(end of broadcast)--- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Simple query takes a long time on win2K
> None of these tests were run over a network - all local. > Given that the hardware is very different, however, I did > find it strange that all win2k (Pro, not server) served up > the records in almost exactly the same time, while the 2.4 > celeron is 80ms! Note that the granularity of the performance counters in EXPLAIN and in psql have a very bad resolution on Win32 in 8.0.1. The EXPLAIN counters has been fixed for 8.1, not sure if it'll be backpatched to 8.0.2. What are you getting your time values from? If it's based on either of these it's entirely possible that the speed difference is completely inside the margin of error. Also, you are not likely to be very CPU-bound for a simple query like that. //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Delay INSERT
On Wed, 23 Mar 2005 14:50:47 +, Richard Huxton wrote: > ON.KG wrote: > > Hi > > > > Does PostgreSQL have something like "INSERT DELAYD" - like it is used in > > MySQL? > > > > or any other way to delay inserting? > > What precisely does this do? It adds an insert into a 'do this' queue and returns. From PostgreSQL-s point of view it would be equivalent of issuing INSERT and not waiting for the result. The MySQL has this mainly because when other statement such as SELECT or UPDATE is in progress, the INSERT would be blocked. PostgreSQL doesn't have such issues with blocking, so only difference between INSERT and INSERT DELAYED from PostgreSQL's standpoint would be waiting and not for the result... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Simple query takes a long time on win2K
> You're right, the Celeron 2400 is much faster than the 200, > but not that much more than the 1800, and all win2k > (professional) machines are serving up the records in exactly > the same amount of time. > > Across a network (issuing the query from the 2400 celeron win > XP to the 233 PII win2k) the records are served up in about > 300ms! Much faster than simply performing the exact same > query locally on the PII. > > Now I'm really confused! > > Any ideas? Yes, I suspect it has to do with context switching. When you run it remotely the server can do it's job without much interference. When you run the client on the same machine, it keeps flipping back and forth between the server and the client. You might get better performance on Win2k server, as that would increase the timeslice for each piece of work. Also, try tweaking the "Performance boost for foreground application" on the performance tab of the system properties. //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] I'm OWNER of the db but I get `permission denied` when
Thanks It's the problem. /David Richard Huxton wrote: David Gagnon wrote: Hi all, I just created a new db wich userX is owner. I log via pgAdminIII with the same user but I can't update the pg_class. UPDATE pg_class SET reltriggers = 0 WHERE relname = 'ic' I get:ERROR: permission denied for relation pg_class I do that on my dev env. The only difference I saw beetween users is that my DEV user as priviledge to create database(But it shouln't matter...?!) I'm guessing your dev user is a superuser, and your other user isn't. Don't forget pg_class isn't in your database, it's shared by all. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] backend process
On Wed, 2005-03-23 at 10:11, Edson Vilhena de Carvalho wrote: > I'm a new user of postgreSQL > > I was loking at the documentation and testing some > things and I make: > > select * from pg_stat_database; > > pg_stat_database is writen on the table of page 317, > one os the outputs is numbackends that is the number > of > active backend server processes. I would like to know > what is a active backend server processes. In PostgreSQL every connection spawns a new backend that operates on the database semi-independently, cooperating with the other backends by means of shared memory. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] I'm OWNER of the db but I get `permission denied` when
David Gagnon wrote: Hi all, I just created a new db wich userX is owner. I log via pgAdminIII with the same user but I can't update the pg_class. You are a datdba but not a superuser :). You have to be a super user to update pg_class. Sincerely, Joshua D. Drake UPDATE pg_class SET reltriggers = 0 WHERE relname = 'ic' I get:ERROR: permission denied for relation pg_class I do that on my dev env. The only difference I saw beetween users is that my DEV user as priviledge to create database(But it shouln't matter...?!) We create the db with : createdb -O userX -E UNICODE webCatalogTest Thanks for your help! /David ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] checkpoint_timeout
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes: > "This option can only be set at server start or in the postgresql.conf > file." > Perhaps I've been misunderstanding the "or" clause? Does the "or" > clause refer to the fact that it can be sent as an option at server > start or changed with a HUP from postgresql.conf but not set per > connection? Yup. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Simple query takes a long time on win2K
You're right, the Celeron 2400 is much faster than the 200, but not that much more than the 1800, and all win2k (professional) machines are serving up the records in exactly the same amount of time. Across a network (issuing the query from the 2400 celeron win XP to the 233 PII win2k) the records are served up in about 300ms! Much faster than simply performing the exact same query locally on the PII. Now I'm really confused! Any ideas? -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 4:43 AM To: A. Mous; pgsql-general@postgresql.org Subject: RE: [GENERAL] Simple query takes a long time on win2K > Hi, > > I have a table with about 1500 records. My query is very > basic: SELECT * FROM foo; > > With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it > returns the results in about 80ms. The same query on the > same database, tested on three different win2k machines all > running 8.0.1, takes roughly 4 seconds. Win2K machines are > as follows: > > 1) PIII 800, 256MB RAM > 2) Celeron 400, 128MB RAM > 3) PII 233, 128MB RAM A Celeron 2400 is obviously much faster than any of these machines, no? COmpared to the 200Mhz, you have 12 times the processor power. This is fram frmo the difference you're seeing, though. But it might be something like the server being able to complete a lot more work in a single timeslice and thus decreasing context switching between processes. Is this Windows 2000 Server or Workstation? The difference in timeslice lengths could make a difference here. Do you see similar differences if you run it across the network? //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Simple query takes a long time on win2K
How long does it take for the _second_ and _third_ times? Just for reference. The reason we want to know about subsequent runs is that things will be cached. Are the drives on the machine very different? This is where I am leaning without any further information because the older machine (in theory) are going to have slower drives. If the celeron has a 7200 rpm machine and the others have 5400 rpm drives... How about you analyze the disks on each machine and compare how fragmented the database files are on the various machines? This is also good when was the last time you ran defrag? 128MB RAM is not very much for a Win2K machine. Not very far from swapping. Depending on what you are doing, you may already be swapping. It would be good to also see an explain anaylze Sincerely, Joshua D. Drake Win2K pro or Win2K server? Performance optimized for server or desktop/applications? Regards, Link. At 02:57 AM 3/23/2005 -0700, A. Mous wrote: Hi, I have a table with about 1500 records. My query is very basic: SELECT * FROM foo; With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the results in about 80ms. The same query on the same database, tested on three different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K machines are as follows: 1) PIII 800, 256MB RAM 2) Celeron 400, 128MB RAM 3) PII 233, 128MB RAM All machines are currently using the default settings upon install. I've tried adjusting shared_buffers and work_mem but nothing seems to make any difference. EXPLAIN ANALYZE on WinXP machine gives: Seq Scan on foo (cost=0.00..65.71 rows=1471 width=95) (actual time=0.000..0.000 rows=1472 loops=1) Same on #3 Win2K machine gives: Seq Scan on foo (cost=0.00..40.72 rows=1472 width=95) (actual time=0.000..80.000 rows=1472 loops=1) All queries are executed locally on the server. Can anyone please explain the profound performance difference here (which appear to be related to the OS)? Much thanks in advance! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Delay INSERT
In article <[EMAIL PROTECTED]>, "ON.KG" <[EMAIL PROTECTED]> writes: > Hi > Does PostgreSQL have something like "INSERT DELAYD" - like it is used in > MySQL? > or any other way to delay inserting? Every INSERT in PostgreSQL is delayed in some sense: firstly, it is not visible to anyone else until you commit, and secondly, it is written first to the (supposedly fast) write-ahead log and only later to the table files. If you have problems with INSERT speed, describe hardware, configuration, and table structure. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] I'm OWNER of the db but I get `permission denied` when
David Gagnon wrote: Hi all, I just created a new db wich userX is owner. I log via pgAdminIII with the same user but I can't update the pg_class. UPDATE pg_class SET reltriggers = 0 WHERE relname = 'ic' I get:ERROR: permission denied for relation pg_class I do that on my dev env. The only difference I saw beetween users is that my DEV user as priviledge to create database(But it shouln't matter...?!) I'm guessing your dev user is a superuser, and your other user isn't. Don't forget pg_class isn't in your database, it's shared by all. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] postgresql unicode lower/upper problem
I am not able to get work lower and upper functions on postgresql v8.0.1 and 8.1b(current cvs copy). I use Debian SID i686 GNU/Linux. Locale: ru_RU.KOI8-R createdb -E UNICODE test psql test test=> SET client_encoding TO KOI8; SET test=> SELECT t FROM t1; t ÐÐ tEsT (2 rows) test=> SELECT upper(t) FROM t1; upper ÐÐ TEST test=> SELECT lower(t) FROM t1; lower ÐÐ test (2 rows) How you can see it work perfect with latin and does not do any lower/upper with koi8. what I do wrong? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] backend process
I'm a new user of postgreSQL I was loking at the documentation and testing some things and I make: select * from pg_stat_database; pg_stat_database is writen on the table of page 317, one os the outputs is numbackends that is the number of active backend server processes. I would like to know what is a active backend server processes. Tank very much Edson Carvalho __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Simple query takes a long time on win2K
None of these tests were run over a network - all local. Given that the hardware is very different, however, I did find it strange that all win2k (Pro, not server) served up the records in almost exactly the same time, while the 2.4 celeron is 80ms! In terms of processor speed, that difference in time is not explained between the 2.4GHz and 1.8GHz machines. Perhaps it is a memory issue since the 1.8GHz box does have only 256, but I've been running Postgres on these machines for some time now and I don't recall this sort of latency with earlier versions. I'll install an earlier version of postgres and do a little test. Stay tuned... -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: March 23, 2005 4:29 AM To: A. Mous Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Simple query takes a long time on win2K A. Mous wrote: > Hi, > > I have a table with about 1500 records. My query is very basic: SELECT * > FROM foo; > > With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the > results in about 80ms. The same query on the same database, tested on three > different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K > machines are as follows: > > 1) PIII 800, 256MB RAM > 2) Celeron 400, 128MB RAM > 3) PII 233, 128MB RAM > > All machines are currently using the default settings upon install. I've > tried adjusting shared_buffers and work_mem but nothing seems to make any > difference. Hmm - very strange. It couldn't be network related could it? IIRC on Windows machines you connect via localhost (because there aren't any unix domain sockets). There have been reports of different performance over network connections, but I don't know if this applies to local connections or if it's as serious as this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL support
Walker, Jed S wrote: Hi, I am an experienced Oracle DBA and I am now working on building a PostgreSQL database for a project. Part of the lure for this was the no-cost licensing as we'll possibly be putting instances of this new system into many local sites. At this time we are planning to build little logic into the database. I will initially do hot backups on it, and will likely want to create a standby cluster (using WAL logs) for high availability. You can use PITR, Mammoth Replicator or Slony for all of this. I'd like to know your opinions on support for PostgreSQL. 1. Do you think I should purchase commercial support (at least for the initial development and release)? Well I am biased because I lead one of the support companies but I think it really depends on your needs. Command Prompt (my company) provides everything from incident based support all the way up to full support and development contracts. Our incident based support is even available 24x7. Thus you can use us only when you need us. 2. Do you have recommendations on what companies are good and a good value? :) 3. Opinions on pay-per-incident vs. support agreements (am I likely to have many issues I'll need support on - see my intro)? Again this depends on your needs. Incidents are great if you are only going to need a company a couple of times a year. If you find that you are using them as a Tier 3 every month for 5-10 hours then a support agreement may save you some money. 4. Any other tips you can give me on having support for this project? There are several good companies out there. Don't be afraid to ask for references. Sincerely, Joshua D. Drake Command Prompt, Inc. 503-667-4564 Your input would be appreciated. Thanks, Jed S. Walker ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OSX, ODBC and Office 2004
Le mardi 22 mars 2005 à 17:55 +, Konstantinos Agouros a écrit : > does this work in any way (PG 7.4.7 and OSX 10.3), so I can access postgres > as data source from say excel? I use OpenOffice.org and JDBC Tony ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] strange overlaps?
Hello,
it's true?
tarif=# select ('10:10:10'::time,'10min'::interval) overlaps
('18:00:00'::time, '6hours'::interval);
overlaps
--
t
(1 row)
I think not. There is problem in overflow one parametr.
tarif=# select ('06:10:10'::time,'1min'::interval) overlaps
('18:00:00'::time, '5hours 59min'::interval);
overlaps
--
f
(1 row)
tarif=# select version();
version
-
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Red Hat Linux 3.3.2-1)
(1 row)
Regards
Pavel Stehule
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL support
On Wed, 2005-03-23 at 05:25, Walker, Jed S wrote: > Hi, > > I am an experienced Oracle DBA and I am now working on building a PostgreSQL > database for a project. Part of the lure for this was the no-cost licensing > as we'll possibly be putting instances of this new system into many local > sites. At this time we are planning to build little logic into the database. > I will initially do hot backups on it, and will likely want to create a > standby cluster (using WAL logs) for high availability Look at Slony for non-WAL shipping replication and PITR for wal shipping. Both are quite useful, in different ways. I like Slony because I always have a live failover ready to go and it's nice to point long running report queries there so you're not loading your primary server down too much. PITR is nice in case things go horrible wrong and someone does something like delete from some table without a where clause. > I'd like to know your opinions on support for PostgreSQL. > 1. Do you think I should purchase commercial support (at least for the > initial development and release)? Not for initial development. Your Oracle experience will be both your blessing and your curse. I.e. your general database knowledge should be fine to get you up and running. Using Oracleisms (i.e. giving all the memory on the computer to postgres instead of letting the kernel do the caching etc...) might get in the way. Read up on tuning a pgsql server here: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > 2. Do you have recommendations on what companies are good and a good value? Not really, I've never used them. But the ones that have folks here on the lists are the ones I'd go to first. > 4. Any other tips you can give me on having support for this project? The postgresql mailing lists are the best starting point. Search the archives. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Simple query takes a long time on win2K
They are quite different hardware. How long does it take for the _first_ time you do the query on the Celeron machine? The first time. Wait until everything has started up first and the machine is quiescent. How long does it take for the _second_ and _third_ times? Do the same for all the machines. Are the drives on the machine very different? How about you analyze the disks on each machine and compare how fragmented the database files are on the various machines? 128MB RAM is not very much for a Win2K machine. Not very far from swapping. Win2K pro or Win2K server? Performance optimized for server or desktop/applications? Regards, Link. At 02:57 AM 3/23/2005 -0700, A. Mous wrote: Hi, I have a table with about 1500 records. My query is very basic: SELECT * FROM foo; With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the results in about 80ms. The same query on the same database, tested on three different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K machines are as follows: 1) PIII 800, 256MB RAM 2) Celeron 400, 128MB RAM 3) PII 233, 128MB RAM All machines are currently using the default settings upon install. I've tried adjusting shared_buffers and work_mem but nothing seems to make any difference. EXPLAIN ANALYZE on WinXP machine gives: Seq Scan on foo (cost=0.00..65.71 rows=1471 width=95) (actual time=0.000..0.000 rows=1472 loops=1) Same on #3 Win2K machine gives: Seq Scan on foo (cost=0.00..40.72 rows=1472 width=95) (actual time=0.000..80.000 rows=1472 loops=1) All queries are executed locally on the server. Can anyone please explain the profound performance difference here (which appear to be related to the OS)? Much thanks in advance! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] I'm OWNER of the db but I get `permission denied` when doing updating table pg_class ???? Any help appreciated
Hi all, I just created a new db wich userX is owner. I log via pgAdminIII with the same user but I can't update the pg_class. UPDATE pg_class SET reltriggers = 0 WHERE relname = 'ic' I get:ERROR: permission denied for relation pg_class I do that on my dev env. The only difference I saw beetween users is that my DEV user as priviledge to create database(But it shouln't matter...?!) We create the db with : createdb -O userX -E UNICODE webCatalogTest Thanks for your help! /David ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Delay INSERT
ON.KG wrote: Hi Does PostgreSQL have something like "INSERT DELAYD" - like it is used in MySQL? or any other way to delay inserting? What precisely does this do? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Delay INSERT
On Wed, Mar 23, 2005 at 07:31:22PM +0300, ON.KG wrote: > Does PostgreSQL have something like "INSERT DELAYD" - like it is used in > MySQL? > > or any other way to delay inserting? What problem are you trying to solve? Are you aware that PostgreSQL uses Multiversion Concurrency Control (MVCC) so readers and writers don't block each other? http://www.postgresql.org/docs/8.0/static/mvcc.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Delay INSERT
Hi Does PostgreSQL have something like "INSERT DELAYD" - like it is used in MySQL? or any other way to delay inserting? Thanx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] inherited table and rules
On Tue, 22 Mar 2005, Scott Frankel wrote: > Syntax troubles. > > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? According to the docs, In a FROM clause, I should be able to > use the ONLY keyword preceding the table name. This throws an error: > > UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey = 1; It's actually UPDATE ONLY people (I was using the select version in my message as a shorthand, sorry). > What is the proper syntax for specifying FROM ONLY in the inheritance > statement? You don't need to do it in the inheritance clause, and I'm not sure what it would do if it were allowed. > This also throws an error: > > CREATE TABLE people_history ( > hist_pkeySERIALNOT NULL PRIMARY KEY, > hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP > ) INHERITS ONLY (people); > > What does GUC stand for? ;) I think it's like grand unified configuration. It's the configuration variables in the conf file and SETs and so on. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PLPGSQL
On Mar 23, 2005, at 8:51 AM, Shaun Clements wrote: Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in another. This needs to be done dynamically as part of a loop. So the column name needs to be called as a variable. Im stuck. The answer is here to allow you to construct SQL statements from parts: http://www.postgresql.org/docs/current/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN You can pass your column name as an argument to a function that does what you want and then concatenate it with whatever else you want in your SQL. Then just EXECUTE the resulting statement. Sean ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [GENERAL] PLPGSQL
Shaun Clements wrote: Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in another. This needs to be done dynamically as part of a loop. So the column name needs to be called as a variable. Im stuck. Any suggestions Use pl/tcl/perl/python or similar rather than plpgsql - it's not good for this sort of thing. Pick whichever language you are most familiar with, they should all cope fine with this sort of problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Table audit system
Hello list, I need to define an audit system that would be easyli include or exclude certain tables, the process is a purchase order where many users changes the info in diferent ways, the requerimient is to log the stamp and user of the change on a table and additionaly log a snapshot of the the order at the time it was change, that must include any child table too, generally the order document includes the order header main table, the order detail child table, the order costs child table and the order comment history child table. So given the need to log a full snapshot not only the change of a column I ask you for ideas or suggestion to get this properly done in Postgresql. Thanks in advance, -- Sinceramente, Josué Maldonado. ... "Toda violación de la verdad no es solamente una especie de suicidio del embustero, sino una puñalada en la salud de la sociedad humana." Ralph Waldo Emerson. Filósofo, ensayista, poeta y político EE.UU. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgres oracle emulation question
On Tue, 22 Mar 2005 20:38:13 -0800, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Lonni J Friedman wrote: > > >On Tue, 22 Mar 2005 17:51:06 -0800, Randy Samberg > ><[EMAIL PROTECTED]> wrote: > > > > > >> > >>Does anyone know if there is a way in Postgres to emulate Oracle, in other > >>words make Postgres think it is an Oracle database? If so, do you have any > >>idea what percentage of people are doing this, and how successful they are > >>with this? Also, how is this done? Do you know of any good links that > >>discuss this. My manager is thinking about replacing a couple of Oracle > >>databases with Postgres, and would like to know the answer to this question. > >> > >> > > > >You mean make postgres slow, bloated & buggy? > > > > > I am going to assume that was a joke, but it is not helpful. No, it wasn't a joke. I'm forced to maintain Oracle databases every day, and I've yet to find any redeeming qualities, so it baffles me why anyone would want to emulate them. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Changing constraints to deferrable
Greg Stark wrote: I want all my foreign key constraints to be deferrable. They were all created with the default (not deferrable). Is it enough to just do update pg_constraint set condeferrable = 't' where contype = 'f'; No - the constraints are actually enforced by triggers - Just just normally don't see those triggers - but if you look into pg_triggers, you'll find them. The have "tgisconstraint" set to true, so it should be easy to find them. Try an additional "update pg_trigger set isdeferrable=true where pgisconstraint = true", and it should work.. I'm not etirely sure about the fieldnames - so better check them - e.g "\d pg_catalog.pg_trigger" could help, when typed into psql ;-) mfg, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
FW: [GENERAL] PLPGSQL
Title: FW: [GENERAL] PLPGSQL Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in another. This needs to be done dynamically as part of a loop. So the column name needs to be called as a variable. Im stuck. Any suggestions Kind Regards, Shaun Clements -Original Message- From: Sean Davis [mailto:[EMAIL PROTECTED]] Sent: 23 March 2005 03:33 PM To: Shaun Clements Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] PLPGSQL On Mar 23, 2005, at 7:56 AM, Shaun Clements wrote: > Is there no way in pgplsql > to call on a dynamic column. > I need to be able to dynamically determine the latest month column > within a dataset, and to get that columns data. > I am unfamiliar with other languages within Postgres > > Kind Regards, > Shaun Clements > You can get all the column names for a table called 'testtable' using: select a.attname from pg_attribute a, pg_class c where a.attrelid=c.oid and a.attnum>0 and c.relname='testtable'; You can then decide what column to use based on whatever logic you like. You will then need to construct the SQL statement using || (concatenate) and execute it using EXECUTE. http://www.postgresql.org/docs/current/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Does this help? Another simpler way to do this would be a different table structure where you put the month in a column by itself rather than a different column for each month. Sean
Re: [GENERAL] Extracting object source code from database to store in CVS...
On Wed, Mar 23, 2005 at 14:10:30 +0200, Adrianna Pinska <[EMAIL PROTECTED]> wrote: > > More specifically, I've been looking for a way to persuade postgresql > to output the create script for a single object - without much > success. It seems that pg_dump can output a dump of the entire > database schema or a dump of a single table, but not of a different > kind of object like a function or view. So at the moment it looks > like I'll have to parse the output of the psql "\d" commands into > create scripts by myself. Note that you can use the -E option to see what queries psql uses to create its output. This might help you write the queries you need to dump function bodies. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PLPGSQL
On Mar 23, 2005, at 7:56 AM, Shaun Clements wrote: Is there no way in pgplsql to call on a dynamic column. I need to be able to dynamically determine the latest month column within a dataset, and to get that columns data. I am unfamiliar with other languages within Postgres Kind Regards, Shaun Clements You can get all the column names for a table called 'testtable' using: select a.attname from pg_attribute a, pg_class c where a.attrelid=c.oid and a.attnum>0 and c.relname='testtable'; You can then decide what column to use based on whatever logic you like. You will then need to construct the SQL statement using || (concatenate) and execute it using EXECUTE. http://www.postgresql.org/docs/current/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Does this help? Another simpler way to do this would be a different table structure where you put the month in a column by itself rather than a different column for each month. Sean ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PLPGSQL
Title: [GENERAL] PLPGSQL Is there no way in pgplsql to call on a dynamic column. I need to be able to dynamically determine the latest month column within a dataset, and to get that columns data. I am unfamiliar with other languages within Postgres Kind Regards, Shaun Clements
Re: [GENERAL] PostgreSQL support
Hi Jed, On Wed, 2005-03-23 at 04:25 -0700, Walker, Jed S wrote: > I am an experienced Oracle DBA and I am now working on building a PostgreSQL > database for a project. Part of the lure for this was the no-cost licensing > as we'll possibly be putting instances of this new system into many local > sites. At this time we are planning to build little logic into the database. > I will initially do hot backups on it, and will likely want to create a > standby cluster (using WAL logs) for high availability. > > I'd like to know your opinions on support for PostgreSQL. > 1. Do you think I should purchase commercial support (at least for the > initial development and release)? > 2. Do you have recommendations on what companies are good and a good value? > 3. Opinions on pay-per-incident vs. support agreements (am I likely to have > many issues I'll need support on - see my intro)? > 4. Any other tips you can give me on having support for this project? 2ndQuadrant would be interested in supporting your use of hot backups and standby clustering. We offer services and support specifically aimed at your needs in that area. I can discuss contractual arrangements with you, if interested. Best Regards, Simon Riggs http://www.2ndquadrant.com/postgresql.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Extracting object source code from database to store in CVS...
On Wed, 23 Mar 2005 07:32:08 -0500, Sean Davis <[EMAIL PROTECTED]> wrote: > You can look at the system catalogs. In particular, look at: > > http://www.postgresql.org/docs/current/static/catalogs.html > http://www.postgresql.org/docs/current/static/catalog-pg-proc.html Thanks - the info you get from these is the same as what is produced by the built-in psql describe functions, but it's in a more useful format. This will make creating the scripts a little simpler. Apologies for the duplicate mail, by the way. Adrianna -- Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn! --Registered Linux User #334504-- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL support
Am Mittwoch, 23. März 2005 12:25 schrieb Walker, Jed S: > I'd like to know your opinions on support for PostgreSQL. > 1. Do you think I should purchase commercial support (at least for the > initial development and release)? > 2. Do you have recommendations on what companies are good and a good value? > 3. Opinions on pay-per-incident vs. support agreements (am I likely to have > many issues I'll need support on - see my intro)? > 4. Any other tips you can give me on having support for this project? Check out the companies offering support: http://techdocs.postgresql.org/companies.php In the end, only you can decide whether you actually need support. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Extracting object source code from database to store in CVS...
You can look at the system catalogs. In particular, look at: http://www.postgresql.org/docs/current/static/catalogs.html http://www.postgresql.org/docs/current/static/catalog-pg-proc.html You can do something like select proname,prosrc from pg_proc; as an example. You could use one of the procedure languages like plperl to grab all the current function definitions, dump them to text files with respective names (some notice will have to be paid to overloaded functions, I suppose), and when necessary, read in the file(s) of new/edited functions and executing the sql to drop/create or recreate them. Hope this helps Sean On Mar 23, 2005, at 7:10 AM, Adrianna Pinska wrote: Hello, I'm working on a project which uses postgresql (7.4.x), and a lot of the project code is in functions, views, etc. in a postgresql database. I would like to create an automated process for extracting all this code to individual text files (which can be managed through a version control system), and for putting the code in the text files back in the database. To begin with, has this sort of thing been done before? I don't want to re-invent the wheel (unless it's a wheel with a proprietary licence). More specifically, I've been looking for a way to persuade postgresql to output the create script for a single object - without much success. It seems that pg_dump can output a dump of the entire database schema or a dump of a single table, but not of a different kind of object like a function or view. So at the moment it looks like I'll have to parse the output of the psql "\d" commands into create scripts by myself. Does anyone here know of a (linux) command-line utility, or a function which can be added to psql, which produces create scripts for single objects? I believe that mysql has a built-in command that does it; that's the functionality I'm looking for. Regards Adrianna -- Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn! --Registered Linux User #334504-- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Extracting object source code from database to store in CVS...
Hello, I'm working on a project which uses postgresql (7.4.x), and a lot of the project code is in functions, views, etc. in a postgresql database. I would like to create an automated process for extracting all this code to individual text files (which can be managed through a version control system), and for putting the code in the text files back in the database. To begin with, has this sort of thing been done before? I don't want to re-invent the wheel (unless it's a wheel with a proprietary licence). More specifically, I've been looking for a way to persuade postgresql to output the create script for a single object - without much success. It seems that pg_dump can output a dump of the entire database schema or a dump of a single table, but not of a different kind of object like a function or view. So at the moment it looks like I'll have to parse the output of the psql "\d" commands into create scripts by myself. Does anyone here know of a (linux) command-line utility, or a function which can be added to psql, which produces create scripts for single objects? I believe that mysql has a built-in command that does it; that's the functionality I'm looking for. Regards Adrianna -- Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn! --Registered Linux User #334504-- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] multi line text data/query ?bug?
> - what about storing a signed document? it's possible that newline >conversion makes the signature invalid. How would you restore the original >document? Before you answer think of: >a) a client running on a platform different from the one that inserted > the document; >b) a document with _mixed_ newline types, such as a windows text with > enmbedded bare \n or \r. [*] I think there can be a difference between ASCII (text) data and binary data. Think of the example of FTP again, which handles this very nicely. Binary data should not have new lines converted. There is a well accepted difference between binary and ascii. Text can be defined as being completely readable by the human eye. If there happens to be binary data embedded in the text, that is not a part of the text but rather supports the text. Any data that can not have the binary data modified becuase it will render the data unusable is not really text. I am not familiar with signed documents, but I would question how they are currently handled in a cross OS environment. You may as well ask the same question about how you would handle a jpeg image. Obviously you will not want to look for LF and replace it with CRLF because in that data LF does not mean skip a line. >what about any other function that may be affected by newline style? >I mean, the user may insert a text that he knows it's 1000 chars long, >and finds that PG thinks it's only 980. Is this "consistent"? If a user inserts a string that he thinks is 1000 charcters long and across PG implementations and documentation it is considered to be 980 charcters long, then that is consistent. If he enters a string that he thinks is 1000 characters long and sometimes PG thinks that it is 980, sometimes 1000 and sometimes 1050 that is not consistent. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Simple query takes a long time on win2K
> Hi, > > I have a table with about 1500 records. My query is very > basic: SELECT * FROM foo; > > With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it > returns the results in about 80ms. The same query on the > same database, tested on three different win2k machines all > running 8.0.1, takes roughly 4 seconds. Win2K machines are > as follows: > > 1) PIII 800, 256MB RAM > 2) Celeron 400, 128MB RAM > 3) PII 233, 128MB RAM A Celeron 2400 is obviously much faster than any of these machines, no? COmpared to the 200Mhz, you have 12 times the processor power. This is fram frmo the difference you're seeing, though. But it might be something like the server being able to complete a lot more work in a single timeslice and thus decreasing context switching between processes. Is this Windows 2000 Server or Workstation? The difference in timeslice lengths could make a difference here. Do you see similar differences if you run it across the network? //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simple query takes a long time on win2K
A. Mous wrote: Hi, I have a table with about 1500 records. My query is very basic: SELECT * FROM foo; With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the results in about 80ms. The same query on the same database, tested on three different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K machines are as follows: 1) PIII 800, 256MB RAM 2) Celeron 400, 128MB RAM 3) PII 233, 128MB RAM All machines are currently using the default settings upon install. I've tried adjusting shared_buffers and work_mem but nothing seems to make any difference. Hmm - very strange. It couldn't be network related could it? IIRC on Windows machines you connect via localhost (because there aren't any unix domain sockets). There have been reports of different performance over network connections, but I don't know if this applies to local connections or if it's as serious as this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL support
Hi, I am an experienced Oracle DBA and I am now working on building a PostgreSQL database for a project. Part of the lure for this was the no-cost licensing as we'll possibly be putting instances of this new system into many local sites. At this time we are planning to build little logic into the database. I will initially do hot backups on it, and will likely want to create a standby cluster (using WAL logs) for high availability. I'd like to know your opinions on support for PostgreSQL. 1. Do you think I should purchase commercial support (at least for the initial development and release)? 2. Do you have recommendations on what companies are good and a good value? 3. Opinions on pay-per-incident vs. support agreements (am I likely to have many issues I'll need support on - see my intro)? 4. Any other tips you can give me on having support for this project? Your input would be appreciated. Thanks, Jed S. Walker ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: ++ [GENERAL] PLPGSQL
Shaun Clements wrote:
Can you assign the value of a dynamic record value.
For example
sales_month1_x := RECORDNAME.quote_ident('month1_'||quote_literal(yr2));
You're right - it won't work. PLPGSQL has strict type-checking, so it's
no good for this sort of stuff. Perhaps pl/tcl/perl/python would be
better for you.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about function body checking and 8.1
On Mar 22, 2005, at 10:04 PM, Tony Caduto wrote: Ok, here is a example CREATE OR REPLACE FUNCTION mytest(); RETURNS VOID as $$ DECLARE mytestvar varchar; mytestvar2 integer; BEGIN mytestvarr = 'bla'; select testfield from nonexistanttable where testfield = 2 INTO mytestvar2; --The table does not exits, yet postgresql does not complain. END; $$ LANGUAGE 'plpgsql' VOLATILE; I also seem to remember that one of the 8.0 betas actually did better checking, but then it was gone in the next beta. I could be wrong on that though. Also if you happen to use PLperl or any of the other ones, do they actually do better checking than PLpgsql? Last time I used a PLperl function it didn't do any checking at creation either. I think (from experience rather than knowledge) that that is still the case as of 8.0.1. I know Tom Lane and I had a brief discussion on one of the lists on the subject a month or two ago, but I can't seem to find the emails. Sean ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] multi line text data/query ?bug?
On Wed, 23 Mar 2005, Sim Zacks wrote: In any case, there are 2 correct solutions to the problem for the case of postgresql. 1) Database standard - postgresql chooses a newline standard and every client must support that if they support postgresql. Either put the onus on the client developers to override the OS standard and support the postgresql standard, or have the db convert the incoming newlines into the db standard. 2) Server's OS - the server must convert any new lines coming in to the standard that it expects. This is similar to an ftp client that converts newlines on the transfer. That means that data sent to a Windows server with an LF will be converted to a CRLF and vice versa. The data restore function will also have to follow the above procedures to make sure you can take data from one server to the other without compromising integrity. Without one of these solutions, PostGreSQL is not compatible between servers and clients. A query written on any client should return the same result. The query being the visible appearance on the screen. That is what the users would expect to have returned. Yeah, those were my points. The _open_ problems are: - what about storing a signed document? it's possible that newline conversion makes the signature invalid. How would you restore the original document? Before you answer think of: a) a client running on a platform different from the one that inserted the document; b) a document with _mixed_ newline types, such as a windows text with enmbedded bare \n or \r. [*] - what about any other function that may be affected by newline style? I mean, the user may insert a text that he knows it's 1000 chars long, and finds that PG thinks it's only 980. Is this "consistent"? What if the user selects for the messages longer than 990? What is the expected answer, from the user standpoint? There's no easy solution I think. [*] This is _way_ more common than you'd think. RFC2822, internet message format, says lines are CRFL separated. It happens sometimes that a message contains a NL or a CR alone. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] multi line text data/query ?bug?
In any case, there are 2 correct solutions to the problem for the case of postgresql. 1) Database standard - postgresql chooses a newline standard and every client must support that if they support postgresql. Either put the onus on the client developers to override the OS standard and support the postgresql standard, or have the db convert the incoming newlines into the db standard. 2) Server's OS - the server must convert any new lines coming in to the standard that it expects. This is similar to an ftp client that converts newlines on the transfer. That means that data sent to a Windows server with an LF will be converted to a CRLF and vice versa. The data restore function will also have to follow the above procedures to make sure you can take data from one server to the other without compromising integrity. Without one of these solutions, PostGreSQL is not compatible between servers and clients. A query written on any client should return the same result. The query being the visible appearance on the screen. That is what the users would expect to have returned. "Marco Colombo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Wed, 23 Mar 2005, Sim Zacks wrote: > > > While I would agree with you that from a purely technical standpoint, the > > user inserted into the database a CRLF and a query with just an LF does not > > exactly match that, from a users and more practical perspective, that does > > not make sense at all. That is why I surrounded the word bug in ??. > > > > I would say that from a users perspective it qualifies as a bug because they > > did not put in specific binary characters. They want a newline. From a > > database standards perspective, I would argue that any database that allows > > connections from a client without qualifying a required operating system > > should be OS neutral. > > > > I would say it is a bug from a users perspective because the exact same > > query works differently from different clients. Since the user does not > > choose what binary characters to put in, they are invisible to the user. > > Anything that is completely invisible to the user should not be considered > > valid qualifying data. > > > > As there is no postgresql database standard, such as "all newlines are unix > > newlines" it is impossible to write a client that will necessarily return > > the data that you want. > > > > This is the exact problem we are having with Python right now, as a Windows > > client cannot write a python function to be run on a linux server. > > Unfortunately, it's not that simple. There are problems with python > when _both_ the client and the server are Windows. Python itself > _always_ uses \n even on Windows. So the only solution is to > "pythonize" the input (convert to \n), no matter what. > > For the more general problem of handling text, see my comments in > this thread: > http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php > > There are interesting problems with multiline text, as a datatype. > Think of digital signatures and checksums. Think of a simple function: > len(text) > should it count line separators as characters? In theory, the only > way to get cross-platform consistent behaviour, is to _ignore_ line > separators when counting or checksumming. But the real world solution > is to treat textfiles as binary and let the users or the application > handle the conversion. > > .TM. > -- >/ / / > / / / Marco Colombo > ___/ ___ / / Technical Manager > / / / ESI s.r.l. > _/ _/ _/[EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Simple query takes a long time on win2K
Hi, I have a table with about 1500 records. My query is very basic: SELECT * FROM foo; With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the results in about 80ms. The same query on the same database, tested on three different win2k machines all running 8.0.1, takes roughly 4 seconds. Win2K machines are as follows: 1) PIII 800, 256MB RAM 2) Celeron 400, 128MB RAM 3) PII 233, 128MB RAM All machines are currently using the default settings upon install. I've tried adjusting shared_buffers and work_mem but nothing seems to make any difference. EXPLAIN ANALYZE on WinXP machine gives: Seq Scan on foo (cost=0.00..65.71 rows=1471 width=95) (actual time=0.000..0.000 rows=1472 loops=1) Same on #3 Win2K machine gives: Seq Scan on foo (cost=0.00..40.72 rows=1472 width=95) (actual time=0.000..80.000 rows=1472 loops=1) All queries are executed locally on the server. Can anyone please explain the profound performance difference here (which appear to be related to the OS)? Much thanks in advance! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] multi line text data/query ?bug?
On Wed, 23 Mar 2005, Sim Zacks wrote: While I would agree with you that from a purely technical standpoint, the user inserted into the database a CRLF and a query with just an LF does not exactly match that, from a users and more practical perspective, that does not make sense at all. That is why I surrounded the word bug in ??. I would say that from a users perspective it qualifies as a bug because they did not put in specific binary characters. They want a newline. From a database standards perspective, I would argue that any database that allows connections from a client without qualifying a required operating system should be OS neutral. I would say it is a bug from a users perspective because the exact same query works differently from different clients. Since the user does not choose what binary characters to put in, they are invisible to the user. Anything that is completely invisible to the user should not be considered valid qualifying data. As there is no postgresql database standard, such as "all newlines are unix newlines" it is impossible to write a client that will necessarily return the data that you want. This is the exact problem we are having with Python right now, as a Windows client cannot write a python function to be run on a linux server. Unfortunately, it's not that simple. There are problems with python when _both_ the client and the server are Windows. Python itself _always_ uses \n even on Windows. So the only solution is to "pythonize" the input (convert to \n), no matter what. For the more general problem of handling text, see my comments in this thread: http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php There are interesting problems with multiline text, as a datatype. Think of digital signatures and checksums. Think of a simple function: len(text) should it count line separators as characters? In theory, the only way to get cross-platform consistent behaviour, is to _ignore_ line separators when counting or checksumming. But the real world solution is to treat textfiles as binary and let the users or the application handle the conversion. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
++ [GENERAL] PLPGSQL
Title: ++ [GENERAL] PLPGSQL
Adding to that.
Can you assign the value of a dynamic record value.
For example
If I have written dataset returned, into a record.
I now want to call on a particular column value from the first row.
The column name is dynamic.
Can I use something like this
sales_month1_x := RECORDNAME.quote_ident('month1_'||quote_literal(yr2));
Where the value of
RECORDNAME.month1_2004
is assigned to sales_month1_x
Is this possible.
It doesnt look right, in that I have referenced RECORDNAME.quote_ident
That should bomb out..
how would I reference the dynamic column of the record.
Thanks in advance.
Kind Regards,
Shaun Clements
[GENERAL] PLPGSQL
Title: [GENERAL] PLPGSQL
Hi All
Im trying to assign the value of a dynamic variable, to a variable.
How is this done in PostgresQL. Is this allowed in Postgres Procedural Language
Here is my attempt.
variable1:= quote_ident('variable_'||quote_literal(year));
Where the variable is called
variable_2004
where name, represents the year
RESULT WANTED: variable1 gets assigned the value of variable_2004.
Any help is appreciated.Thanks
Kind Regards,
Shaun Clements
