Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 29/11/03, Randal L. Schwartz ([EMAIL PROTECTED]) wrote:
Well, since I need 2.5 ideas per month for the three columns I'm still
writing, I'm certainly in a position to write nice things about PG,
although I always have to work it in from a Perl slant.
Actually, I'm sure that any of the magazines I'm in would appreciate
an additional article or two from me.
If you can think of something that fits in 2000 words or so (or 4000
if it needs part 1 and 2), and can have a Perl wrapper, I'd appreciate
some inspiration.
Hi Randal
I think I may have an idea for an article which would address a common
problem for people writing database client interfaces:
The problem is simply explained.
Problem title:
The page of pages problem (!)
The problem:
You want to return a subset of a large number items using some
fairly complex search criteria. You want to make only one database
call, benefit from a cached query, and don't want to have all the
rows in memory. How do you get the total count of pages for the
relevant search criteria?
Why is this relevant?
Moving logic that is inherent to the database to the database
provides a potentially rich yet simple interface to database
queries that can benefit a number of client applications.
Typically this sort of query would be written as at least two
dynamically generated queries in the client program that has to be
parsed by the backend before it is executed. By using functions we
can hide complex joins behind simple field names, and provide
flexible (if limited) search capabilites, as well as caching and
sensible error messages.
Approach:
Using Postgres one can construct a function and then do either
SELECT * from function fn_explore($searchstring, $limit, $offset);
OR
SELECT
*
FROM
function fn_explore()
WHERE
searchterm ~* 'test'
LIMIT
5
OFFSET
10;
What is cool about the second format is that (if the function
returned a type 'explore_result' as below), your PHP/Perl programmer
can at their interface do something like
'... where id 1 AND author IN ('james', 'bill')...'
However I don't know how you get back the total rows in this case,
also maybe the caching effects are minimised?
Type definition:
CREATE TYPE explore_result as (
id INTEGER, -- some sort of row id
total INTEGER, -- total rows for query
author VARCHAR,
image BYTEA
/*
Not needed unless search is done outside db.
, searchterm VARCHAR
*/
);
Sketch function definition:
CREATE OR REPLACE FUNCTION
fn_explore (integer, integer, integer) RETURNS setof explore_result
AS '
DECLARE
searchstring ALIAS for $1;
offsetter ALIAS for $2;
limiter ALIAS for $3;
resulter explore_page%rowtype;
BEGIN
/*
variable verifation section chopped
*/
FOR resulter IN
SELECT
n_id as id,
LOJ.pagetotal as total
pers.t_name as author,
image.b_contents as image
/*
need searchterm returned if we are doing search outside
the database
, COALESCE(t_title || '' '' || t_text, ) as searchterm
FROM
db
/*
- self join on db LOJ for unoffset, unlimited row count
refer to searchterm stuff below
*/
WHERE
/* note, if we are doing a search outside of the
* function and t_title or t_text could be empty then we
* need to coalesce to an empty string
* COALESCE(t_title || '' '' || t_text, ) as searchterm
*/
searchstring ~ t_title || '' '' || t_text
ORDER BY
dt_modified DESC
LIMIT
limiter
OFFSET
offsetter
,
LOOP
RETURN NEXT
resulter;
END LOOP;
RETURN;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
[EMAIL PROTECTED]
www.campbell-lange.net
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing user
Thanks for your help but I still have a small problem. I'm try to do as you suggested and use prepare/execute but I'm doing something silly. I'm using: PREPARE my_prep1(name) AS SET SESSION AUTHORIZATION $1; and get the error message: ERROR: syntax error at or near set at character 27 I have tried many variations on this theme but have no luck. Suggestions? Thanks Colin C G [EMAIL PROTECTED] writes: SET SESSION AUTHORIZATION username; ERROR: syntax error at or near $1 at character 28 You'll need to use EXECUTE to do this. Utility statements in general aren't prepared to deal with parameters. regards, tom lane _ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger ---(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] UTF support in WIN32 native and Lower/Upper in 7.5 release
I'm looking at TODO list for PostgreSQL and Fix upper()/lower() to work for multibyte encodings has no dash (-) -- so it won't be made in 7.5 ? Another question is about WIN32 native release - currently, all win users cannot set locales, becouse Cygwin does not support it -- I hope that it will be solved in native release, so UTF-8 should work as well ?? Just wanted to know how to plan my further work. Thanks. ML ---(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] Changing user
On Tuesday 16 December 2003 10:12, C G wrote: Thanks for your help but I still have a small problem. I'm try to do as you suggested and use prepare/execute but I'm doing something silly. I'm using: PREPARE my_prep1(name) AS SET SESSION AUTHORIZATION $1; and get the error message: ERROR: syntax error at or near set at character 27 You want EXECUTE. Something like: DECLARE set_qty text; ... set_qry := ''SET SESSION AUTHORIZATION '' || $1; EXECUTE set_qry; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] passing array as argument and returning an array in plpgsql
Hi all,
I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like
argument : '{1,2,3}'
Return value : varchar array
Variables :
-
a alias for $1
b _varchar
Usage :
-
b[1] = a[1];
b[2] = a[2];
return b;
Is it possible.
TIA,
--
regards,
Deepa K
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] UTF support in WIN32 native and Lower/Upper in 7.5
On Tue, 16 Dec 2003, Marek Lewczuk wrote: I'm looking at TODO list for PostgreSQL and Fix upper()/lower() to work for multibyte encodings has no dash (-) -- so it won't be made in 7.5 ? An item in the TODO list only gets a dash when it is complete. The fact that it does not have a dash does not mean that there is no hope for the 7.5 release, as Peter E is working on this particular item. Another question is about WIN32 native release - currently, all win users cannot set locales, becouse Cygwin does not support it -- I hope that it will be solved in native release, so UTF-8 should work as well ?? You may be confusing locale with encoding here. UTF-8 is an encoding not a locale. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] UTF support in WIN32 native and Lower/Upper in 7.5
Kris Jurka wrote: On Tue, 16 Dec 2003, Marek Lewczuk wrote: Another question is about WIN32 native release - currently, all win users cannot set locales, becouse Cygwin does not support it -- I hope that it will be solved in native release, so UTF-8 should work as well ?? You may be confusing locale with encoding here. UTF-8 is an encoding not a locale. Well, yes. But locales are needed if we want to sort query results with local signs ? Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] passing array as argument and returning an array in
hello
It is possible
CREATE OR REPLACE FUNCTION foo(anyarray) RETURNS anyarray AS '
DECLARE b integer[];
BEGIN b := $1; b[1] := b[1] + 1;
RETURN b;
END;
' LANGUAGE plpgsql;
testdb011= select foo(ARRAY[1,2,3]);
foo
-
{2,2,3}
(1 dka)
Regards
Pavel
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] add column sillyness
[EMAIL PROTECTED] says... tested, solid, native replication both syncronous and asyncronous (at least they list this as a feature, I assume it works as advertised) As an Interbase/Firebird user, I'm confused by this. There is a replication solution, but AFAIK, it's commercial - not Open Source. native windows versions This is true - if PostgreSQL got its install/setup as easy as for IB/FB, they would really start to go places! scales down better for embedded apps There is a dll version available for single user apps - which makes installation very nice (on Windows). The first feature is something that is not a huge deal to me right now but it probably will be someday. Right now I do a full backup every 15 minutes and rsync it to a backup db server. As my databases are small right now this is not much of a problem. I'm hoping that this feature will pop up in postgres before it becomes a must have. I thought that there was a commerical Replicator that has gone Open Source? Is there more than one project for PG? Does anyone have a link to such a comparison? Also can anyone elaborate on the features that postgres has that firebird lacks? If there were some obvious show stoppers in firebird it would save me the time of having to do a trial port of an app to firebird before I find them on my own. What would constitute a show stopper for you? Paul... rg -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] add column sillyness
[EMAIL PROTECTED] says... Yes, I didn't mean to make a statement about firebird support (which I know nothing about) But that didn't stop you actually making an (untrue) statement about it! but rather I was just trying to comment on and show appreciation for the amazing support that I get here. It appears to me that support is good here - however it is certainly comparable on the firebird lists. Paul... rg -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(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] add column sillyness
[EMAIL PROTECTED] says... Last night after posting this I asked myself. Could I get the same kind of support (basically an answer to any question within 24 hours from one of the actual postgres developers) with firebird that I can get here? I doubt it. www.ibphoenix.com - go to lists and you will find actual developers also on the support list. Paul... -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] add column sillyness
[EMAIL PROTECTED] says... AFAICT, the main thing that Firebird lacks is a viable open-source development community :-(. This is untrue - go to www.ibphoenix.com and follow the links to the lists. It's a nice bit of software, and I'd be happy to see it doing better, but it seems like they just have not been able to gather critical mass around it. So in any comparison you need to factor in the likelihood that Postgres will be improving at a much greater rate than Firebird. Firebird seems to be holding its own in the battle with Interbase 7 (commerical product). Paul... regards, tom lane -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Any commercial shopping cart packages using
On Mon, 2003-12-15 at 11:53, James Moe wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Mon, 15 Dec 2003 09:13:49 +, Tony wrote:
excel it's previous incantation.
aside
The spelling is its, not it's.
Its is a possessive pronoun. It's is a contraction for it is.
/aside
'tis not as simple as that my friend, 'tis really more a matter of
dialect.
http://www.word-detective.com/back-d.html#its
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
P.S. Hi Greg :-)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Php help
First, your subject line is misleading and likely to get your post
ignored. Your problem isn't related to PHP, it is an sql problem. (And
as such probably more appropriate for the pgsql-sql list, but whatever)
I think what you're trying to do is:
UPDATE rocket SET search = partno WHERE partno ilike '5R%';
Robert Treat
On Thu, 2003-12-11 at 01:39, Eric Holmstrom wrote:
Hi there,
Ive been reading but not getting far, so thought i would ask here. IN
SQL im trying to do this.
What its meant to do is look in Column partno. Then check if there are
any part numbers starting with 5R. Once it done that it should insert
the value 5R RACING into the blank SEARCH column inline with it.
Ive Tried
Select partno LIKE 5R% INSERT INTO rocket (search) VALUE (5R RACING)
Recieved the error
You have an error in your SQL syntax near 'INSERT INTO rocket (search)
VALUE (5R RACING)' at line 1
any ideas how 2 do this? or point me to reading material to help?
Thankyou
Eric Holmstrom
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: FW: [GENERAL] database failure..
Ausrack Webmaster wrote: Nobody got any ideas on the below problem? :-( From the behaviour I would guess there is something corrupt in one of the system catalogs of that database. Make sure the coredump size of the postmaster process is unlimited, recreate the problem and you should find a file named core in the data directory of that database. Hopefully the postgres executable was built with enough symbol information so that you can get a stack backtrace from that core file with a debugger. Jan [root /tmp]# psql -V psql (PostgreSQL) 7.0.2 contains readline, history, multibyte support Portions Copyright (c) 1996-2000, PostgreSQL, Inc Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. Nothing at all in the logs...How do I change the debugging/log level to show more? NB. a few of the DBs on the server dont have any problems at all... Jason -Original Message- From: Marc G. Fournier [mailto:[EMAIL PROTECTED] Sent: Sunday, December 14, 2003 8:00 PM To: Ausrack Webmaster Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] database failure.. On Sun, 14 Dec 2003, Ausrack Webmaster wrote: Hi, I have a database, which just happens to be the main database on a RAQ4..(cobalt) that even when recreated it still dies, even before readding any tables.. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit cobalt=# \d The connection to the server was lost. Attempting reset: Succeeded. I can't dump either.. getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. '. Any idea what might be causing this? anything in the logs? hardware failure maybe? what version of database? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is it not datestyle that determines date format output?
On Thu, 11 Dec 2003, Netto wrote: The way PostgreSQL deals with the date format is confusing me... I need PostgreSQL to return dates from selects at this format: dd/mm/, but it insists in returning it as -mm-dd. I say insists cause I had already set datestyle to European (in postgresql.conf) which represents the format I want... I checked it executing: SHOW DATESTYLE and I got: DateStyle --- ISO with European conventions When inserting dates, PostgreSQL understands very well my date format like dd/mm/, but it is also important to get the date like that. I think it's possible, but I had tried all the tricks I knew or I could retrieve from manual... This may be a dup (it arrived in my inbox on 11 Dec 2003), but I'll reply just in case. What flavor of Postgresql are you running? I'll assume 7.4. I'm assuming you've read this section of the docs: http://www.postgresql.org/docs/current/static/datatype-datetime.html Have you tried entering: set DateStyle='SQL, dmy'; ISO style makes it the -mm-dd format, SQL makes it the other. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Hiding views or functions definitions to defined users
Hi Is there a working solution to hide views, functions or even any kind of object definition to certain users, possibly using schemas (like private schemas versus public ones) ? The situation is as followed : we would like to give one of our partners read/write access to a database, but we don't want them to see certain views or functions definitions because of privacy concerns. Thanks for any support Laurent Perez --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: FW: [GENERAL] database failure..
You're gonna have a hard time getting support for a version that old (we've since seen 7.1, 7.2, 7.3, and 7.4 come out and 7.5 is in the cooker right now. If it's possible to backup your database, I'd highly recommend upgrading postgresql on a test machine or something. It sounds like you might have a bad block on your hard drive, or possible 7.0.2 has managed to corrupt the data files all on its own. Note that 7.0.3 was the last of that line, so even if you're gonna stick with 7.0.x, you should be running that version. Each version of Postgresql since 6.5.x has gotten faster and more stable in my experience, and many bugs have been squashed since the time that 7.0 was put out. If you can backup the drive postgresql is on file level wise and test it for bad blocks, that would be good, if it's IDE it might do well being reformatted. Also, look at running memtest86 on the box to make sure you don't have flakey memory. (www.memtest86.com, it's free) On Tue, 16 Dec 2003, Ausrack Webmaster wrote: Nobody got any ideas on the below problem? :-( [root /tmp]# psql -V psql (PostgreSQL) 7.0.2 contains readline, history, multibyte support Portions Copyright (c) 1996-2000, PostgreSQL, Inc Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. Nothing at all in the logs...How do I change the debugging/log level to show more? NB. a few of the DBs on the server dont have any problems at all... Jason -Original Message- From: Marc G. Fournier [mailto:[EMAIL PROTECTED] Sent: Sunday, December 14, 2003 8:00 PM To: Ausrack Webmaster Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] database failure.. On Sun, 14 Dec 2003, Ausrack Webmaster wrote: Hi, I have a database, which just happens to be the main database on a RAQ4..(cobalt) that even when recreated it still dies, even before readding any tables.. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit cobalt=# \d The connection to the server was lost. Attempting reset: Succeeded. I can't dump either.. getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. '. Any idea what might be causing this? anything in the logs? hardware failure maybe? what version of database? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] Any commercial shopping cart packages using
My previous query applies here too then... Is the cart built using full features of PG or is it a port using database abstraction libs with the same functionality as MySQL? Obviously the power of PG lies in all the goodies MySQL doesn't yet have. Cheers T. B. van Ouwerkerk wrote: Are there any commercial web store/shopping cart packages or host sites that run under PostgreSQL? I found one web store package in the pgsql project archives, but it looks like it may need a lot of tinkering to get it working. http://www.fishcart.org It runs with MySQL, PostgreSQL, Solid, Oracle and MSSQL. With the latest version we ran into some minor issues with PostgreSQL but those will be solved with the new release that is supposed to get out as soon as all the latest features are fully tested. B. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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] [NOVICE] PostgreSQL Training
Quoting Peter Eisentraut [EMAIL PROTECTED]: Amy Young wrote: In the mean time, I will investigate the 21 day book (I have used the series many times!) and hope the PostgreSQL community will recognize the need for some training classes I don't see that there is a lack of availability of training opportunities. Just ask any of the PostgreSQL consultants and they will do custom training for you. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Great idea!! As a part of advocacy perhaps we in the community should add training as one of the things we do. I'm sure some of us have done training before but if we mobilize this effort more formally from within, we could quickly have quite a bit of trainers once we decide how to divide up the knowledge (i.e. training levels). I think Bruce's said his materials are on his web site so perhaps we should start there with the intention of repackaging that information for community distribution. -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgres respond after toomany times to a query view
On 16 Dec 2003, claudia wrote: Hi, I developing a program using postgres and linux like operating system. My problem is this: I have a quite complicated view with roughly 1 record. When I execute a simple query like this select * from myview postgres respond after 50 - 55 minutes roughly. I hope that someone can help me with some suggestion about reason of this behavior and some solution to reduce time ti have results. Thank you for your attentions and I hope to receive some feedback as soon as possible We'll need to see a couple things: schema of the underlying tables, your view definition, and the output of: explain analyze select * from view; Also, what version of postgresql are you running? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] passing array as argument and returning an array in
I got this when I was searching for something else. I will forward this
to you.
http://archives.postgresql.org/pgsql-general/2003-11/msg00852.php
CREATE or REPLACE FUNCTION foo(integer[]) RETURNS int AS
'DECLARE
a alias for $1;
index integer := 1;
total integer := 0;
BEGIN
WHILE a[index] 0
LOOP
total := total + a[index];
index := index + 1;
END LOOP;
RETURN total;
END;
' LANGUAGE 'plpgsql';
test= select foo('{1,2}');
foo
-
3
(1 row)
On Tue, 2003-12-16 at 03:25, K. Deepa wrote:
Hi all,
I am using postgresql7.4. How to handle arrays in plpgsql. How can
I pass an array. Is it possible to retrieve values from an array by
indexing it like
argument : '{1,2,3}'
Return value : varchar array
Variables :
-
a alias for $1
b _varchar
Usage :
-
b[1] = a[1];
b[2] = a[2];
return b;
Is it possible.
TIA,
--
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Join Issues
Hello all, I have been working with joins and having alot of success up until now. What I have is this: SELECT a.merno ,g.mcmid FROM ( total AS a LEFT JOIN mcmid AS g ON (g.merno=a.merno)) WHERE a.repno='11' AND a.month='2003-11-01' AND g.month='2003-11-01' ORDER BY merno Currently it is returning only 178 records where it should be returning 407 records. The 401 records are what are returned from the total table. I beleave the problem is with the: AND g.month='2003-11-01' any clues? ---(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] Firebird and PostgreSQL at the DB Corral.
On 16/12/2003 21:04 Paul Ganainm wrote: Hi Paul, --- Better than row-level locking X (I assume that what is meant here is MVCC?) One thing I couldn't find when looking at the FB on-line docs a week or two ago was anything like support for transaction isolation level serializable. Whilst the PG docs states taht its implementation of t.i.l.s is not quite to SQL spec, it is still very good as you don't need to lock rows with select ... for update. How does FB do in this respect? Functional and Partial indexes O -- No partial indexes? Get them to put it on their TODO list ;) BTW, has FB got an equivalent of PG sequences? -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] UTF support in WIN32 native and Lower/Upper in 7.5 release
Marek Lewczuk wrote: I'm looking at TODO list for PostgreSQL and Fix upper()/lower() to work for multibyte encodings has no dash (-) -- so it won't be made in 7.5 ? Another question is about WIN32 native release - currently, all win users cannot set locales, becouse Cygwin does not support it -- I hope that it will be solved in native release, so UTF-8 should work as well ?? Just wanted to know how to plan my further work. Thanks. Not sure. CC'ing the win32 list. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] update slows down in pl/pgsql function
On Tue, 16 Dec 2003, Jenny Zhang wrote: I have stored procedure written in pl/pgsql which takes about 13 seconds to finish. I was able to identify that the slowness is caused by one update SQL: UPDATE shopping_cart SET sc_sub_total=sc_subtotal, sc_date=now() WHERE sc_id=sc_id; Umm, is that exactly the condition you're using? Isn't that going to update the entire table? ---(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] Sequence question.
I'm working on an idea that uses sequences. I'm going to create a table like this: id serial, sequence int, keyword varchar(32), text text for every keyword there will be a uniq sequence for it eg: id, sequence, keyword 1, 1, foo, ver1 2, 1, bar, bar ver1 3, 2, foo, ver2 4, 2, bar, bar ver2 etc... I could have one sequence for all keyword which would be 1,3, etc... I would be prefer to have them in sequence. I'm sure someone has ran into this before, any ideas? Anthony. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Join Issues
On Tue, 16 Dec 2003, Dev wrote: Hello all, I have been working with joins and having alot of success up until now. What I have is this: SELECT a.merno ,g.mcmid FROM ( total AS a LEFT JOIN mcmid AS g ON (g.merno=a.merno)) WHERE a.repno='11' AND a.month='2003-11-01' AND g.month='2003-11-01' ORDER BY merno Currently it is returning only 178 records where it should be returning 407 records. The 401 records are what are returned from the total table. I beleave the problem is with the: AND g.month='2003-11-01' any clues? By saying g.month = '2003-11-01' in the where you've effectively removed the outerness of the join. If there's no matching g row for g.merno=a.merno, it extends the a row with nulls for the g column and then will be checking g.month='2003-11-01' which will return unknown because the g row has a null for month. Depending on the behavior you want, either you'd want AND (g.month is null or g.month='2003-11-01') in the where or you want the month clause in the ON at which point it's taken into account for determining if there's a matching row. ---(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] Any commercial shopping cart packages using postgresql?
Alex Satrapa wrote: I'm currently exploring the Zelerate AllCommerce system http://allcommerce.sourceforge.net Ick... this product is *so* the poster child of the MySQL generation: # # Table structure for table 'addresses' # CREATE TABLE addresses ( objid varchar(20) DEFAULT '' NOT NULL, objclass varchar(20) DEFAULT '' NOT NULL, objtype varchar(20) DEFAULT '' NOT NULL, ... PRIMARY KEY objid ); No foreign keys! Look at all those DEFAULT '' NOT NULL columns! What are they thinking? As penance for suggesting this product, I will clean up the SQL and at least post my experiences with installing and using this product on PostgreSQL. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Any commercial shopping cart packages using postgresql?
-Original Message- From: Alex Satrapa [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 7:24 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Any commercial shopping cart packages using postgresql? Alex Satrapa wrote: I'm currently exploring the Zelerate AllCommerce system http://allcommerce.sourceforge.net Ick... this product is *so* the poster child of the MySQL generation: # # Table structure for table 'addresses' # CREATE TABLE addresses ( objid varchar(20) DEFAULT '' NOT NULL, objclass varchar(20) DEFAULT '' NOT NULL, objtype varchar(20) DEFAULT '' NOT NULL, ... PRIMARY KEY objid ); No foreign keys! Look at all those DEFAULT '' NOT NULL columns! What are they thinking? The no foreign keys thing means RI is out the window, of course. A sea of tables, floating in a soupy database fog of danger. But as for the DEFAULT '' NOT NULL entries, CODD and Date eventually decided that NULL data was a big mistake. While SQL programmers are used to it, most end users with slim SQL familiarity will be pretty shocked when: SELECT COUNT(*) FROM addresses WHERE column = 'some_constant' Added with SELECT COUNT(*) FROM addresses WHERE NOT column = 'some_constant' Is not equal to SELECT COUNT(*) FROM addresses I tend to agree that every column should have a default and not be allowed to become NULL. Just to keep end-user astonishment at a minimum. As penance for suggesting this product, I will clean up the SQL and at least post my experiences with installing and using this product on PostgreSQL. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Any commercial shopping cart packages using postgresql?
Dann Corbit wrote: But as for the DEFAULT '' NOT NULL entries, CODD and Date eventually decided that NULL data was a big mistake. While SQL programmers are used to it, most end users with slim SQL familiarity will be pretty shocked when: And so, too, will man people with little or no understanding of internal combustion engines get surprised when their diesel engine explodes after putting high-octane unleaded fuel into it... I tend to agree that every column should have a default and not be allowed to become NULL. Just to keep end-user astonishment at a minimum. The idea of NOT NULL is to make sure that *valid* stuff is put in. Most of these columns are set to absolutely meaningless defaults, ensuring that your database not only lacks referential integrity, but contains nothing of value either! If I had enough hair left, I'd be pulling it out right about now ;) Alex ---(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] Any commercial shopping cart packages using postgresql?
On Tue, Dec 16, 2003 at 07:37:33PM -0800, Dann Corbit wrote: But as for the DEFAULT '' NOT NULL entries, CODD and Date eventually decided that NULL data was a big mistake. snip I tend to agree that every column should have a default and not be allowed to become NULL. Just to keep end-user astonishment at a minimum. Your argument does tend to support the idea that columns should not be allowed to become NULL. That's what the NOT NULL attribute is for. But that doesn't mean you should supply a default value. If the field is marked NOT NULL and you forget to insert something, the statement should error out. Silently filling with blanks is just waiting for a disaster to happen, especially without RI. Also, NULL does have some very useful situations, such as a BillID field for a transaction that has not been billed yet. Using blanks means you would have to invent a dummy bill '' to assign them to to satisfy foreign keys. With NULL the problem does not exist. Here's an idea, make all columns by default NOT NULL and add a new attribute NULLABLE ;) -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers anywhere, to being about 20% done. Sweet. And the last 80% usually takes 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce pgp0.pgp Description: PGP signature
[GENERAL] restore error - language plperlu is not trusted
Greetings, I just had to dump and restore one of my DBs (7.4RC2), and I got an interesting message. I first did: pg_dump dbname db_restore.sql Then at console did the following: \i db_restpre.sql which performed everything as expected with the following ERROR message: ERROR: language plperlu is not trusted and it gave a line number, which contained the following: GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION; Now, my plperlu functions seem to behaving as expected (they read from and write to /tmp). Should I be worried? Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Any commercial shopping cart packages using postgresql?
Ick... this product is *so* the poster child of the MySQL generation: ... No foreign keys! Look at all those DEFAULT '' NOT NULL columns! What are they thinking? You'd be surprised what the schema of most MEDICAL apps looks like (if they aren't built off MS ACCESS, that is...). And one would think medical DB designers are wont to be paranoid about quality/integrity of data ... Karsten Hilbert, MD www.gnumed.org (we do try to do a better job and this list is invaluable for it) -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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][ADMIN][HACKERS]data fragmentation
Hi, I too had the same problem; There was one query which used to take a very long time. What I did was, I took a backup of the whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Now my queries are running faster. Try it. All the very best. Somasekhar -Original Message- From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:07 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [GENERAL][ADMIN][HACKERS]data fragmentation Hi, i have a theorical question. i was thought that data fragmentation can cause a loss of performance when retrieving data from a database. Some DBMS solved this with dbspaces, but postgresql doesn't support this concept. so, pgsql databases tend to suffer from data fragmentation? if yes, what is the solution you recommend? also i was thought that even when DBMS support dbspaces DELETEing records may cause data fragmentation anyway. so, can i think of DELETE statement as a double-edged sword? it is indifferent in pgsql - it doesn't support dbspaces anyway? thanks in advance, Jaime Casanova (el_vigia) _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(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])
Re: [GENERAL][ADMIN][HACKERS]data fragmentation
On Fri, Dec 12, 2003 at 09:59:23AM +0530, Somasekhar Bangalore wrote: Hi, I too had the same problem; There was one query which used to take a very long time. What I did was, I took a backup of the whole database. Reinstalled postgres on a different mount point and restored the data back into the new database. Now my queries are running faster. Try it. All the very best. You could instead apply CLUSTER to the affected tables. (I'm assuming you already apply VACUUM periodically and REINDEX as appropiate) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Everybody understands Mickey Mouse. Few understand Hermann Hesse. Hardly anybody understands Einstein. And nobody understands Emperor Norton. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
