[GENERAL] {OT?] Auth_PG_grp_group_field directive gives parameter error
I've sent an e-mail to Guiseppe Tanzilli about this, but maybe someone here has seen this. I'm pretty sure it's not PostGreSQL, but it is tangential. We are updating to mod_auth_pgsql2 v2.0.latest and apache 2.0.latest, in the process of updating to PostGreSQL 7.4.latest. We get the following error: > Auth_PG_grp_group_field takes one argument, the name of the group-name field. on the directive Auth_PG_grp_group_field rid These are the directives we are using: Auth_PG_host 127.0.0.1 Auth_PG_port 5432 Auth_PG_database apache_auth Auth_PG_user postgres Auth_PG_pwd postgres Auth_PG_pwd_table user_bbs Auth_PG_uid_field uid Auth_PG_pwd_field pw Auth_PG_grp_table user_bbs #Auth_PG_gid_field rid # name change from 2.0.0 Auth_PG_grp_group_field rid# Auth_PG_gid_field -> Auth_PG_grp_group_field Auth_PG_grp_user_field uid # works from 2.0.0 #Auth_PG_grp_whereclause " and rid = '[EMAIL PROTECTED]' " Auth_PG_encrypted on Auth_PG_hash_type MD5 AuthName "Please Enter Your Password" AuthType Basic require valid-user require group [EMAIL PROTECTED] (And, yes, I'll also try an apache mailing list.) Apologies in advance if the noise is not appreciated. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] determine sequence name for a serial
On Thu, Oct 28, 2004 at 07:55:51PM -0400, Greg Stark wrote: > > "Ed L." <[EMAIL PROTECTED]> writes: > > > In PostgreSQL, at least for the past 5 years if not longer, if you create a > > SERIAL column for (schemaname, tablename, columnname), then your sequence > > will *always* be "schemaname.tablename_columnname_seq". If that naming > > convention changes, there will be a whole lotta breakage world-wide. > > I hope you're wrong about people expecting that to be true because it isn't. > The resulting sequence name is limited to 63 characters and gets truncated if > it goes over. (63!? was it intended to be 64?) I believe the limit used to be > 32 characters too. The NAMEDATALEN constant is defined to be 64, and that includes the trailing \0, so identifiers are limited to 63 bytes. -- Alvaro Herrera () "Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Sorting street addresses
How will that work when people reside at 123A Some St. Address that need to be sorted and/or grouped in any way should be stored as multiple fields. door number door number suffix Most often a letter street name prefix Section street name street name suffix Direcetion street type St, Cr, Rd etc subdivision typeUnit, Apt, Office etc subdivision Alphanumeric City State Postal Code Reformating street address for address correction and for the purpose of distribution and/or statistics is a pain. Try these: 105-1234 N 13th St E NY 1234 N 13th E St apt 105 1234 North 13th St East apt 105 New-York #105 1234 N Thirteenth St E NY You get my drift... and I did not try appartment letter. JLL Richard Poole wrote: On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote: I would like all those on the same street grouped together. Is there any tricks to getting the street names sorted first, possibly where numbers and strings separate? You could do something like CREATE FUNCTION streetname(text) RETURNS text AS ' SELECT substring($1 FROM ''[a-zA-Z ]+$'') ' LANGUAGE 'SQL'; and then add an ORDER BY streetname(address) to your select. Richard ---(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 8: explain analyze is your friend
[GENERAL] Upgrading from beta3 to beta4
I did not notice in the Install instructions that a dump restore needed to be done... This is what I got when I upgrade the v8b3 to the v8b4 FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with CATALOG_VERSION_NO 200408031, but the server was compiled with CATALOG_VERSION_NO 200410111. HINT: It looks like you need to initdb. Grumble... Jerry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] determine sequence name for a serial
"Ed L." <[EMAIL PROTECTED]> writes: > In PostgreSQL, at least for the past 5 years if not longer, if you create a > SERIAL column for (schemaname, tablename, columnname), then your sequence > will *always* be "schemaname.tablename_columnname_seq". If that naming > convention changes, there will be a whole lotta breakage world-wide. I hope you're wrong about people expecting that to be true because it isn't. The resulting sequence name is limited to 63 characters and gets truncated if it goes over. (63!? was it intended to be 64?) I believe the limit used to be 32 characters too. In any case it's just plain good design to avoid unnecessary interrelationships between different parts of the code. Practically speaking it makes renaming something not involve an error-prone search and replace. More importantly it makes it easier to verify that a piece of code is correct without having to hunt down all the related bits to be sure the relationships are correct. It also makes it possible to reuse or refactor the code. -- greg ---(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] determine sequence name for a serial
On Thursday October 28 2004 5:31, Michael Fuhr wrote: > On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote: > > But I didn't understand why you care to get rid of the explicit > > reference to the sequence object in your code in the first place. In > > PostgreSQL, at least for the past 5 years if not longer, if you create > > a SERIAL column for (schemaname, tablename, columnname), then your > > sequence will *always* be "schemaname.tablename_columnname_seq". > > Only for certain values of "always." Tables and columns can be renamed, > so the sequence name might no longer be "tablename_columnname_seq", > but rather "oldtablename_oldcolumnname_seq". Your point is well taken, I see the gotcha there, and thus the value of a function. Ed ---(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] determine sequence name for a serial
# But I didn't understand why you care to get rid of the explicit reference to # the sequence object in your code in the first place. In PostgreSQL, at # least for the past 5 years if not longer, if you create a SERIAL column for # (schemaname, tablename, columnname), then your sequence will *always* be # "schemaname.tablename_columnname_seq". If that naming convention changes, # there will be a whole lotta breakage world-wide. When a table is renamed, related sequences' names don't change (as of 7.4.5). The ability to automagically pull the sequence based on the schema.table.column would be nice if you don't want to worry about having to update your table name and sequence name references in code. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(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] determine sequence name for a serial
On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote: > > But I didn't understand why you care to get rid of the explicit reference to > the sequence object in your code in the first place. In PostgreSQL, at > least for the past 5 years if not longer, if you create a SERIAL column for > (schemaname, tablename, columnname), then your sequence will *always* be > "schemaname.tablename_columnname_seq". Only for certain values of "always." Tables and columns can be renamed, so the sequence name might no longer be "tablename_columnname_seq", but rather "oldtablename_oldcolumnname_seq". -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Turning a subselect into an array
On Thu, Oct 28, 2004 at 05:37:29PM -0500, Jim C. Nasby wrote: > I'm sure this has been answered before, but the search seems to be down > again. > > How can I convert the results of a subselect into an array? IE: > > CREATE TABLE a(a int, b int, c int[]); > INSERT INTO table_a > SELECT a, b, (SELECT c FROM table_c WHERE table_c.parent = table_b.id) > FROM table_b See the "Array Constructors" section in the PostgreSQL documentation: http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS INSERT INTO table_a SELECT a, b, ARRAY(SELECT c FROM table_c WHERE table_c.parent = table_b.id) FROM table_b -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] determine sequence name for a serial
On Thu, 2004-10-28 at 16:51 -0600, Ed L. wrote: > On Thursday October 28 2004 11:42, Robby Russell wrote: > > > > Thanks, this seems to work well. My goal is to actually create a php > > function that takes a result and returns the insert_id like > > mysql_insert_id() does, but without needing to know the sequence names > > and such. I would make a psql function, but I don't always have that > > option with some clients existing systems. > > An alternative is to simply select nextval() from a separately-created > sequence object to get the serial value, then insert with that value. No > need to have a serial column then, but you do need to explicitly create the > sequence object, as opposed to SERIAL. > nextval, currval, either way, I would need to know the specific sequence name. Was looking for a good way to pass a function a schema and table and return a sequence. I got exactly what I was looking for and have been able to build a function that will handle this for me. It's part of a db layer class that I use with mysql and pgsql, and was using mysql_insert_id and wanted to model a function that would return an id like the mysql_insert_id function does. (one of the few pgsql/php functions that doesn't exist in php natively..but does with mysql) -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development *--- Now supporting PHP5 --- / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Thursday October 28 2004 11:42, Robby Russell wrote: > > Thanks, this seems to work well. My goal is to actually create a php > function that takes a result and returns the insert_id like > mysql_insert_id() does, but without needing to know the sequence names > and such. I would make a psql function, but I don't always have that > option with some clients existing systems. An alternative is to simply select nextval() from a separately-created sequence object to get the serial value, then insert with that value. No need to have a serial column then, but you do need to explicitly create the sequence object, as opposed to SERIAL. But I didn't understand why you care to get rid of the explicit reference to the sequence object in your code in the first place. In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be "schemaname.tablename_columnname_seq". If that naming convention changes, there will be a whole lotta breakage world-wide. Ed ---(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] Reasoning behind process instead of thread based
On Thu, Oct 28, 2004 at 02:44:55PM +0200, Marco Colombo wrote: > I think that it would be interesting to discuss multi(processes/threades) > model vs mono (process/thread). Mono as in _one_ single process/thread > per CPU, not one per session. That is, moving all the "scheduling" > between sessions entirely to userspace. The server gains almost complete > control over the data structures allocated per session, and the resources > allocated _to_ sessions. This is how DB2 and Oracle work. Having scheduling control is very interesting, but I'm not sure it needs to be accomplished this way. There are other advantages too; in both products you have a single pool of sort memory; you can allocate as much memory to sorting as you want without the risk of exceeding it. PostgreSQL can't do this and it makes writing code that wants a lot of sort memory a real pain. Of course this could probably be solved without going to a 'mono process' model. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Turning a subselect into an array
I'm sure this has been answered before, but the search seems to be down again. How can I convert the results of a subselect into an array? IE: CREATE TABLE a(a int, b int, c int[]); INSERT INTO table_a SELECT a, b, (SELECT c FROM table_c WHERE table_c.parent = table_b.id) FROM table_b ; -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Derived tables?
CSN <[EMAIL PROTECTED]> writes: > Just wondering - does PG support derived tables? I'm > not really sure what the difference is between them > and subqueries. None whatever, at least using the definition offered by your second reference: A derived table is a select statement inside parenthesis, with an alias, used as a table in a join. The SQL92 spec appears to use the phrase in exactly this way (except they don't require the construct to appear in a join, as indeed we don't either; the most correct explanation would probably be "used as a table in a FROM clause"). MySQL often has their own spin on such terms ;-). I'm not sure what MySQL 4.1 actually supports in this line. But I'll make a side bet that they don't yet optimize them as well as we do ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Sorting street addresses
Richard Poole wrote: On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote: I would like all those on the same street grouped together. Is there any tricks to getting the street names sorted first, possibly where numbers and strings separate? You could do something like CREATE FUNCTION streetname(text) RETURNS text AS ' SELECT substring($1 FROM ''[a-zA-Z ]+$'') ' LANGUAGE 'SQL'; and then add an ORDER BY streetname(address) to your select. You could also add a function index that would help speed things along. Sincerely, Joshua D. Drake Richard ---(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 PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Sorting street addresses
On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote: > I would like all those on the same street grouped together. Is there any > tricks to getting the street names sorted first, possibly where numbers > and strings separate? You could do something like CREATE FUNCTION streetname(text) RETURNS text AS ' SELECT substring($1 FROM ''[a-zA-Z ]+$'') ' LANGUAGE 'SQL'; and then add an ORDER BY streetname(address) to your select. Richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Derived tables?
Just wondering - does PG support derived tables? I'm not really sure what the difference is between them and subqueries. http://www.mysql.com/news-and-events/press-release/release_2004_32.html http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics_printversion.asp __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Issue adding foreign key
I have 2 existing tables in my db:
iss=> \d pollgrpinfo
Table "public.pollgrpinfo"
Column | Type | Modifiers
---++---
pollgrpinfoid | integer| not null
pollgrpid | integer| not null
name | character varying(100) |
descript | character varying(200) |
Indexes:
"pollgrpinfo_pkey" primary key, btree (pollgrpinfoid)
"pollgrpinfo_pollgrpid_key" unique, btree (pollgrpid)
iss=> \d notpoll
Table "public.notpoll"
Column| Type | Modifiers
-++-
notpollid | integer| not null
pollgrpid | integer|
notgroupsid | integer|
alerting| character(1) | default 'y'::bpchar
disuser | character varying(50) |
distime | integer|
alertingcom | character varying(200) |
Indexes:
"notpoll_pkey" primary key, btree (notpollid)
"notpoll_pollgrpid_key" unique, btree (pollgrpid)
"notpoll_alerting_index" btree (alerting)
Triggers:
"RI_ConstraintTrigger_2110326" AFTER INSERT OR UPDATE ON notpoll
FROM notgroups NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('notgroups_exists', 'notpoll',
'notgroups', 'UNSPECIFIED', 'notgroupsid', 'notgroupsid')
I am trying to add a foreign key to the notpoll table
iss=> alter table notpoll add constraint pollgrp_exists foreign
key(pollgrpid) references pollgrpinfo on delete cascade;
ERROR: insert or update on table "notpoll" violates foreign key
constraint "pollgrp_exists"
DETAIL: Key (pollgrpid)=(7685) is not present in table "pollgrpinfo".
I have verified that the information is in the pollgrpinfo table:
iss=> select * from pollgrpinfo where pollgrpid=7685;
pollgrpinfoid | pollgrpid | name | descript
---+---+--+--
767 | 7685 | HTTP |
(1 row)
I could use a suggestion on how to proceed in troubleshooting the error
message. I am running 7.4.5
Thanks,
Woody
iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net
---(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] Sorting street addresses
Thanks to some help here on the list, I've been able to get addresses
sorting pretty well, but now I have a issue with same addresses on
different streets not grouping the streets. This is what I'm using a
substring search in the ORDER BY statement now like in this view:
SELECT tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
tblhudsimilargroups.similar_group_id, tblhudbuildings.address,
tblhudbuildings.hud_building_id,
is_bldg_lbp(tblhudbuildings.hud_building_id) AS is_lbp,
is_bldg_lbp_hazard(tblhudbuildings.hud_building_id) AS is_lbp_hazard
FROM (tblhudsimilargroups LEFT JOIN tblhudbuildings ON
((tblhudsimilargroups.similar_group_id =
tblhudbuildings.similar_group_id)))
ORDER BY tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
("substring"((tblhudbuildings.address)::text,
'[^0-9]+'::text))::character
varying, ("substring"((tblhudbuildings.address)::text,
'^[0-9]+'::text))::integer;
And getting this result:
ohc=> SELECT public.viewbldginfo.group_id, public.viewbldginfo.address
FROM public.viewbldginfo WHERE (public.viewbldginfo.rems_id
='84136');
group_id | address
--+--
A| 3606 ROYALTY COURT
A| 3601/3603 ROYALTY COURT
A| 3602/3604 ROYALTY COURT
A| 3605/3607 ROYALTY COURT
A| 3701/3703 MCKINLEY COURT
A| 3702/3704 MCKINLEY COURT
A| 3705/3707 MCKINLEY COURT
A| 3709/3711 MCKINLEY COURT
A| 7801/7803 SOCIAL CIRCLE
A| 7801/7803 ANDALUSIA
A| 7801/7803 HAVERSHAM
A| 7802/7804 ANDALUSIA
A| 7802/7804 HAVERSHAM
A| 7805/7807 SOCIAL CIRCLE
A| 7806/7808 HAVERSHAM
A| 7811/7813 SOCIAL CIRCLE
A| 7815/7817 SOCIAL CIRCLE
A| 7825/7827 SOCIAL CIRCLE
A| 7833/7835 SOCIAL CIRCLE
I would like all those on the same street grouped together. Is there any
tricks to getting the street names sorted first, possibly where numbers
and strings separate?
--
Robert
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] primary key and existing unique fields
I think the same too but sometimes it seems in the real world performance is given more value than a properly designed db. Or the long term flexiblity is not taken into account given the short term requirements. regards Sally From: Bruno Wolff III <[EMAIL PROTECTED]> To: Sally Sally <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Date: Thu, 28 Oct 2004 12:44:00 -0500 On Thu, Oct 28, 2004 at 14:31:32 +, Sally Sally <[EMAIL PROTECTED]> wrote: > Dawid, > I am interested in the first point you made that: > having varchar(12) in every referencing table, takes more storage > space. > The thing is though, if I have a serial primary key then it would be an > additional column. Or you are saying the space taken by a VARCHAR(12) field > is more than two INT fields? ( or is it the fact that when it is referenced > it will appear several times?) I guess the reason I am resisting the idea > of an additional primary key field is to avoid the additional lookup in > some queries. Perhaps it's a minor almost irrelevant performance factor. I think it is better to worry about what is going to make it easiest to have clean data and to support future changes than worry about performance. Over the long run hardware is cheaper than people. ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] determine sequence name for a serial
On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote: > # CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS ' > # SELECT adsrc > # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute > # WHERE > # adrelid = pg_class.oid AND > # pg_class.relnamespace = pg_namespace.oid AND > # pg_attribute.attnum = pg_attrdef.adnum AND > # pg_attribute.attrelid = pg_class.oid AND > # pg_namespace.nspname = $1 AND > # pg_class.relname = $2 AND > # pg_attribute.attname = $3; > # ' language sql; > > As per Tom's mention of pg_depend, here's something that seems to do > the trick for the time being, assuming the column is a serial: > > -- get_sequence(schema_name, table_name, column_name) > > CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS > text AS ' > SELECT seq.relname::text > FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, > pg_depend > WHERE > pg_depend.refobjsubid = pg_attribute.attnum AND > pg_depend.refobjid = src.oid AND > seq.oid = pg_depend.objid AND > src.relnamespace = pg_namespace.oid AND > pg_attribute.attrelid = src.oid AND > pg_namespace.nspname = $1 AND > src.relname = $2 AND > pg_attribute.attname = $3; > ' language sql; > Thanks, this seems to work well. My goal is to actually create a php function that takes a result and returns the insert_id like mysql_insert_id() does, but without needing to know the sequence names and such. I would make a psql function, but I don't always have that option with some clients existing systems. -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now supporting PHP5 and PHP4 --- / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] field incrementing in a PL/pgSQL trigger
On Thu, Oct 28, 2004 at 09:14:17AM -0700, Tim Vadnais wrote: > > My questions are: Is there a way I can dynamically determine the number of > fields in the rows that is being maintained. (a function much like: > PQnfields(const PGresult *); ) > Then I need a way to get the name of the field (using a function much like: > PQfname(const PGresult *, int); ) You asked this last week and there were a couple of responses: http://archives.postgresql.org/pgsql-general/2004-10/msg01077.php http://archives.postgresql.org/pgsql-general/2004-10/msg01097.php http://archives.postgresql.org/pgsql-general/2004-10/msg01112.php -- 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] primary key and existing unique fields
On Thu, Oct 28, 2004 at 14:31:32 +, Sally Sally <[EMAIL PROTECTED]> wrote: > Dawid, > I am interested in the first point you made that: > having varchar(12) in every referencing table, takes more storage > space. > The thing is though, if I have a serial primary key then it would be an > additional column. Or you are saying the space taken by a VARCHAR(12) field > is more than two INT fields? ( or is it the fact that when it is referenced > it will appear several times?) I guess the reason I am resisting the idea > of an additional primary key field is to avoid the additional lookup in > some queries. Perhaps it's a minor almost irrelevant performance factor. I think it is better to worry about what is going to make it easiest to have clean data and to support future changes than worry about performance. Over the long run hardware is cheaper than people. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [pgsql-fr-generale] Problème de threadPostgresql
Dénouement : J'ai enfin trouvé toutes les réponses à mes questions via la comande REINDEX. Merci à "Jean-Christophe Arnu" (s'il passe par ici) qui a confirmé via son article sur http://www.postgresqlfr.org/?q=node/view/49 la solution que j'avais cherché depuis quelques temps. "Froggy / Froggy Corp." wrote: > > Le serveur actuelle ayant que 256Mo de RAM, j avais supprimé il y a > plusieurs mois les connexions persistantes. > > Mais en pratique, après une petite gaffe de ma part, j avais un très bon > load, et ceci en connexion non persistantes. > > Actuellement, je n'utilise plus de connexion persistantes. Mais au final > je me demande si ce n'ai pas juste un problème de tuning car après la > suppression/restauration d'une table utilisateur, j etais passé d'un > load de 3-4 à moins de 1. > > Daniel Verite wrote: > > > > Froggy / Froggy Corp. writes > > > > > l'id du thread change constement, donc le serveur kill/créé un log à > > > chaque affichage de page pratiquement. > > > Le même test a été effectué sur un serveur de test, et là je me > > > retrouve bien avec x threads postgres. > > > > Vérifier les paramètres pgsql.allow_persistent et pgsql.max_persistent du > > fichier php.ini, à supposer que les connexions soient faites avec > > pg_pconnect() ? > > > > PS: il ne s'agit pas de threads mais de processus, postgresql n'utilisant pas > > les threads. > > > > -- > > Daniel > > PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] field incrementing in a PL/pgSQL trigger
"Tim Vadnais" <[EMAIL PROTECTED]> writes: > My questions are: Is there a way I can dynamically determine the number of > fields in the rows that is being maintained. I'm starting to think there should be a FAQ entry for this ;-) plpgsql is essentially incapable of doing anything that involves dynamic field access, especially if the field types aren't known in advance either. I believe you can do what you want in pltcl, and you can definitely write such a trigger in C, but plpgsql is the wrong tool for the job. If you want to try it in C, there are some relevant examples in contrib/spi/ in the PG source distribution. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question Regarding Locks
Karsten Hilbert <[EMAIL PROTECTED]> writes: > Just so that I am not getting this wrong: >> BTW, a handy proxy for "row has not changed" is to see if its XMIN >> system column is still the same as before. > Considering that my business objects remember XMIN from when > they first got the row would the following sequence make sure > I am in good shape ? > begin; > select ... for update; > update ... set ... where > my_pk= > AND > xmin= > This should either update 1 row in which case I can commit or > zero rows in which case I need to rollback and handle the merge > conflict. The reasoning would be that the condition > my_pk=my_pk_value would select the row I am interested in > while xmin=the_old_xmin would ensure that row hasn't been > modified. > Am I right or is there a flaw in my thinking ? I think you can skip the SELECT FOR UPDATE altogether if you do it that way. Otherwise it looks fine. 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] '1 year' = '360 days' ????
On Wed, Oct 27, 2004 at 16:26:13 -0600, Guy Fraser <[EMAIL PROTECTED]> wrote: > > When calculating any usage based on time, it is a good idea to > store usage in days:hours:minutes:seconds because they are static > and stable, if you discount the deceleration of the earth and > corrections in leap seconds for atomic clocks [see > http://tycho.usno.navy.mil/leapsec.html ]. The length of calendar days isn't constant. In many timezones, one day a year is 23 hours long and another is 25 hours long. Having month and year intervals is useful for events that repeat monthly or yearly in spite of there not being a constant number of seconds between events. ---(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] Question Regarding Locks
Just so that I am not getting this wrong: > BTW, a handy proxy for "row has not changed" is to see if its XMIN > system column is still the same as before. Considering that my business objects remember XMIN from when they first got the row would the following sequence make sure I am in good shape ? begin; select ... for update; update ... set ... where my_pk= AND xmin= This should either update 1 row in which case I can commit or zero rows in which case I need to rollback and handle the merge conflict. The reasoning would be that the condition my_pk=my_pk_value would select the row I am interested in while xmin=the_old_xmin would ensure that row hasn't been modified. Am I right or is there a flaw in my thinking ? Thanks, Karsten -- 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
[GENERAL] field incrementing in a PL/pgSQL trigger
Hi, My boss wants to add a special type of logging to some of our tables on update/delete/insert. I need to log who, when, table_name, field name, original value and new value for each record, but only logging modified fields, and he wants me to do this using postgres pgSQL triggers. The changes would be inserted into a second table. We are given 10 automatically created variables. Some of which I know I can use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME. I can use these to get general information for the update, but when the trigger is called, I don't know how many fields are in the tables that are being updated. My questions are: Is there a way I can dynamically determine the number of fields in the rows that is being maintained. (a function much like: PQnfields(const PGresult *); ) Then I need a way to get the name of the field (using a function much like: PQfname(const PGresult *, int); ) Using the dynamically generated name I could then walk the NEW and OLD columns to compare the values. (e.g. if (NEW.field != OLD.field) do something;); Can anyone help me with this? Thank you in advance. Tim Vadnais ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] what could cause inserts getting queued up and db locking??
"Brian Maguire" <[EMAIL PROTECTED]> writes: > What are the implications to further increasing the checkpoint so say > 40? AFAIK the downsides are (a) more disk space eaten for pg_xlog, (b) if you suffer a crash, it will take longer to recover (because there'll be more uncheckpointed work to replay); (c) the checkpoint itself could require more I/O because there's more pending write activity. > Also how does 8.0's background-writer feature work and what are going to > benefits? The idea of the bgwriter is to trickle out disk writes continuously instead of having a big write storm at each checkpoint. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] what could cause inserts getting queued up and db locking??
Tom, You hit the nail on the head with what we did. We did two things and it made a world of difference. We moved from RAID 5 SCSII drives to our EMC SAN RAID 10 and adjusted the checkpoint segments from 15 to 30. The bottleneck disappeared totally and actually have never seen better performance. Two questions: What are the implications to further increasing the checkpoint so say 40? Also how does 8.0's background-writer feature work and what are going to benefits? Brian -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 5:59 PM To: Brian Maguire Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] what could cause inserts getting queued up and db locking?? Brian Maguire <[EMAIL PROTECTED]> wrote: >> We though there might be locking, but noticed that there were not any >> queries in wait mode indicating that no statements were blocked by >> another statement's lock. In that case it's not a locking problem, but just a resource-saturation problem. I'm wondering if you are maxing out your disk drives' throughput. Are the slowdowns correlated with checkpoints? (Watch to see if there is a postmaster child process spawned for checkpointing when it happens.) Fooling with checkpoint intervals might help some, though I suspect the only real answer will be 8.0's background-writer feature. regards, tom lane ---(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] '1 year' = '360 days' ????
Gaetano Mendola wrote: Guy Fraser wrote: Trivia: In approximately 620 million years a day will be twice as long as it is today. Do you think then that Postgres628M.0 will fix it ? :-) Regards Gaetano Mendola I just hope, I don't have to work an equivalent fraction of the day for the same pay, but with any luck I'll have all my bills paid and be retired by then. ;-) -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL]: Unable to load libsqlpg.so
Hello: I'm trying to use kylix3 and postgresql 7.4.1. My distro was Debian Woody, kernel 2.20. I make the link /usr/local/pgsql/lib/libpq.so pointing to libpq.so.2.2 and connection with my database get fine. But now, using Debian Sarge Testing, kernel 2.4.27 (I tried also with kernel 2.6.8-1) that link don't make things work anymore, I get the error 'Unable to load libsqlpg.so' and I cannot connect with my postgresql database. Anyone knows how can I make my connection work again? Thank you, Carlos ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Reasoning behind process instead of thread based
Marco Colombo wrote: [processes vs threads stuff deleted] In any modern and reasonable Unix-like OS, there's very little difference between the multi-process or the multi-thread model. _Default_ behaviour is different, e.g. memory is shared by default for threads, but processes can share memory as well. There are very few features threads have that processes don't, and vice versa. And if the OS is good enough, there are hardly performance issues. Most servers have a desire to run on Windows-NT and I would consider Solaris a "modern and reasonable Unix-like OS". On both, you will find a significant performance difference. I think that's true for Irix as well. Your statement is very true for Linux based OS'es though. I think that it would be interesting to discuss multi(processes/threades) model vs mono (process/thread). Mono as in _one_ single process/thread per CPU, not one per session. That is, moving all the "scheduling" between sessions entirely to userspace. The server gains almost complete control over the data structures allocated per session, and the resources allocated _to_ sessions. I think what you mean is user space threads. In the Java community known as "green" threads, Windows call it "fibers". That approach has been more or less abandoned by Sun, BEA, and other Java VM manufacturers since a user space scheduler is confined to one CPU, one process, and unable to balance the scheduling with other processes and their threads. A kernel scheduler might be slightly heavier but it does a much better job. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] disabling constraints
I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint errors. Is there a way to do something like: 1) disable all constraints 2) truncate all tables 3) re-enable all constraints ? In the slony project there is a procedure "altertableforreplication" that appears to do 1), but since it is updating pg_catalog tables directly, I don't want to cut/paste before I understand what it's doing! Is there any "standard" way of doing this? - DAP -- David ParkerTazz Networks(401) 709-5130 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] compatibilityissues from 7.1 to 7.4
On Thu, Oct 28, 2004 at 10:35:27AM +0900, Joel wrote: > > I'm looking at the release notes for 7.2 and thinking that, when we make > the jump, jumping to 7.4 will probably be the best bet. Given that 7.2 is pretty much end of life now, I certainly wouldn't adopt it. If you're going through the pain of upgrading, might as well go all the way. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(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] primary key and existing unique fields
Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. Thanks Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(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] Tables and Indexes
Hi, Even in Beta, it is just fine to me! When I create a index, the tablespace used is the table's tablespace, unless I use the tablespace clause to put it in another one. It is ok, I have read documentation and learned that. I was looking for a default configuration, so ALL the indexes created will be put in a tablespace, and the tables in another one, without using tablespace clause. So, I start the postmaster, create two tablespaces, configure it to put some things in one, and some other things in another, and after that I create my tables, indexes and view the natural way. (hey, how many times did I write the word 'tablespace' in this e-mail??? hehehhe) Regards, Marcelo --- "Joshua D. Drake" <[EMAIL PROTECTED]> escreveu: > Patrick Fiche wrote: > > >Hi, > > > >I think that TABLESPACE is what you need... > >It's now available in Postgresql, just look at the > syntax in documentation. > > > > > Actually it is only available in Beta. You will have > to wait a little > while longer for stable release. > > Sincerely, > > Joshua D. Drake > > > > > > > > > > >>-- > >> > >> > >- > > > > > >>Patrick Fiche > >>email : [EMAIL PROTECTED] > >>tél : 01 69 29 36 18 > >>-- > >> > >> > >- > > > > > >> > >> > >> > > > > > >-Original Message- > >From: [EMAIL PROTECTED] > >[mailto:[EMAIL PROTECTED] > Behalf Of MaRCeLO PeReiRA > >Sent: jeudi 28 octobre 2004 15:04 > >To: pgsql > >Subject: [GENERAL] Tables and Indexes > > > > > >Hi guys, > > > >Is there a way I can separate things in PostgreSQL? > >Putting tables in a disk partition and indexes in > >another one? > > > >Regards, > > > >MaRcELo PeReiRa > >PHP/SQL/PostgreSQL > > > >__ > >Do You Yahoo!? > >Tired of spam? Yahoo! Mail has the best spam > protection around > >http://mail.yahoo.com > > > >---(end of > broadcast)--- > >TIP 8: explain analyze is your friend > > > > > > > > > >Protected by Polesoft Lockspam > >http://www.polesoft.com/refer.html > > > > > >---(end of > broadcast)--- > >TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > -- > 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 > > ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tables and Indexes
Patrick Fiche wrote: Hi, I think that TABLESPACE is what you need... It's now available in Postgresql, just look at the syntax in documentation. Actually it is only available in Beta. You will have to wait a little while longer for stable release. Sincerely, Joshua D. Drake -- - Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 18 -- - -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MaRCeLO PeReiRA Sent: jeudi 28 octobre 2004 15:04 To: pgsql Subject: [GENERAL] Tables and Indexes Hi guys, Is there a way I can separate things in PostgreSQL? Putting tables in a disk partition and indexes in another one? Regards, MaRcELo PeReiRa PHP/SQL/PostgreSQL __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend Protected by Polesoft Lockspam http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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
Re: [GENERAL] Question Regarding Locks
Tom, thanks ! You are even helping lurkers like me that haven't asked anything :-) ... > A better design is to fetch the data without locking it, allow the > user to edit as he sees fit, and then when he clicks "save" you do > something like > > begin; > select row for update; > if [ row has not changed since you originally pulled it ] then > update row with changed values; > commit; > else > abort; > notify user of conflicts > let user edit new data to resolve conflicts and try again > fi > > In this design the row lock is only held for milliseconds. > > You need to provide some code to let the user merge what he did with the > prior changes, so that he doesn't have to start over from scratch in the > failure case. > BTW, a handy proxy for "row has not changed" is to see if its XMIN > system column is still the same as before. If so, no transaction has > committed an update to it. (This may or may not help much, since you're > probably going to end up groveling over all the fields anyway in the > "notify user" part, but it's a cool hack if you can use it.) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Tables and Indexes
Hi, I think that TABLESPACE is what you need... It's now available in Postgresql, just look at the syntax in documentation. > -- - > Patrick Fiche > email : [EMAIL PROTECTED] > tél : 01 69 29 36 18 > -- - > > > -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of MaRCeLO PeReiRA Sent: jeudi 28 octobre 2004 15:04 To: pgsql Subject: [GENERAL] Tables and Indexes Hi guys, Is there a way I can separate things in PostgreSQL? Putting tables in a disk partition and indexes in another one? Regards, MaRcELo PeReiRa PHP/SQL/PostgreSQL __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend Protected by Polesoft Lockspam http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question Regarding Locks
Thanks for the response on this, especially the tip regarding xmin. I've been spending much of the night and morning comptemplating this issue. I am glad to have gotten this information, before going any further. Due to the front end design, I believe I can implement all this within a short period of time. Thanks again... On Wednesday 27 October 2004 06:44 pm, Tom Lane saith: > Terry Lee Tucker <[EMAIL PROTECTED]> writes: > > I would like to be able to provide feedback to the user when they > > select a row for update (using SELECT FOR UPDATE). At present, if the > > row is being accessed (with SELECT FOR UPDATE) by another user, the > > application just sits there waiting. > > To me, this says that you're already off on the wrong foot. > > You don't ever want your client application holding locks while a > human user edits text, drinks coffee, goes out to lunch, or whatever. > A better design is to fetch the data without locking it, allow the > user to edit as he sees fit, and then when he clicks "save" you do > something like > > begin; > select row for update; > if [ row has not changed since you originally pulled it ] then > update row with changed values; > commit; > else > abort; > notify user of conflicts > let user edit new data to resolve conflicts and try again > fi > > In this design the row lock is only held for milliseconds. > > You need to provide some code to let the user merge what he did with the > prior changes, so that he doesn't have to start over from scratch in the > failure case. What "merge" means requires some business-logic knowledge > so I can't help you there, but this way you are spending your effort on > something that actually helps the user, rather than just tells him he > has to wait. Performance will be much better too --- long-lasting > transactions are nasty for all sorts of reasons. > > BTW, a handy proxy for "row has not changed" is to see if its XMIN > system column is still the same as before. If so, no transaction has > committed an update to it. (This may or may not help much, since you're > probably going to end up groveling over all the fields anyway in the > "notify user" part, but it's a cool hack if you can use it.) > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Tables and Indexes
Hi guys, Is there a way I can separate things in PostgreSQL? Putting tables in a disk partition and indexes in another one? Regards, MaRcELo PeReiRa PHP/SQL/PostgreSQL __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] '1 year' = '360 days' ????
Guy Fraser wrote: Trivia: In approximately 620 million years a day will be twice as long as it is today. Do you think then that Postgres628M.0 will fix it ? :-) Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Reasoning behind process instead of thread based
[processes vs threads stuff deleted] In any modern and reasonable Unix-like OS, there's very little difference between the multi-process or the multi-thread model. _Default_ behaviour is different, e.g. memory is shared by default for threads, but processes can share memory as well. There are very few features threads have that processes don't, and vice versa. And if the OS is good enough, there are hardly performance issues. I think that it would be interesting to discuss multi(processes/threades) model vs mono (process/thread). Mono as in _one_ single process/thread per CPU, not one per session. That is, moving all the "scheduling" between sessions entirely to userspace. The server gains almost complete control over the data structures allocated per session, and the resources allocated _to_ sessions. I bet this is very theoretical since it'd require a complete redesign of some core stuff. And I have strong concerns about portability. Still, it could be interesting. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasoning behind process instead of thread based
Martijn van Oosterhout wrote: Now you've piqued my curiosity. You have two threads of control (either two processes or two threads) which shared a peice of memory. How can the threads syncronise easier than processes, what other feature is there? AFAIK the futexes used by Linux threads is just as applicable and fast between two processes as two threads. All that is required is some shared memory. Agree. On Linux, this is not a big issue. Linux is rather special though, since the whole kernel is built in a way that more or less puts an equal sign between a process and a thread. This is changing though. Don't know what relevance that will have on this issue. Shared Memory and multiple processes have other negative impacts on performance since you force the CPU to jump between different memory spaces. Switching between those address spaces will decrease the CPU cache hits. You might think this is esoteric and irrelevant, but the fact is, cache misses are extremely expensive and the problem is increasing. While CPU speed has increased 152 times or so since the 80's, the speed on memory has only quadrupled. Or are you suggesting the only difference is in switching time (which is not that significant). "not that significant" all depends on how often you need to switch. On most OS'es, a process switch is significantly slower than switching between threads (again, Linux may be an exception to the rule). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasoning behind process instead of thread based
[EMAIL PROTECTED] wrote: So Thomas, you say you like the PostgreSQL process based modell better than the threaded one used by MySQL. But you sound like the opposite. I'd like to know why you like processes more. Ok, let me try and explain why I can be perceived as a scatterbrain :-). PostgreSQL is a very stable and well functioning product. It is one of the few databases out there that has a well documented way of adding plugins written in C and quite a few plugins exists today. You have all the server side languages, (PL/pgsql PL/Perl, PL/Tcl, PL/Java, etc.), and a plethora of custom functions and other utilities. Most of this is beyond the control of the PostgreSQL core team since it's not part of the core product. It would be extremely hard to convert everything into a multi-threaded environment and it would be even harder to maintain the very high quality that would be required. I think PostgreSQL in it's current shape, is ideal for a distributed, Open Source based conglomerate of products. The high quality core firmly controlled by the core team, in conjunction with all surrounding features, brings you DBMS functionality that is otherwise unheard of in the free software market. I believe that this advantage is very much due to the simplicity and bug-resilient single-threaded design of the PostgreSQL. My only regret is that the PL/Java, to which I'm the father, is confined to one connection only. But that too has some advantages in terms of simplicity and reliability. So far PostgreSQL At present, I'm part of a team that develops a very reliable multi-threaded system (a Java VM). In this role, I've learned a lot about how high performance thread based systems can be made. If people on this list wants to dismiss multi-threaded systems, I feel they should do it based on facts. It's more than possible to build a great multi-threaded server. It is my belief that as PostgreSQL get more representation in the high end market where the advantages of multi-threaded solutions get more and more apparent, it will find that the competition from a performance standpoint is sometimes overwhelming. I can't say anything about MySQL robustness because I haven't used it much. Perhaps the code quality is indeed below what is required for a multi-threaded system, perhaps not. I choose PostgreSQL over MySQL because MySQL lacks some of the features that I feel are essential, because it does some things dead wrong, and because it is dual licensed. Hope that cleared up some of the confusion. Regards, Thomas Hallgren ---(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] Reasoning behind process instead of thread based
On Thu, Oct 28, 2004 at 12:13:41AM +0200, Thomas Hallgren wrote: > Martijn van Oosterhout wrote: > >A lot of these advantages are due to sharing an address space, right? > >Well, the processes in PostgreSQL share address space, just not *all* > >of it. They communicate via this shared memory. > > > Whitch is a different beast altogether. The inter-process mutex handling > that you need to synchronize shared memory access is much more expensive > than the mechanisms used to synchronize threads. Now you've piqued my curiosity. You have two threads of control (either two processes or two threads) which shared a peice of memory. How can the threads syncronise easier than processes, what other feature is there? AFAIK the futexes used by Linux threads is just as applicable and fast between two processes as two threads. All that is required is some shared memory. Or are you suggesting the only difference is in switching time (which is not that significant). Also, I admit that on some operating systems, threads are much faster than processes, but I'm talking specifically about linux here. Thanks in advance, -- Martijn van Oosterhout <[EMAIL PROTECTED]> 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. pgpHZFYBQFSEX.pgp Description: PGP signature
Re: [GENERAL] compatibilityissues from 7.1 to 7.4
On Thu, 28 Oct 2004 01:01:20 -0400 Tom Lane <[EMAIL PROTECTED]> wrote > Joel <[EMAIL PROTECTED]> writes: > > Any thoughts on the urgency of the move? > > How large is your pg_log file? 7.1 was the last release that had the > transaction ID wraparound limitation (after 4G transactions your > database fails...). If pg_log is approaching a gig, you had better > do something PDQ. Great. Very low use (to this point) BBS and similar things, so it looks like we'll miss this issue. > More generally: essentially all of the data-loss bugs we've fixed lately > existed also in 7.1. The core committee made a policy decision some > time ago that we wouldn't bother back-patching further than 7.2, however. > The only reason 7.2 is still getting some patching attention is that it > was the last pre-schema release, and so there might be some people out > there with non-schema-aware applications who couldn't conveniently move > up to 7.3 or later. But once 8.0 is out we'll probably lose interest in > supporting 7.2 as well. Thanks for the answers. I think we have good motivation to proceed. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Reasoning behind process instead of thread based
Tom Lane wrote: That argument has zilch to do with the question at hand. If you use a coding style in which these things should be considered recoverable errors, then setting up a signal handler to recover from them works about the same whether the process is multi-threaded or not. The point I was trying to make is that when an unrecognized trap occurs, you have to assume not only that the current thread of execution is a lost cause, but that it may have clobbered any memory it can get its hands on. I'm just arguing that far from all signals are caused by unrecoverable errors and that threads causing them can be killed individually and gracefully. I can go further and say that in some multi-threaded environments you as a developer don't even have the opportunity to corrupt memory. In such environments the recognized traps are the only ones you encounter unless the environment is corrupt in itself. In addition, there are a number of techniques that can be used to make it impossible for the threads to unintentionally interfere with each others memory. I'm not at all contesting the fact that a single-threaded server architecture is more bug-tolerant and in some ways easier to manage. What I'm trying to say is that it is very possible to write even better, yet very reliable servers using a multi-threaded architecture and high quality code. ... The point here is circumscribing how much can go wrong before you > realize you're in trouble. Ok now I do follow. With respect to my last comment about speed, I guess it's long overdue to kill this thread now. Let's hope the forum stays intact :-) Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Bug: 8.0 beta1 either view optimization or pgdump/pgrestore
On second thought another way to optimize a query like that would be to remove the * and only put in the columns that are actually being used, as opposed to taking the * literally. Such that if the fields in the select list use 2 columns and the join uses 1 column, only those 3 columns should be expanded by the optimizer. This would probably make the query more efficient as well, being that it selects fewer fields. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
