Re: [GENERAL] DateStyle causes drama during upgrade
Martijn van Oosterhout wrote: > > We used pg_dump in various ways, all with the date style "iso" > but always some of the dates appeared to be translated wrong. > Eventually we worked out that even though the datestyle was > set to "iso" on both machines, the old postgres read it as > "ISO with european conventions" whereas the new postgres read > it as "ISO with US conventions". > This is the postgresql debian package 7.0.2-3. > > PS. I thought we'd left behind all the US/non-US datestyle > distinction when we all started using ISO format (-mm-dd). > That was somewhat naive of me, huh? I've been bitten by this too. It seems that there are two characteristics for the dates: format (for output) and 'conventions' for input, and that 6.5 -> 7.0 changed from defaulting to European conventions to US conventions. I suspect this is Debian specific. Perhaps there should be a way of setting the conventions side of things in the /etc/postgresql/postmaster.init like there is a way of setting the format? Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Re: [GENERAL] php update problems
many thanks both andrew and ryan =)
- Original Message -
From: "ryan" <[EMAIL PROTECTED]>
To: "Mike Sears" <[EMAIL PROTECTED]>
Cc: "pgsql-general" <[EMAIL PROTECTED]>
Sent: Tuesday, August 22, 2000 2:40 PM
Subject: Re: [GENERAL] php update problems
> > Mike Sears wrote:
> >
> > Having a slight problem w/ my postgres database. for some reaons its
> > no longer updating anymore, and for what reason I'm unsure
> >
> > if I'm right then the below "should" update the table I'm working in,
> > though it doesn't. Is ther indeed something wrong here?
> >
> > > require("dbconect.inc.php");
> >
> > $result = pg_exec($db,
> > "UPDATE news
> > SET date='$date' topic='$topic' body='$body' person='$person'
> > WHERE date='$date2' topic='$topic2' body='$body2' person='$person2'
> > id2='$id';" );
>
> Hi,
>
> Your SQL looks badly formed.
> The brief syntax of UPDATE is:
>
> UPDATE SET col1=val1, col2=val2, ..., coln=valn WHERE
> condition1 AND condition2 ;
>
> so basically you're missing 'AND' between each of the where clauses, you
> don't have commas.
>
> If the update returns '0 rows altered' then you know someone else has
> modified the data from under you.
>
> Regards,
> -ryan
>
>
> --
> Ryan Rawson
> System Administrator
> Binary Environments Ltd.
> [EMAIL PROTECTED]
>
Re: [GENERAL] php update problems
> Mike Sears wrote:
>
> Having a slight problem w/ my postgres database. for some reaons its
> no longer updating anymore, and for what reason I'm unsure
>
> if I'm right then the below "should" update the table I'm working in,
> though it doesn't. Is ther indeed something wrong here?
>
> require("dbconect.inc.php");
>
> $result = pg_exec($db,
> "UPDATE news
> SET date='$date' topic='$topic' body='$body' person='$person'
> WHERE date='$date2' topic='$topic2' body='$body2' person='$person2'
> id2='$id';" );
UPDATE news SET date='$date', topic='$topic', body='$body',
person='$person' WHERE date='$date2' AND topic='$topic2' AND
body='$body2' AND person='$person2' AND id2='$id';
Cheers,
Andrew.
--
_
Andrew McMillan, e-mail: [EMAIL PROTECTED]
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
[GENERAL] php update problems
Having a slight problem w/ my postgres database.
for some reaons its no longer updating anymore, and for what reason I'm
unsure
if I'm right then the below "should" update the
table I'm working in, though it doesn't. Is ther indeed something wrong
here?
require("dbconect.inc.php");
$result =
pg_exec($db, "UPDATE news SET
date='$date' topic='$topic' body='$body' person='$person'WHERE date='$date2'
topic='$topic2' body='$body2' person='$person2' id2='$id';" );
pg_colse($db);
?>
Mike
Re: [GENERAL] Foreign key to all inherited tables
On Tue, 22 Aug 2000, Darrin Ladd wrote:
> Hi,
>
> I was wondering if there is a way to have a foreign key reference to the
> primary key column of all tables throughout an inheritance tree. For
> example, I have a parent_table with a unique_id (type serial) and a child
> table which inherits the parent_table (inheriting the unique_id). I would
> like to have another table have a field, unique_id, who's value must be in
> the unique_id field of the parent or the child. I tried adding an asterix
> to the end of the foreign key table refrence, {CONSTRAINT fk_other_table
> FOREIGN KEY (unique_id) REFERENCES parent_table* (unique_id)} but the parser
> didn't like that. Then I tried creating a check constraint on the field in
> the 'other_table' to check if the value was 'IN (SELECT unique_id from
> parent_table*)'. The table creation went fine, but when I tried to insert
> any values into the table it produced an error:
> ExecEvalExpr: unknown expression type 108.
Currently you cannot do Foreign Keys to inheritance tress (as you noted),
that's in the known things to do to the foreign key stuff, but doesn't
have a particular ETA. The latter thing is a problem with subselects in
constraints which is a not particularly easy thing to deal with, since
such constraints are actually on all tables referenced in the subselect
as well as the table you specified the constraint on.
You may be able to do this with triggers. You'd technically need one for
insert/update on the main table and one for update/delete on each table of
the inheritance tree (to prevent deletions of referenced items). This
isn't a complete soulution really (there are some details of FK that are
a bit wierd and hard to do in normal triggers, but it's probably fairly
close)
Re: [GENERAL] Great Bridge re-runs benchmark with MySQL "tuned"
See http://www.greatbridge.com/news/p_081620001.html - we increased the cache, ran a vacuum analyze, a few minor things. Regards, Ned "Poul L. Christiansen" wrote: > It would be interesting to see how well PostgreSQL performed when it was > tuned. > > Or has it allready been tuned? > > Ned Lilly wrote: > > > Folks, > > > > We posted the following announcement on our website today, at > > http://www.greatbridge.com/news/press.html. > > > > Please feel free to email me off-list with any questions. > > > > Thanks, > > Ned > > > > UPDATE, August 22, 2000: > > > > MySQL performance improves with tuning suggestions from development > > team; > > PostgreSQL still leads all contenders under heavy usage > > > > Following our recent release of AS3AP and TPC-C benchmark test > > results, Great Bridge offered to re-run the tests with tuning and > > custom configuration settings suggested by the MySQL development > > team. We did, and we want to share the results. > > > > It's important to note that the MySQL configuration originally > > tested was the default MySQL installation, using the standard > > MyODBC.dll Windows driver installed by MySQL (for the benchmark > > software client machine, which ran Windows NT). Probably the most > > significant change came from substituting a faster driver, called > > MyODBC2.dll; according to the MySQL development team, the default > > driver is used for debugging purposes, and is known to be slower in > > production environments. > > > > At their suggestion, we also implemented the following tuning > > settings: > > > > * key_buffer=64m > > * table_cache=256 > > * sort_buffer=1m > > * record_buffer=1m > > > > So what were the results? MySQL did significantly better across the > > board, averaging 69% more transactions per second in this tuned > > environment, and exceeding Postgres' raw performance until the > > seventh concurrent user. Its performance peaked at 1,321 tps (at 3 > > users), but still started to fall off about the same point as in the > > previous test (4 users). See graphic > > (http://www.greatbridge.com/img/as3ap_new.gif). > > > > What does this mean? Our interpretation is that, properly > > configured, MySQL is indeed a faster performer in raw read-only > > databases with 6 or fewer users. We should note that these tests > > results do not represent the full suite of AS3AP tests - only the > > multiuser ir_select (information retrieval) test. Other tests in the > > AS3AP suite require views, which MySQL does not currently support. > > We should also note that the TPC-C test, which simulates a more > > robust OLTP environment, still would not run under the tuned MySQL > > configuration, primarily due to SQL compliance issues (see Richard > > Brosnahan's analysis elsewhere in the main story). But overall, > > MySQL acquitted itself well when expertly tuned for the AS3AP > > ir_select test.
Re: [GENERAL] [Solved] SQL Server to PostgreSQL
I've wondered that myself, actually. What are the benefits and drawbacks to going with one over the other, besides the obvious 255-char field length limit for varchar? The reason to stay away from "memo" fields in other serious RDBMSs are typically more difficult maintenance, significantly lower performance, and requiring special function calls to get the data out. Do any of those apply to PG? Jeff Tom Lane wrote: > > Tressens Lionel <[EMAIL PROTECTED]> writes: > > Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait : > > )I was able to get the table format by using MS Access. Only question left > > )is what is the corresponding field type in PostgreSQL for a memo field in > > )SQL Server/Access (varchar())? > > > 'text' type perhaps ? > > Uh ... what's wrong with varchar(n) ? > > regards, tom lane
Re: [GENERAL] [Solved] SQL Server to PostgreSQL
On Tue, 22 Aug 2000, Tom Lane wrote: > Tressens Lionel <[EMAIL PROTECTED]> writes: > > Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait : > > )I was able to get the table format by using MS Access. Only question left > > )is what is the corresponding field type in PostgreSQL for a memo field in > > )SQL Server/Access (varchar())? > > > 'text' type perhaps ? > > Uh ... what's wrong with varchar(n) ? How big can our n be for varchar? By looking at his description I'm thinking SQL Server allows a large n. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
Re: [GENERAL] Server Overload
> I've seen brief posts regarding server loads, distrubution for heavy loads, > etc, but don't recall if there were any solutions... > > Anyways...We are running a Postgres DB against multiple frontend > webservers. For most of the time, everything runs fine. Then, all of a > sudden, everything will start to go all funky. Crashing...Errors...Etc... > > When I log onto the DB server and try to do anything, I get "Too many open > files in system." ulimit is set to unlimited and there is PLENTY of FREE > memory. What OS are you running? Despite ulimit being "unlimited", your kernel may have a limit on either the number of open files, or the number of file handles. steve
Re: [GENERAL] Server Overload
"Arthur M. Kang" <[EMAIL PROTECTED]> writes: > When I log onto the DB server and try to do anything, I get "Too many open > files in system." Sounds like you need to regenerate your kernel with larger NFILE and/or NINODE parameters. Can't give you details about how to do this since every Unix platform has a different way to do it, but consult your sysadmin documentation. regards, tom lane
Re: [GENERAL] hidden data fields
you could "hide" or mask columns through views... mikeo At 02:10 PM 8/22/00 -0700, Mike Sears wrote: In mysql you can make some data apear to be garbled or hiddne, I'm wondering if this can be done using psql?
[GENERAL] Foreign key to all inherited tables
Hi,
I was wondering if there is a way to have a foreign key reference to the
primary key column of all tables throughout an inheritance tree. For
example, I have a parent_table with a unique_id (type serial) and a child
table which inherits the parent_table (inheriting the unique_id). I would
like to have another table have a field, unique_id, who's value must be in
the unique_id field of the parent or the child. I tried adding an asterix
to the end of the foreign key table refrence, {CONSTRAINT fk_other_table
FOREIGN KEY (unique_id) REFERENCES parent_table* (unique_id)} but the parser
didn't like that. Then I tried creating a check constraint on the field in
the 'other_table' to check if the value was 'IN (SELECT unique_id from
parent_table*)'. The table creation went fine, but when I tried to insert
any values into the table it produced an error:
ExecEvalExpr: unknown expression type 108.
Does anyone have a work-around for this?
Thanks!
Darrin
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[GENERAL] Server Overload
I've seen brief posts regarding server loads, distrubution for heavy loads, etc, but don't recall if there were any solutions... Anyways...We are running a Postgres DB against multiple frontend webservers. For most of the time, everything runs fine. Then, all of a sudden, everything will start to go all funky. Crashing...Errors...Etc... When I log onto the DB server and try to do anything, I get "Too many open files in system." ulimit is set to unlimited and there is PLENTY of FREE memory. I shut down one of the frontend servers to clear some connections to the DB and then start it back up and everything seems to be back to normal. Has anyone experienced anything similar? Does anyone have any suggestions on what I could try to do to rectify this problem? Any comments and/or suggestions are much appreciated! Thanks. Arthur
Re: [GENERAL] [Solved] SQL Server to PostgreSQL
Tressens Lionel <[EMAIL PROTECTED]> writes: > Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait : > )I was able to get the table format by using MS Access. Only question left > )is what is the corresponding field type in PostgreSQL for a memo field in > )SQL Server/Access (varchar())? > 'text' type perhaps ? Uh ... what's wrong with varchar(n) ? regards, tom lane
[GENERAL] hidden data fields
In mysql you can make some data apear to be garbled or hiddne, I'm wondering if this can be done using psql?
[GENERAL] I lied! [Solved] SQL Server to PostgreSQL
I hate it when I do this. See an answer I want and run with it rather than find the real answer. Turned out the data files (.mdb) _didn't_ belong to the database. They were a piece of the database that was used for a report. Back to the old grind wheel. Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [GENERAL] regex back-references?
At 4:26 PM -0400 8/21/2000, Tom Lane wrote: >Michael Blakeley <[EMAIL PROTECTED]> writes: >> Do I need to tell postgres to rebuild pg_language, perhaps? > >See the createlang utility script. PL languages aren't installed >by default (due to possibly-overzealous concern about security). Thanks - that did it. RFE: change the error message ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. to read ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. If you have compiled postgres with 'plperl' support, you may need to run the 'createlang' utility. thanks, -- Mike
Re: [GENERAL] [Solved] SQL Server to PostgreSQL
Le 22.08.00 a 09:37, "Roderick A. Anderson" m'ecrivait : )I was able to get the table format by using MS Access. Only question left )is what is the corresponding field type in PostgreSQL for a memo field in )SQL Server/Access (varchar())? 'text' type perhaps ? Lionel
[GENERAL] [Solved] SQL Server to PostgreSQL
A thanks to everyone on this list and especially; Jeffery Rhines, Chris Knight, Chris Bitmead, and Sevo Stille. The solution turned out to be very simple. After catching a SCSI BUS speed mismatch problem which caused a NT Backup 'Restore' failure I discovered that the actual data was in .mdb files! Copied the files to a system running MS Access (Office 97) and was able to export them to a delimited format which went into PostgreSQL with very few problems. Mostly there were split lines which the \copy command didn't like. Hand corrected them. I was able to get the table format by using MS Access. Only question left is what is the corresponding field type in PostgreSQL for a memo field in SQL Server/Access (varchar())? Again thanks for all the help, Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [GENERAL] Great Bridge re-runs benchmark with MySQL "tuned"
It would be interesting to see how well PostgreSQL performed when it was tuned. Or has it allready been tuned? Ned Lilly wrote: > Folks, > > We posted the following announcement on our website today, at > http://www.greatbridge.com/news/press.html. > > Please feel free to email me off-list with any questions. > > Thanks, > Ned > > UPDATE, August 22, 2000: > > MySQL performance improves with tuning suggestions from development > team; > PostgreSQL still leads all contenders under heavy usage > > Following our recent release of AS3AP and TPC-C benchmark test > results, Great Bridge offered to re-run the tests with tuning and > custom configuration settings suggested by the MySQL development > team. We did, and we want to share the results. > > It's important to note that the MySQL configuration originally > tested was the default MySQL installation, using the standard > MyODBC.dll Windows driver installed by MySQL (for the benchmark > software client machine, which ran Windows NT). Probably the most > significant change came from substituting a faster driver, called > MyODBC2.dll; according to the MySQL development team, the default > driver is used for debugging purposes, and is known to be slower in > production environments. > > At their suggestion, we also implemented the following tuning > settings: > > * key_buffer=64m > * table_cache=256 > * sort_buffer=1m > * record_buffer=1m > > So what were the results? MySQL did significantly better across the > board, averaging 69% more transactions per second in this tuned > environment, and exceeding Postgres' raw performance until the > seventh concurrent user. Its performance peaked at 1,321 tps (at 3 > users), but still started to fall off about the same point as in the > previous test (4 users). See graphic > (http://www.greatbridge.com/img/as3ap_new.gif). > > What does this mean? Our interpretation is that, properly > configured, MySQL is indeed a faster performer in raw read-only > databases with 6 or fewer users. We should note that these tests > results do not represent the full suite of AS3AP tests - only the > multiuser ir_select (information retrieval) test. Other tests in the > AS3AP suite require views, which MySQL does not currently support. > We should also note that the TPC-C test, which simulates a more > robust OLTP environment, still would not run under the tuned MySQL > configuration, primarily due to SQL compliance issues (see Richard > Brosnahan's analysis elsewhere in the main story). But overall, > MySQL acquitted itself well when expertly tuned for the AS3AP > ir_select test.
[GENERAL] Great Bridge re-runs benchmark with MySQL "tuned"
Folks, We posted the following announcement on our website today, at http://www.greatbridge.com/news/press.html. Please feel free to email me off-list with any questions. Thanks, Ned UPDATE, August 22, 2000: MySQL performance improves with tuning suggestions from development team; PostgreSQL still leads all contenders under heavy usage Following our recent release of AS3AP and TPC-C benchmark test results, Great Bridge offered to re-run the tests with tuning and custom configuration settings suggested by the MySQL development team. We did, and we want to share the results. It's important to note that the MySQL configuration originally tested was the default MySQL installation, using the standard MyODBC.dll Windows driver installed by MySQL (for the benchmark software client machine, which ran Windows NT). Probably the most significant change came from substituting a faster driver, called MyODBC2.dll; according to the MySQL development team, the default driver is used for debugging purposes, and is known to be slower in production environments. At their suggestion, we also implemented the following tuning settings: * key_buffer=64m * table_cache=256 * sort_buffer=1m * record_buffer=1m So what were the results? MySQL did significantly better across the board, averaging 69% more transactions per second in this tuned environment, and exceeding Postgres' raw performance until the seventh concurrent user. Its performance peaked at 1,321 tps (at 3 users), but still started to fall off about the same point as in the previous test (4 users). See graphic (http://www.greatbridge.com/img/as3ap_new.gif). What does this mean? Our interpretation is that, properly configured, MySQL is indeed a faster performer in raw read-only databases with 6 or fewer users. We should note that these tests results do not represent the full suite of AS3AP tests - only the multiuser ir_select (information retrieval) test. Other tests in the AS3AP suite require views, which MySQL does not currently support. We should also note that the TPC-C test, which simulates a more robust OLTP environment, still would not run under the tuned MySQL configuration, primarily due to SQL compliance issues (see Richard Brosnahan's analysis elsewhere in the main story). But overall, MySQL acquitted itself well when expertly tuned for the AS3AP ir_select test.
Re: [GENERAL] using INTERSECT and UNION in IN clause
On Tue, Aug 22, 2000 at 01:50:26PM +0400, Alex Guryanow wrote: > Hi, > > postgresql 7.0.2. Why by executing the following query > > select * from magazine > where id in ( > select mag_id from dict where word = 'akademie' intersect > select mag_id from dict where word = 'der' intersect > select mag_id from dict where word = 'klasse' ) > > I receive the following error: > > ERROR: parse error at or near 'intersect' [snip] > > Is it possible to use INTERSECT and UNION keywords in subqueries? I guess not. I imagine this limitation will be lifted in a future version. An alternative possibility is joining the table to itself on mag_id: select * from magazine where id in ( select d1.mag_id from dict as d1, dict as d2, dict as d3 where d1.word = 'akademie' and d2.word='der' and d3.word='klasse' and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id) In fact, do the whole thing as one big join: select * from magazine,dict as d1, dict as d2, dict as d3 where d1.word = 'akademie' and d2.word='der' and d3.word='klasse' and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id and magazine.id = d1.mag_id; This should work optimally if you have indexes on magazine(id) dict(mag_id) dict(word) Hope that helps, Jules
[GENERAL] using INTERSECT and UNION in IN clause
Hi, postgresql 7.0.2. Why by executing the following query select * from magazine where id in ( select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) I receive the following error: ERROR: parse error at or near 'intersect' while the query select mag_id from dict where word = 'akademie' intersect select mag_id from dict where word = 'der' intersect select mag_id from dict where word = 'klasse' ) is executed successfully. Is it possible to use INTERSECT and UNION keywords in subqueries? Regards, Alex
