date:20041027

Martijn,
I agree. One issue I can think of is that if you store each timestamp
as a (seconds,timezone) pair, the storage requirements will balloon,
since timezone can be something like Australia/Sydney and this will
be repeated for every value in the table. I don't know how to deal
easily with this since there is no unique identifier to timezones and
no implicit order.
The only solution I can think of is have initdb create a pg_timezones
table which assigns an OID to each timezone it finds. Then the type can
use that.
I think this is a good solution actually, any thoughts?
Using OID's is a good idea, but I think a canonical list of known 
timezone to OID mappings must be maintained and shipped with the 
PostgreSQL core.

If OID's are generated at initdb time, there's a great risk that the 
OID's will differ between databases using different versions of 
PostgreSQL. That in turn will have some negative implications for data 
exchange.

Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Error restoring bytea from dump

2004-10-27 Thread Egyd Csaba (Freemail)

Hi again,

Another mistake of mine ... :) The real problem was that I used a Windows
based archiver (PowerArchiver) to unzip the gz file which - for some reasons
??? - chunked the long lines at aproximately every 16K. Using gunzip the
problem oozed away. :) 

Bye,
 -- Csaba

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr
Sent: Tuesday, October 26, 2004 10:05 PM
To: Egy?d Csaba (Freemail)
Cc: '[EMAIL PROTECTED]'
Subject: Re: [GENERAL] Error restoring bytea from dump

On Tue, Oct 26, 2004 at 08:51:31PM +0200, Egyd Csaba wrote:

 the restoration of a dump stops at the line above.

What line above?  Are you referring to Error restoring bytea in the
subject header?  Is that the *exact* error message?

 The dump was created with pgsql 7.3.2 and I need to pump it into a 
 7.4.3 one. Should anybody tell me what the problem can be and how I 
 can solve it.

Have extraneous newlines and/or carriage returns made it into the dump?
That might result in invalid input syntax for type bytea, which isn't the
exact error you mentioned but might be what you meant.

 (There are double apostophes [''] many times in the string - is it
normal???
 Besides of the field separator [','] of course...)

http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-STRINGS
http://www.postgresql.org/docs/7.4/static/datatype-binary.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 2004.10.15.
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 2004.10.15.
 


---(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] Bug: 8.0 beta1 either view optimization or pgdump/pgrestore

2004-10-27 Thread Sim Zacks

OOps. Didn't send it to the list. (There has to be a better way of
doing this then always having to remember to change the recipient.)

I'm sorry, I thought I described the problem pretty clearly.
Here is the actual queries with comment annotations where the problem
occurred.

1) This is the query that I typed in:
create or replace view qry_AssembliesMissingInfo as
SELECT a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, a.NumPins, 
case when numpins is null then '' else 'Num Pins Not Specified. ' end || case when 
PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. ' end || 
case when (PackagePricingGroupID is not null And BasePrice is null) Or PricePerPin is 
null then 'Group Prices are Not Configured' else '' end AS Problem
FROM qry_AssembliesMissingInfo1 a LEFT JOIN 

/*Here is the virtual table I mentioned using select * on a join*/
(select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID = 
c.PricingGroupID) groups

ON a.PackageID = groups.PackageID AND a.SupplierID = groups.SupplierID
WHERE (a.NumPins Is Null AND groups.BasePrice Is Null) OR (groups.BasePrice Is Null 
AND groups.PricePerPin Is Null)
OR (coalesce(PricePerPin,0)0 and coalesce(NumPins,0)=0)
GROUP BY a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, 
a.NumPins, case when numpins is null then '' else 'Num Pins Not Specified. ' end || 
case when PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. 
' end || case when (PackagePricingGroupID is not null And BasePrice is null) Or 
PricePerPin is null then 'Group Prices are Not Configured' else '' end, 
groups.BasePrice, groups.PricePerPin;


2) This is what PGAdmin shows after I have created view:
-- View: qry_assembliesmissinginfo

-- DROP VIEW qry_assembliesmissinginfo;

CREATE OR REPLACE VIEW qry_assembliesmissinginfo AS 
 SELECT a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, a.numpins, 
(
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END || 
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) || 
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR 
priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END AS problem
   FROM qry_assembliesmissinginfo1 a

   /*Here is where the problem comes in as you can see there are a
   number of fields with the same name, such as pricinggroupid,
   createuserid... */
   
   LEFT JOIN ( SELECT packagepricinggroupid, pricinggroupid, packageid, createuserid, 
createdate, modifyuserid, modifydate, pricinggroupid, description, supplierid, 
baseprice, priceperpin, currencyid, createuserid, createdate, modifyuserid, modifydate
FROM packagepricinggroups b
   JOIN pricinggroups c ON b.pricinggroupid = c.pricinggroupid) groups ON 
a.packageid = groups.packageid AND a.supplierid = groups.supplierid
  WHERE a.numpins IS NULL AND groups.baseprice IS NULL OR groups.baseprice IS NULL AND 
groups.priceperpin IS NULL OR COALESCE(priceperpin, 0::double precision)  0::double 
precision AND COALESCE(numpins, 0) = 0
  GROUP BY a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, 
a.numpins, (
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END || 
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) || 
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR 
priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END, groups.baseprice, groups.priceperpin;

3) Exactly what happened.
   My view worked fine after I created it.
   I did a Backup using PGAdmin (which uses pg_dump)
   I did a restore to a new database using PGAdmin (which uses PG_restore)
   The restore gave me the ambiguous error I mentioned before
   on that view.
   I took the PGAdmin version of my view and saw that it had
   extrapolated the fields from * and that was causing the
   ambiguity. I then changed it back to * and executed the
   create or replace view statement.
   The view now works again.
   
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax



Sim Zacks [EMAIL PROTECTED] writes:
 I just did a dump and restore of my database and one of my views did
 not recreate.

 The error received was :
 pg_restore.exe: [archiver (db)] could not execute query: ERROR:  column reference 
 pricinggroupid is ambiguous

 I checked the function in the original database,

[GENERAL]

2004-10-27 Thread f-f

Hello !

To kill a session i used KILL -INT PID.

This command wasn't successfull. The processus is still here when a 'ps ax' or a 
'select * from pg_stat_activity'

Is there an other way to kill this process only because a web server 24/7 use postgres 
and cannot stop postgresql now.

Thanks !

HM

PS : To stop postgres i use 'pg_ctl -m immediate stop' to stop
postgresql because the '/etc/init.d/postgresql stop' fails when a
session is blocked.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Theory

Mayra,
I need info on the caracteristics of object
relational databases and their advantages as well as disdvantages in 
comparison to relational databases and OO Databases!  Please explain 
these chacteristics with respect to what Postgresql can and cannot do.
 
Thanks for your assistance. 

With respect to the IMO very helpful reply you got from Jeff Davis on 
this topic yesterday, what is it you expect from this list? We won't 
write an essay for you you know.

Regards,
Thomas Hallgren
---(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] primary key and existing unique fields

2004-10-27 Thread Richard Huxton

Sally Sally wrote:
 This 
existing unique field will have to be a character of fixed length 
(VARCHAR(12)) because although it's a numeric value there will be 
leading zeroes. 
Plenty of people are contributing their tuppence-worth regarding the 
choice of surrogate vs natural primary key.

Can I just point out that your existing unique field is EITHER a numeric 
value OR it has a fixed number of characters - numbers don't have 
leading zeros.

If what you have is a number, then perhaps consider int8/numeric types 
and format appropriately when you display the values.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Bug or stupidity

On Wed, Oct 27, 2004 at 12:15:10AM +0200, Thomas Hallgren wrote:
 Martijn,
  Do you have a better
 suggestion, other than forbidding the currently allowed syntax?
 
 Yes I do.
 
 We agree that my second example should be disallowed since the semantics 
 of the FROM clause is different for a DELETE so the add_missing_from 
 is actually not adding to a FROM clause, it is guessing the scope for 
 the predicate. I assume the same is true for an UPDATE where there is no 
 FROM at all.

Not true, UPDATE in PostgreSQL does allow a from clause. Observe:

# \h update
Command: UPDATE
Description: update rows of a table
Syntax:
UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

Perfectly reasonable addition, but not strictly SQL standard. Also, the
scope is not guessed, it's totally unambiguous. I avoid the issue
entirly by either never using aliases, or always using aliases, hence
the issue doesn't come up, but that's me.

Anyway, I think there's a confusion in the phrase from clause. From
the server's point of view, it's the list of tables the query is
working with and this applies to all kinds of queries, DELETE, SELECT
and UPDATE alike. Internally all those queries are processed the same,
it's just what happens to the selected rows that changes. SELECT and
UPDATE allow you to explicitly list tables, DELETE doesn't. The bit
after FROM in a DELETE query is *not* the from clause by this
definition.

But I guess it comes down to to how strictly you want to follow the SQL
standard.

 My suggestion is that we rename the add_missing_from to:
 
 update_delete_autoscope
 
 and that this option has no effect on SELECT clauses. It would be more 
 or less harmless to have it enabled by default.

As pointed out above, it's not needed to update. And add_missing_from
currently has no effect on delete, so your suggested option appears to
be merely the inverse of what is already there.

 DELETE FROM first_table x
   WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo  4)
 
 The number of characters is almost the same in both statements even for 
 a very simple WHERE clause thanks to aliasing. The benefits of aliasing 
 increases as the WHERE clause gets more complicated.

The SQL standard (what I can find on the web anyway) doesn't allow an
alias there, and neither does PostgreSQL. Incidently, MS SQL server
allows the following syntax:

DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON 

The UPDATE syntax extension I mentioned above is also in MS SQL as far
as I can tell (I've never personally used it). Would adding support for
a from clause there make a difference to you?

Ref: http://www.mvps.org/access/queries/qry0022.htm

 Why confuse people with yet another syntax?

Why confuse people by changing a perfectly usable syntax, that's been
present for years (since the beginning I beleive) and generates NOTICEs
already. The difference between NOTICEs and WARNINGs is that NOTICEs
are expected, a direct consequence of the query, whereas warnings are
unexpected, change each time you run the query. By that definition it
clearly should be a NOTICE.

Anyway, this isn't going anywhere. Neither of us is going to make any
changes to the server. And the core has decided to leave it as is for
the time being. Maybe after 8.0 is released it can be revisited.

Have a nice day,
-- 
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.


pgpVAtj0zqwB2.pgp
Description: PGP signature

Re: [GENERAL] Bug or stupidity

2004-10-27 Thread Sim Zacks

I didn't see that join syntax in the documentation for delete, thanks
for pointing it out.

MS SQL Server syntax for a delete is a little less confusing, IMHO.

instead of DELETE FROM x WHERE x.a = table.a and x.b  table.b and table.c = 4;
they have DELETE x FROM x join table on x.a = table.a and x.b  table.b and table.c = 4

the table being deleted from is listed  separately, but you can still
have full join syntax (including outer joins) to help with the
deletion.

This is similar to the current PostGreSQL update syntax, except that
the table being updated is not part of the from and therefore can only
be connected through an inner join, not an outer join.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax



On Tue, Oct 26, 2004 at 06:21:23PM +0200, Thomas Hallgren wrote:
 Do you consider this overly complex? Compare:
 
 DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and 
 x.b  table.b and table.c = 4)
 
 to:
 
 DELETE FROM x, table WHERE x.a = table.a and x.b  table.b and table.c = 4
 
 In the latter, what is it you are deleting? Is it x or table? I'm not at 
 all in favor of listing several tables in the FROM clause of a DELETE 
 statement (that includes implicitly adding them).

The problem is that in DELETE, there is no FROM clause in the sense
there is with the other commands, the FROM keyword is used for a
different purpose. The FROM clause the tables are automatically added
to does not have an equivalent in the original SQL statement. 

I'm in favour of the status quo, exactly the current default behaviour.
That second example you give is confusing and should be disallowed. But
no-one has come up with anything better. Do you have a better
suggestion, other than forbidding the currently allowed syntax?

 Every DB interface I've used so far displays the notices
 where I can see them. This notice is one of the less useful, there
 are other more useful warnings which are much more handy to see...
  
 
 Right. Useful warnings! Seems you agree that this should be a warning, 
 not a notice.

Hmm, I consider a notice to be a warning anyway, something you should
always read. The default log level is notice anyway, so if you're
seeing warnings, you'll see the notices too...

Anyway, I think the reasoning so far is, the default stays as it is
until someone comes up with a non-confusing way of adding a real FROM
clause to DELETEs. Requiring people upgrading to add missing tables in
the FROM for SELECT and UPDATE is one thing. Asking them to rewrite
every DELETE query as a subselect is a bit too far. It would be nice
also because then you could then also use aliases.

Have a nice day,
-- 
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.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Comment on timezone and interval types

On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
 Martijn,
 I agree. One issue I can think of is that if you store each timestamp
 as a (seconds,timezone) pair, the storage requirements will balloon, 
 since timezone can be something like Australia/Sydney and this will
 be repeated for every value in the table. I don't know how to deal   
 easily with this since there is no unique identifier to timezones and
 no implicit order.
 
 The only solution I can think of is have initdb create a pg_timezones
 table which assigns an OID to each timezone it finds. Then the type can
 use that.
 
 I think this is a good solution actually, any thoughts?
 
 Using OID's is a good idea, but I think a canonical list of known
 timezone to OID mappings must be maintained and shipped with the 
 PostgreSQL core.

How can there be a canonical list of known timezones if every
operating system has it's own list. Maybe you can provide a base list,
but you have to allow for people to make their own.

 If OID's are generated at initdb time, there's a great risk that the
 OID's will differ between databases using different versions of 
 PostgreSQL. That in turn might have some negative implications for data
 exchange.

I doubt it, the OIDs would never be output. Types, triggers, functions
etc all have OIDs that never appear in any output anywhere, so why
should these. Since PostgreSQL doesn't support you to copying any part
of the raw data files between different installations, let alone
different versions, I think the issues with data exchange are not a
problem.

Have a nice day,
-- 
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.


pgpmz6mujetnK.pgp
Description: PGP signature

Re: [GENERAL]

2004-10-27 Thread Richard Huxton

[EMAIL PROTECTED] wrote:
Hello !
To kill a session i used KILL -INT PID.
This command wasn't successfull. The processus is still here when a
'ps ax' or a 'select * from pg_stat_activity'
Is there an other way to kill this process only because a web server
24/7 use postgres and cannot stop postgresql now.
From the manuals:
To terminate the postmaster normally, the signals SIGTERM, SIGINT, or 
SIGQUIT can be used. The first will wait for all clients to terminate 
before quitting, the second will forcefully disconnect all clients, and 
the third will quit immediately without proper shutdown, resulting in a 
recovery run during restart.

The utility command pg_ctl can be used to start and shut down the 
postmaster safely and comfortably.

Does pg_ctl do anything for you?
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Comment on timezone and interval types

2004-10-27 Thread Michael Glaesemann

On Oct 27, 2004, at 6:00 PM, Martijn van Oosterhout wrote:
On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
Using OID's is a good idea, but I think a canonical list of known
timezone to OID mappings must be maintained and shipped with the
PostgreSQL core.
How can there be a canonical list of known timezones if every
operating system has it's own list. Maybe you can provide a base list,
but you have to allow for people to make their own.
My understanding is that with the addition of the zic time zone data to 
the PostgreSQL server, there's no longer any need to rely on OS time 
zone data. Some areas may still use OS time zone data--I'm not sure if 
the all the niggling pieces have been converted yet. One could then 
produce a canonical list, based on the zic data.

Corrections welcome if I've misunderstood something.
Regards,
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Comment on timezone and interval types

2004-10-27 Thread Marco Ferretti

On Wed, 2004-10-27 at 09:00 +0200, Thomas Hallgren wrote:

 Using OID's is a good idea, but I think a canonical list of known 
 timezone to OID mappings must be maintained and shipped with the 
 PostgreSQL core.
 
 If OID's are generated at initdb time, there's a great risk that the 
 OID's will differ between databases using different versions of 
 PostgreSQL. That in turn will have some negative implications for data 
 exchange.
 
 Regards,
 Thomas Hallgren
 

I definitely agree with Thomas . The fact that  OIDs are generated at
initdb time really scares me since we have different versions of the
database engine running; it would really be a nightmare if the OIDs were
different from machine to machine


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] update data in different database

2004-10-27 Thread Henriksen, Jonas F

Hi, 

is it possible to write a trigger, using pl/pgSQL,  that updates tables in a different 
database than the one the trigger is called from? If it is, what is the syntax for 
calling the other database? My to databases are on the same server. 

Regards Jonas:))

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] update data in different database

2004-10-27 Thread Karsten Hilbert

 is it possible to write a trigger, using pl/pgSQL, that
 updates tables in a different database than the one the trigger
 is called from? If it is, what is the syntax for calling the
 other database? My to databases are on the same server.
One way I can think of is using dblink from the contrib/
directory. It can be used to connect to another database from
within the one you are primarily connected to.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] update data in different database

2004-10-27 Thread Richard Huxton

Henriksen, Jonas F wrote:
Hi,
is it possible to write a trigger, using pl/pgSQL,  that updates
tables in a different database than the one the trigger is called
from? If it is, what is the syntax for calling the other database? My
to databases are on the same server.
Look into the dblink package in the contrib directory of the source 
distribution (or your contrib package)

--
  Richard Huxton
  Archonet Ltd
---(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] Changing access permissions without re-starting the database

2004-10-27 Thread Carlos









Hi Forum,

Is there a way to change the database access permissions for
different IP addresses without having to re-start PostgreSQL? I often
want to preclude all IP address but one from accessing the database.
Currently, I change the pg_hba.conf file and re-start the database but I would
like to be able to do the same thing without having to re-start.



Thanks in advance for your response

Re: [GENERAL] [Fwd: Abrupt close of pgsql backend]

2004-10-27 Thread Andrew Sullivan

On Tue, Oct 26, 2004 at 08:27:31PM -0600, Scott Marlowe wrote:
 Were you running a later version, you'd have the option of logging your
 queries.  I don't think 7.1 supported that though.

It certainly did.  I can't remember the invocation.  You'd better
have a log rotator, though, if you log all your queries, because your
logs get real big real fast.

That isn't to say that moving off 7.1 isn't a really good idea.


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Changing access permissions without re-starting the database

2004-10-27 Thread Michael Glaesemann

On Oct 27, 2004, at 9:23 PM, Carlos wrote:
PostgreSQL?  I often want to preclude all IP address but one from 
accessing the database.  Currently, I change the pg_hba.conf file and 
re-start the database but I would like to be able to do the same thing 
without having to re-start.
Try pg_ctl reload instead of pg_ctl restart
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] primary key and existing unique fields

2004-10-27 Thread Bruno Wolff III

On Wed, Oct 27, 2004 at 00:10:27 +0200,
  Dawid Kuroczko [EMAIL PROTECTED] wrote:
 3. If you'll need things like last 50 keys, you can SELECT * FROM
 foo ORDER BY yourserialkey DESC LIMIT 50;

You really shouldn't be doing that if you are using sequences to generate
the key. Sequences are just guarenteed to return unique values, not to
return them in order. Because groups of sequences can be allocated to
a backend at once depending on a setting settable by a client, you can
get assignments out of order. Also for overlapping transactions what
the application means by the last 50 entries may not match what you
get when you get the 50 highest serial values.

---(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 access permissions without re-starting the database

2004-10-27 Thread Carlos Oliva

Thank you Michael.  This should work

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Glaesemann
Sent: Wednesday, October 27, 2004 9:27 AM
To: Carlos
Cc: '[EMAIL PROTECTED]'
Subject: Re: [GENERAL] Changing access permissions without re-starting the
database

On Oct 27, 2004, at 9:23 PM, Carlos wrote:

 PostgreSQL?  I often want to preclude all IP address but one from 
 accessing the database.  Currently, I change the pg_hba.conf file and 
 re-start the database but I would like to be able to do the same thing 
 without having to re-start.

Try pg_ctl reload instead of pg_ctl restart

Michael Glaesemann
grzm myrealbox com

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL]

2004-10-27 Thread f-f

Thanks for your answer!

I would like to kill only a postgres session not postmaster.
The table pg_session indicates the pid of the wrong session but kill -INT pid didn't 
stop this session.

pg_ctl works fine when i want to stop postmaster with the argument '-m immediate stop'


Message d'origine
Date: Wed, 27 Oct 2004 10:11:16 +0100
De: Richard Huxton [EMAIL PROTECTED]
A: [EMAIL PROTECTED]
Copie à: [EMAIL PROTECTED]
Sujet: Re: [GENERAL]

[EMAIL PROTECTED] wrote:
 Hello !
 
 To kill a session i used KILL -INT PID.
 
 This command wasn't successfull. The processus is still here when a
 'ps ax' or a 'select * from pg_stat_activity'
 
 Is there an other way to kill this process only because a web server
 24/7 use postgres and cannot stop postgresql now.

 From the manuals:
To terminate the postmaster normally, the signals SIGTERM, SIGINT, or 
SIGQUIT can be used. The first will wait for all clients to terminate 
before quitting, the second will forcefully disconnect all clients, and 
the third will quit immediately without proper shutdown, resulting in a 
recovery run during restart.

The utility command pg_ctl can be used to start and shut down the 
postmaster safely and comfortably.

Does pg_ctl do anything for you?

-- 
   Richard Huxton
   Archonet Ltd

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Array values and foreign keys

2004-10-27 Thread Daniel Savard

Is there a way to define a foreign key for the values of an array?

For example, if table T1 is having a colum A which is defined as integer[] can I 
define a foreign key in order to force each value to be a pointer (index) to a row in 
a table T2?

If yes, how? Is there any shortcomings to this approach?

Thanks,

Daniel Savard

---
Daniel Savard
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Resource temporarily unavailable

2004-10-27 Thread Ben-Nes Michael

Hi everyone

From time to time i get the following error:
could not fork new process for connection: Resource temporarily unavailable

im using Postgres 7.4.5, with kernel 2.6.2

my conf is:

max_connections = 400

and system is:

www3:/etc/postgresql# ulimit -a
core file size(blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size   (kbytes, -m) unlimited
open files(-n) 1024
pipe size  (512 bytes, -p) 8
stack size(kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes(-u) 256
virtual memory(kbytes, -v) unlimited

im totaly bogus, any ideas ?

Thanks
--
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] ABRUPT CLOSURE OF POSTGRESQL SOCKET

2004-10-27 Thread Deepa K

Hi,
I am running postgresql 7.1.3 in RedHat Linux 7.2. From an
external C application, three connections are established with
postmaster (it
is started with -i option) through unix sockets. Two times I received
EPIPE error when trying to send a query to postmaster. This is because of
the abrupt closure of pgsql backend.

The query sent is formed dynamically and hence I'm unable to get
the query whose processing may have led to such abrupt closure. But
this application is working for 2 years till now without any such
problems. So I don't really suspect the query.

The postgresql todo lists that usage of views can lead to
such abrupt closure. But there are no views used in my application.
The another possbile reason for abnormal closure that I understand
is if the hard disk is full. But this is also not the case.

Pgsql log shows the following message :
=

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
NOTICE:  RelationBuildDesc: can't open pg_trigger: Too many open files in
system
pq_recvbuf: unexpected EOF on client connection
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 28312 exited with status 11
Server process (pid 28312) exited with status 11 at Wed Oct 27 17:00:02 2004
Terminating any active server processes...
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 28250
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 28248
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 28204
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 28162
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 20173
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 20171
/usr/bin/postmaster: CleanupProc: sending SIGQUIT to process 20170
/usr/bin/postmaster: CleanupProc: pid 28162 exited with status 0
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: ServerLoop:handling reading 5
/usr/bin/postmaster: ServerLoop:handling reading 5
NOTICE:  Message from PostgreSQL backend:
The Postmaster has informed me that some other backend  died
abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am   going to
terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
/usr/bin/postmaster: reaping dead processes...

Coming to the question,
==

a. What could be the reason for abnormal closure of postmaster
   socket?
b. How can the application detect such a abnormal closure of
   socket?

-- 
regards,
Deepa K

---(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] Comment on timezone and interval types

Michael Glaesemann [EMAIL PROTECTED] writes:
 On Oct 27, 2004, at 6:00 PM, Martijn van Oosterhout wrote:
 How can there be a canonical list of known timezones if every
 operating system has it's own list. Maybe you can provide a base list,
 but you have to allow for people to make their own.

 My understanding is that with the addition of the zic time zone data to 
 the PostgreSQL server, there's no longer any need to rely on OS time 
 zone data.

Correct, but it is still the case that different installations will need
to have slightly different timezone lists.  Consider for example the
australian_timezones kluge we have now, and consider that there are
several known cases of zone name conflicts that are not covered by
australian_timezones (the one I remember at the moment is IST which both
the Israelis and the Indians use; but I think there are some others).
I think the most reasonable way to solve this will be to invent a
configuration file that lets people list the zone abbreviations they
want to use and the corresponding UTC offsets.  We will need a mapping
method that can cope with changes in such a file.

But having said that, I concur with Martijn that there is no problem,
because the OIDs (or whatever numeric ID we use) are inside the database
and will never be visible outside it.  There is no more portability risk
here than there is in using platform-native byte order in integers.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Resource temporarily unavailable

Ben-Nes Michael [EMAIL PROTECTED] writes:
 From time to time i get the following error:
 could not fork new process for connection: Resource temporarily unavailable

This generally means that the kernel has run out of memory.

 my conf is:
 max_connections = 400

Perhaps that is overly optimistic for your available hardware.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] [NOVICE] ABRUPT CLOSURE OF POSTGRESQL SOCKET

Deepa K [EMAIL PROTECTED] writes:
 I am running postgresql 7.1.3 in RedHat Linux 7.2.

You do realize that both your database and your OS are ancient versions
with many known bugs?

 NOTICE:  RelationBuildDesc: can't open pg_trigger: Too many open files in
 system

I think this is probably the source of the core dump --- the code
probably isn't prepared to recover from that.  You'll need to increase
the kernel limit on number of open files.  In recent PG versions you
could alternatively decrease max_files_per_process, but I'm pretty sure
7.1 did not have that setting ...

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

[GENERAL] Exact or less specific match ?

2004-10-27 Thread NTPT

Hi.
i  have table like this:
create table my_data (
cond_1 int8,cond_2 varchar(),cond_3  cond_n whatrver ,data text)
This table represents a simple tree structure with known max level (n) . 
This table is filled with data, but branches have not a same depth.

Now I need to select from table
select data from my_data where cond_1=x AND cond_2='blah' And cond_3= . 
AND cond_n=whatewer

But, this tree have not a same depth in all his branches. So I need to 
select Exact match, and, if the exact match is not possible  (ie if there is 
not line that fit WHERE  condition ),

to select with WHERE  cond_1=x AND cond_2='blah' And cond_3= . AND 
cond_(n-1)=whatewer and so on until the 'data' is not empty or  top of the 
tree reached (ie if not match, find data from upper node of the tree).

I know, that similar effects can be reached with  COALESCE,
select coalesce ((select data from my_data where cond_1=x AND cond_2='blah' 
And cond_3= . AND cond_n=whatewer),(select data from my_data where 
cond_1=x AND cond_2='blah' And cond_3= . AND cond_(n-1)=whatewer) 
,...,(select data from my_data where cond_1=x ))

but i think it is not ideal, because it needs to perform a N  subselects, 
what can eat a lot of machine time...


is there some other way to do exact or less specific match ?
Thank you. please execuse my bad english 

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Reasoning behind process instead of thread based arch?

2004-10-27 Thread nd02tsk

Hello!

I have a couple of final ( I hope, for your sake ) questions regarding
PostgreSQL.

I understand PostgreSQL uses processes rather than threads. I found this
statement in the archives:

The developers agree that multiple processes provide
more benefits (mostly in stability and robustness) than costs (more
connection startup costs). The startup costs are easily overcome by
using connection pooling.


Please explain why it is more stable and robust? More from the above
statement:

Also, each query can only use one processor; a single query can't be
executed in parallel across many CPUs. However, several queries running
concurrently will be spread across the available CPUs.

And it is because of the PostgreSQL process architecture that a query
can't be executed by many CPU:s right? Although I wonder if this is the
case in MySQL. It only says in their manual that each connection is a
thread.

Also, MySQL has a library for embedded aplications, the say:

We also provide MySQL Server as an embedded multi-threaded library that
you can link into your application to get a smaller, faster,
easier-to-manage product.

Do PostgreSQL offer anything similar?

Thank you for your time.

Tim




---(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] Psycopg difficulty...

2004-10-27 Thread Jerry LeVan

Psycopg and probably PySQL seem to have decided to return
a float type when libpq returns a string tagged with
the numeric property.
This can cause pretty printing problems when generating
reports. ( I like all of my decimal points to line up.)
For example in my python based browser if I try the
command
select 123.4500
I will get
123.45
and I have lost the scale. More over the description
attribute for the cursor will contain 2^16 -1 for the
precision and scale so that there is no way to recover
the lost scale factor.
For regular columns that have been declared with
a numeric(9,2) attribute (as an example). then a select
will leave in the cursor column description the proper
values for the precision and scale and I can generate
correct looking reports.
However for computed columns in something like
SELECT oid, *, (SELECT sum(amount)
 FROM checks  WHERE x.oid = oid ) AS total
FROM checks x
ORDER BY  date,oid ;
Assuming that amount is declared with numeric(9,2)
the total column will have dropped any trailing
zeros (ie 19.70 will display as 19.7).
Basically computed columns do not furnish any
info as to scale and precision
The only way I can see to get around the problem is
to cast the total column with the desired precision.
SELECT oid, *, (SELECT sum(amount)::numeric(9,2)
 FROM checks  WHERE x.oid = oid ) AS total
FROM checks x
ORDER BY  date,oid ;
But I don't have to do the casting using libpq,pgsql,tcl,
or perl-dbi interface.
It seems like the right thing to do is to return the
string value and let the user do the formatting
like all of the other interfaces do...
Jerry
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Newbie question about escaping in a function

2004-10-27 Thread Thomas F.O'Connell

Try using EXECUTE.
http://www.postgresql.org/docs/7.4/static/plpgsql- 
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 26, 2004, at 11:57 AM, Naeem Bari wrote:
I have a simple function defined thusly:

CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4,  
varchar)

 RETURNS timestamp AS
'
DECLARE
 tdat timestamp;
 rdat timestamp;
BEGIN
 IF ($1 IS NULL) THEN
 TDAT := NOW();
 ELSE
 TDAT := $1;
 END IF;

 select tdat + interval ''$2 $3'' into rdat;
 return rdat;
END;
'
 LANGUAGE 'plpgsql' VOLATILE;

The problem is the interval part. How do I tell the bugger to use the  
second and third params as input to interval? I have tried different  
ways of escaping, from \$2 $3\ to $2 $3 and everything else in  
between, it just doesnt like it.


Help! J

Thanks,
naeem
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Array values and foreign keys

2004-10-27 Thread Pierre-Frdric Caillaud


	You can't express it directly with a CHECK constraint but you can do this  
:

	- add CHECK( test_array( yourcolumn )) in your table definition
	- create function test_array which takes an array and looks if all its  
elements are in your table T2, I do something like comparing the length of  
the array to SELECT count(1) FROM T2 WHERE key IN array
	You can do it other ways but you'll have to use a function.

On Wed, 27 Oct 2004 10:19:02 -0400, Daniel Savard [EMAIL PROTECTED] wrote:
Is there a way to define a foreign key for the values of an array?
For example, if table T1 is having a colum A which is defined as  
integer[] can I define a foreign key in order to force each value to be  
a pointer (index) to a row in a table T2?

If yes, how? Is there any shortcomings to this approach?
Thanks,
Daniel Savard
---
Daniel Savard
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Reasoning behind process instead of thread based

2004-10-27 Thread Doug McNaught

[EMAIL PROTECTED] writes:

 The developers agree that multiple processes provide
 more benefits (mostly in stability and robustness) than costs (more
 connection startup costs). The startup costs are easily overcome by
 using connection pooling.
 

 Please explain why it is more stable and robust? 

Because threads share the same memory space, a runaway thread can
corrupt the entire system by writing to the wrong part of memory.
With separate processes, the only data that is shared is that which is
meant to be shared, which reduces the potential for such damage. 

 Also, each query can only use one processor; a single query can't be
 executed in parallel across many CPUs. However, several queries running
 concurrently will be spread across the available CPUs.

 And it is because of the PostgreSQL process architecture that a query
 can't be executed by many CPU:s right?

There's no theoretical reason that a query couldn't be split across
multiple helper processes, but no one's implemented that feature--it
would be a pretty major job.

 Also, MySQL has a library for embedded aplications, the say:

 We also provide MySQL Server as an embedded multi-threaded library that
 you can link into your application to get a smaller, faster,
 easier-to-manage product.

 Do PostgreSQL offer anything similar?

No.  See the archives for extensive discussion of why PG doesn't do
this.

-Doug

---(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] Reasoning behind process instead of thread based

2004-10-27 Thread Scott Marlowe

On Wed, 2004-10-27 at 09:56, [EMAIL PROTECTED] wrote:
 Hello!
 
 I have a couple of final ( I hope, for your sake ) questions regarding
 PostgreSQL.
 
 I understand PostgreSQL uses processes rather than threads. I found this
 statement in the archives:
 
 The developers agree that multiple processes provide
 more benefits (mostly in stability and robustness) than costs (more
 connection startup costs). The startup costs are easily overcome by
 using connection pooling.
 
 
 Please explain why it is more stable and robust? More from the above
 statement:

This question shows up every 6 months or so.  You might wanna search the
archives (I use google to do that, but YMMV with the postgresql site's
search engine.)

Basically, there are a few issues with threading that pop up their ugly
heads.  One:  Not all OSes thread libraries are created equal.  There
was a nasty bug in one of the BSDs that causes MySQL to crash a couple
years ago that drove them nuts.  So programming a threaded
implementation means you have the vagaries of different levels of
quality and robustness of thread libraries to deal with.  Two:  If a
single process in a multi-process application crashes, that process
alone dies.  The buffer is flushed, and all the other child processes
continue happily along.  In a multi-threaded environment, when one
thread dies, they all die.  Three:  Multi-threaded applications can be
prone to race conditions that are VERY hard to troubleshoot, especially
if they occur once every million or so times the triggering event
happens.

On some operating systems, like Windows and Solaris, processes are
expensive, while threads are cheap, so to speak.  this is not the case
in Linux or BSD, where the differences are much smaller, and the
multi-process design suffers no great disadvantage.

 Also, each query can only use one processor; a single query can't be
 executed in parallel across many CPUs. However, several queries running
 concurrently will be spread across the available CPUs.
 
 And it is because of the PostgreSQL process architecture that a query
 can't be executed by many CPU:s right? Although I wonder if this is the
 case in MySQL. It only says in their manual that each connection is a
 thread.

Actually, if it were converted to multi-threaded tomorrow, it would
still be true, because the postgresql engine isn't designed to split off
queries into constituent parts to be executed by seperate threads or
processes.  Conversely, if one wished to implement it, one could likely
patch postgresql to break up parts of queries to different child
processes of the current child process (grand child processes so to
speak) that would allow a query to hit multiple CPUs.

 Also, MySQL has a library for embedded aplications, the say:
 
 We also provide MySQL Server as an embedded multi-threaded library that
 you can link into your application to get a smaller, faster,
 easier-to-manage product.
 
 Do PostgreSQL offer anything similar?

No, because in that design, if your application crashes, so does, by
extension, your database.  Now, I'd argue that if I had to choose
between which database to have crash in the middle of transactions, I'd
pick PostgreSQL, it's generally considered a bad thing to have a
database crash mid transaction.  PostgreSQL is more robust about crash
recovery, but still...

That's another subject that shows up every x months, an embedded version
of PostgreSQL. Basically, the suggestion is to use something like
SQLlite, which is built to be embedded, and therefore has a much lower
footprint than PostgreSQL could ever hope to achieve.  No one wants
their embedded library using up gobs of RAM and disk space when it's
just handling one thread / process doing one thing.  It's like
delivering Pizzas with a Ferrari, you could do it, it just eouldn't make
a lot of sense.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Newbie question about casting literals - oracle/postgres

2004-10-27 Thread Dianne Yumul

hi,
I think it works for me.  what version of postgres do you have? maybe  
you just need to upgrade : )

test=# select cust_id, 'TEST' as test, cust_address from customers;
  cust_id   | test | cust_address
+--+--
 11 | TEST | 200 Maple Lane
 12 | TEST | 333 South Lake Drive
 13 | TEST | 1 Sunny Place
 14 | TEST | 829 Riverside Drive
 15 | TEST | 4545 53rd Street
 16 | TEST |
(6 rows)
test=# select version();
version
 

 PostgreSQL 7.3.7 on powerpc-unknown-linux-gnu, compiled by GCC gcc  
(GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r4, propolice)
(1 row)

hth,
dianne
On Oct 26, 2004, at 6:24 PM, Naeem Bari wrote:
Ok,

I have a query that runs fine in oracle:

 select driver_id, 'GREEN' as color, pos_date, pos_lat, pos_lon
 from driver_pos
 where driver_id = 1
 order by pos_date

The only way this works in postgres is by casting GREEN to text  
using GREEN::text


The problem is then this does not work with oracle. Since my software  
has to support both databases, I am left in a bit of a bind. Any ideas  
on how to make postgres accept GREEN as text without my having to  
spell it out by casting?


Thanks!
naeem

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Reasoning behind process instead of thread based

2004-10-27 Thread Joshua D. Drake

On some operating systems, like Windows and Solaris, processes are
expensive, while threads are cheap, so to speak. this is not the case
in Linux or BSD, where the differences are much smaller, and the
multi-process design suffers no great disadvantage.
Even on Windows or Solaris you can use techniques like persistent
connections or connection pooling to eliminate the process overhead.

Actually, if it were converted to multi-threaded tomorrow, it would
still be true, because the postgresql engine isn't designed to split off
queries into constituent parts to be executed by seperate threads or
processes. Conversely, if one wished to implement it, one could likely
patch postgresql to break up parts of queries to different child
processes of the current child process (grand child processes so to
speak) that would allow a query to hit multiple CPUs.
I would be curious as to what this would actually gain. Of course there
are corner cases but I rarely find that it is the CPU that is doing all
the work, thus splitting the query may not do you any good.

In theory I guess being able to break it up and execute it to different
CPUs could cause the results to process faster, but I wonder if it would
be a large enough benefit to even notice?
We also provide MySQL Server as an embedded multi-threaded library that
you can link into your application to get a smaller, faster,
easier-to-manage product.
Do PostgreSQL offer anything similar?
No, it isn't really designed to do that. Like Oracle also is not a
database you would Embed.

pick PostgreSQL, it's generally considered a bad thing to have a
database crash mid transaction. PostgreSQL is more robust about crash
recovery, but still...
That's another subject that shows up every x months, an embedded version
of PostgreSQL. Basically, the suggestion is to use something like
SQLlite, which is built to be embedded, and therefore has a much lower
footprint than PostgreSQL could ever hope to achieve. No one wants
their embedded library using up gobs of RAM and disk space when it's
just handling one thread / process doing one thing. It's like
delivering Pizzas with a Ferrari, you could do it, it just eouldn't make
a lot of sense.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: [GENERAL] Reasoning behind process instead of thread based

2004-10-27 Thread nd02tsk

Two:  If a
 single process in a multi-process application crashes, that process
 alone dies.  The buffer is flushed, and all the other child processes
 continue happily along.  In a multi-threaded environment, when one
 thread dies, they all die.


So this means that if a single connection thread dies in MySQL, all
connections die?

Seems rather serious. I am doubtful that is how they have implemented it.




---(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] page locking? too many btree indexes...

On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
 Can too many btree indexes cause page level locking?  

Yes, too many btree indexes can, as can a single btree index.

 I am experiencing locking related on two tables.  Each has several
 indexes on it (4 or more).  One table is frequently updated (20%),
 occasional inserts(10%), and many reads (70%) and the other has many
 inserts and reads every 20 secs.

Most likely, your problem is not related to the indexes.  Yes, there is
page-level exclusive locking on the indexes when there's insert or
delete operations going on, but they don't cause deadlocks.  The likely
cause of your problem is foreign key relationships.  Those are
implemented using row-level exclusive locking, and they can (and often
do) cause deadlocks.

Do you have any foreign keys defined?

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito (Kim, Kipling)


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Array values and foreign keys

On Wed, Oct 27, 2004 at 05:59:46PM +0200, Pierre-Fr?d?ric Caillaud wrote:
 
   - add CHECK( test_array( yourcolumn )) in your table definition
   - create function test_array which takes an array and looks if all 
   its  elements are in your table T2, I do something like comparing the 
 length of  the array to SELECT count(1) FROM T2 WHERE key IN array

This provides only partial foreign key checking: depending on how
the application works, you might also need to ensure that updates
and deletes in T2 don't break the references in T1.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Newbie question about casting literals - oracle/postgres

On Tue, Oct 26, 2004 at 08:24:56PM -0500, Naeem Bari wrote:

 The only way this works in postgres is by casting 'GREEN' to text using
 'GREEN'::text
  
 The problem is then this does not work with oracle.

So use a standards-conformant cast, like

cast('GREEN' as text)

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes. (http://slashdot.org/comments.pl?sid=44793cid=4647152)


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Reasoning behind process instead of thread based

On Wed, Oct 27, 2004 at 07:47:03PM +0200, [EMAIL PROTECTED] wrote:
 Two:  If a
  single process in a multi-process application crashes, that process
  alone dies.  The buffer is flushed, and all the other child processes
  continue happily along.  In a multi-threaded environment, when one
  thread dies, they all die.
 
 
 So this means that if a single connection thread dies in MySQL, all
 connections die?
 
 Seems rather serious. I am doubtful that is how they have implemented it.

It's part of the design of threads. If a thread does an invalid lookup,
it's the *process* (ie all threads) that receives the signal and it's
the *process* that dies.

Just like a SIGSTOP stops all threads and a SIGTERM terminates them
all. Signals are shared between threads. Now, you could ofcourse catch
these signals but you only have one address space shared between all
the threads, so if you want to exit to get a new process image (because
something is corrupted), you have to close all connections.

And indeed, the one MySQL server I can see is four threads. Nasty.
-- 
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.


pgpNTnEsDLEvN.pgp
Description: PGP signature

Re: [GENERAL] Reasoning behind process instead of thread based

[EMAIL PROTECTED] wrote:
Two:  If a
single process in a multi-process application crashes, that process
alone dies.  The buffer is flushed, and all the other child processes
continue happily along.  In a multi-threaded environment, when one
thread dies, they all die.

So this means that if a single connection thread dies in MySQL, all
connections die?
Seems rather serious. I am doubtful that is how they have implemented it.
That all depends on how you define crash. If a thread causes an 
unhandled signal to be raised such as an illegal memory access or a 
floating point exception, the process will die, hence killing all 
threads. But a more advanced multi-threaded environment will install 
handlers for such signals that will handle the error gracefully. It 
might not even be necesarry to kill the offending thread.

Some conditions are harder to handle than others, such as stack overflow 
and out of memory, but it can be done. So to state that multi-threaded 
environments in general kills all threads when one thread chrashes is 
not true. Having said that, I have no clue as to how advanced MySQL is 
in this respect.

Regards,
Thomas Hallgren
---(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] what could cause inserts getting queued up and db locking??

On Tue, Oct 26, 2004 at 03:10:04PM -0400, Brian Maguire wrote:
 Thanks.  We do have it set to 15 mb.  I would think that 16 mb would not
 make a big difference.  Do you have any other ideas?  

Huh?  No, you have it set to 15 *segments*, each of which is 16 MB long.
Maybe setting it higher will help you, but maybe it won't, depending on
wheter there's a checkpoint run when the system is in a somewhat idle
state.

Oh, you may also want to increase checkpoint_timeout, so that
checkpoints are run less frequently.

But then, checkpoints will be run less frequently and they will take
longer.  If you do have idle or low-load periods, try to run a
checkpoint when they occur.  Else you may need faster disks ...

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
This is a foot just waiting to be shot(Andrew Dunstan)


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Bug: 8.0 beta1 either view optimization or pgdump/pgrestore

Sim Zacks [EMAIL PROTECTED] writes:
 /*Here is the virtual table I mentioned using select * on a join*/
 (select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID 
 = c.PricingGroupID) groups

Okay, evidently the problem is that you have identically named
columns in the two tables PackagePricingGroups and PricingGroups,
so the groups join contains duplicate column names.  (AFAICS this
is not illegal per the SQL spec, but I wonder whether it shouldn't
be, because it's very hard to avoid ambiguity.)

I've tweaked ruleutils.c for 8.0 so that the display looks like

...
  LEFT JOIN ( SELECT b.packagepricinggroupid, b.pricinggroupid,
  b.packageid, b.createuserid, b.createdate, b.modifyuserid,
  b.modifydate, c.pricinggroupid, c.description,
  c.supplierid, c.baseprice, c.priceperpin, c.currencyid,
  c.createuserid, c.createdate, c.modifyuserid, c.modifydate
  FROM packagepricinggroups b
   JOIN pricinggroups c ON
...

which solves this particular issue.  I'm not sure a complete solution is
possible in the presence of duplicate column names; perhaps you should
modify the query to avoid that.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Reasoning behind process instead of thread based

2004-10-27 Thread Dann Corbit

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of 
 Thomas Hallgren
 Sent: Wednesday, October 27, 2004 11:16 AM
 To: [EMAIL PROTECTED]
 Subject: Re: [GENERAL] Reasoning behind process instead of 
 thread based

 [EMAIL PROTECTED] wrote:
 Two:  If a
 single process in a multi-process application crashes, that process 
 alone dies.  The buffer is flushed, and all the other child 
 processes 
 continue happily along.  In a multi-threaded environment, when one 
 thread dies, they all die.

  So this means that if a single connection thread dies in MySQL, all 
  connections die?

  Seems rather serious. I am doubtful that is how they have 
 implemented 
  it.

 That all depends on how you define crash. If a thread causes an 
 unhandled signal to be raised such as an illegal memory access or a 
 floating point exception, the process will die, hence killing all 
 threads. But a more advanced multi-threaded environment will install 
 handlers for such signals that will handle the error gracefully. It 
 might not even be necesarry to kill the offending thread.

 Some conditions are harder to handle than others, such as 
 stack overflow 
 and out of memory, but it can be done. So to state that 
 multi-threaded 
 environments in general kills all threads when one thread chrashes is 
 not true. Having said that, I have no clue as to how advanced 
 MySQL is 
 in this respect.

There are clear advantages to separate process space for servers.
1.  Separate threads can stomp on each other's memory space.  (e.g.
imagine a wild, home-brew C function gone bad).
2.  Separate processes can have separate user ids, and [hence] different
rights for file access.  A threaded server will have to either be
started at the level of the highest user who will attach or will have to
impersonate the users in threads.  Impersonation is very difficult to
make portable.
3.  Separate processes die when they finish, releasing all resources to
the operating system.  Imagine a threaded server with a teeny-tiny
memory leak, that stays up 24x7.  Eventually, you will start using disk
for ram, or even use all available disk and simply crash.

Threaded servers have one main advantate:
Threads are lightweight processes and starting a new thread is faster
than starting a new executable.

The thread advantage can be partly mitigated by pre-launching a pool of
servers.

---(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] QMail

2004-10-27 Thread Eric

Is there something to interface postgreSQL with QMail to store mails in
pgsql instead of using mbox or maildir?

Or maybe it's not a good idea to do that?

I think there is some adavantages...



---(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] QMail

2004-10-27 Thread Steven Klassen

* Eric [EMAIL PROTECTED] [2004-10-27 14:14:25 -0400]:

 Is there something to interface postgreSQL with QMail to store mails
 in pgsql instead of using mbox or maildir?

This looks informative:

http://qmail-sql.digibel.be/

-- 
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication  Support Services, (503) 667-4564

---(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] Array values and foreign keys

2004-10-27 Thread Daniel Savard

Le mer 27/10/2004 à 11:59, Pierre-Frédéric Caillaud a écrit :
 
 
   You can't express it directly with a CHECK constraint but you can do this  
 :
 
   - add CHECK( test_array( yourcolumn )) in your table definition
   - create function test_array which takes an array and looks if all its  
 elements are in your table T2, I do something like comparing the length of  
 the array to SELECT count(1) FROM T2 WHERE key IN array
   You can do it other ways but you'll have to use a function.
 
 

Fine. I got it right after fiddling a little bit. The function is
something like:

CREATE FUNCTION test_array (smallint[]) RETURNS bool AS '
   select case when count(1) = array_upper($1,1) then true
   else false
  end from t2 where cle = any($1);
' LANGUAGE SQL;

It compares the length of the array to the number of elements actually
found in the reference table.

-- 

===
Daniel Savard

===



---(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

Dann,
I'm not advocating a multi-threaded PostgreSQL server (been there, done 
that :-). But I still must come to the defense of multi-threaded systems 
in general.

You try to convince us that a single threaded system is better because 
it is more tolerant to buggy code. That argument is valid and I agree, a 
multi-threaded environment is more demanding in terms of developer 
skills and code quality.

But what if I don't write crappy code or if I am prepared to take the 
consequences of my bugs, what then? Maybe I really know what I'm doing 
and really want to get the absolute best performance out of my server.

There are clear advantages to separate process space for servers.
1.  Separate threads can stomp on each other's memory space.  (e.g.
imagine a wild, home-brew C function gone bad).
Not all servers allow home-brewed C functions. And even when they do, 
not all home-brewers will write crappy code. This is only a clear 
advantage when buggy code is executed.

2.  Separate processes can have separate user ids, and [hence] different
rights for file access.  A threaded server will have to either be
started at the level of the highest user who will attach or will have to
impersonate the users in threads.  Impersonation is very difficult to
make portable.
Yes, this is true and a valid advantage if you ever want access external 
and private files. Such access is normally discouraged though, since you 
are outside of the boundaries of your transaction.

3.  Separate processes die when they finish, releasing all resources to
the operating system.  Imagine a threaded server with a teeny-tiny
memory leak, that stays up 24x7.  Eventually, you will start using disk
for ram, or even use all available disk and simply crash.
Sure, but a memory leak is a serious bug and most leaks will have a 
negative impact on single threaded systems as well. I'm sure you will 
find memory leak examples that are fatal only in a multi-threaded 24x7 
environment but they are probably very few overall.

Threaded servers have one main advantate:
Threads are lightweight processes and starting a new thread is faster
than starting a new executable.
A few more from the top of my head:
1. Threads communicate much faster than processes (applies to locking 
and parallel query processing).
2. All threads in a process can share a common set of optimized query plans.
3. All threads can share lots of data cached in memory (static but 
frequently accessed tables etc.).
4. In environments built using garbage collection, all threads can share 
the same heap of garbage collected data.
5. A multi-threaded system can apply in-memory heuristics for self 
adjusting heaps and other optimizations.
6. And lastly, my favorite; a multi-threaded system can be easily 
integrated with, and make full use of, a multi-threaded virtual 
execution environment such as a Java VM.
...

Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Reasoning behind process instead of thread based arch?

On Wed, Oct 27, 2004 at 05:56:16PM +0200, [EMAIL PROTECTED] wrote:
 
 I understand PostgreSQL uses processes rather than threads. I found this
 statement in the archives:
 
 The developers agree that multiple processes provide
 more benefits (mostly in stability and robustness) than costs (more
 connection startup costs). The startup costs are easily overcome by
 using connection pooling.
 
 Please explain why it is more stable and robust?

I can't speak for the developers, but here are my thoughts:

A critical problem in a thread could terminate the entire process
or corrupt its data.  If the database server were threaded, such
problems would affect the entire server.  With each connection
handled by a separate process, a critical error is more likely to
affect only the connection that had the problem; the rest of the
server survives unscathed.

 Also, each query can only use one processor; a single query can't be
 executed in parallel across many CPUs. However, several queries running
 concurrently will be spread across the available CPUs.
 
 And it is because of the PostgreSQL process architecture that a query
 can't be executed by many CPU:s right? Although I wonder if this is the
 case in MySQL. It only says in their manual that each connection is a
 thread.

I don't know if MySQL can use multiple threads for a single query;
it might simply be using one thread per connection instead of a one
process per connection.  If that's the case, then queries executed
by a particular connection are still single-threaded, the same as
in PostgreSQL.

A database that uses a separate process for each connection could
still employ multiple threads within each process if somebody could
figure out a way to distribute a query amongst the threads.  I don't
know what the PostgreSQL developers' thoughts on that are.

A disadvantage of threads is that some systems (e.g., FreeBSD 4)
implement threads in userland and threads don't take advantage of
multiple CPUs.  On such systems, using multiple processes better
employs additional CPUs.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Bug or stupidity

Martijn,
I realize that the change I'm proposing might be too complex to be added
in the upcoming 8.0 release. I do find this discussion interesting
though, so please bear with me while I try to tie up some loose ends.
UPDATE [ ONLY ] table SET col = expression [, ...]
   [ FROM fromlist ]
   [ WHERE condition ]
Perfectly reasonable addition, but not strictly SQL standard. Also, the
scope is not guessed, it's totally unambiguous.
Ok, bad choice of words. It's not guessed, and I agree, this is
perfectly reasonable.
Anyway, I think there's a confusion in the phrase from clause.
There's no confusion. I fully understand the differences. That's why
think that the term 'add_missing_from' is misleading. From a strict
syntax point of view it implies expansion to the statement we both
agreed should be disallowed. The fact that it doesn't actually add a
missing from but rather expands the scope for the predicate is somewhat
confusing. Hence my suggestion that the variable is renamed.
But I guess it comes down to to how strictly you want to follow the SQL
standard.
 

I think it's OK to deviate from the standard and add features. My whole
argument in this thread is based on the fact that PostgreSQL adds tables
to the FROM clause of a SELECT which may produce incorrect results and
that this magic is performed by default.
My suggestion is that we rename the add_missing_from to:
update_delete_autoscope
and that this option has no effect on SELECT clauses. It would be more 
or less harmless to have it enabled by default.
   

As pointed out above, it's not needed to update. And add_missing_from
currently has no effect on delete, so your suggested option appears to
be merely the inverse of what is already there.
 

What I was trying to say is that: a) since the 'add_missing_from'
affects the predicate scope for DELETE's, UPDATE's, and SELECT's, and
since those statements have different ways of expressing this scope, the
current choice of name is a bit confusing and b) it would be nice if the
variable affected DELETE and UPDATE scopes only. Now you point out that
an UPDATE can have a FROM clause, so let me revise my suggestion and
instead say:
1. Let's add a variable named autoscope_for_delete that is enabled by
default and only affects the scope of a DELETE predicate. We do this to
maintain backward compatibility.
2. Let's change so that add_missing_from is disabled by default and
doesn't affect the DELETE statement at all.
3. The autoscope_for_delete will use generate notices and
add_missing_from will generate warnings.
DELETE FROM first_table x
 WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo  4)
   

The SQL standard (what I can find on the web anyway) doesn't allow an
alias there, and neither does PostgreSQL.
The SQL 2003 draft I have states:
delete statement: searched ::=
   DELETE FROM target table [ [ AS ] correlation name ]
   [ WHERE search condition ]
whereas SQL 3 is more elaborated:
table reference ::=
 table name [ [ AS ] correlation name
 [ left paren derived column list right paren ] ]
   | derived table [ AS ] correlation name
 [ left paren derived column list right paren ]
   | joined table
delete statement: searched ::=
   DELETE FROM table reference
 [ WHERE search condition ]
Perhaps PostgreSQL should adopt this?
Incidently, MS SQL server allows the following syntax:
DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON 
The UPDATE syntax extension I mentioned above is also in MS SQL as far
as I can tell (I've never personally used it). Would adding support for
a from clause there make a difference to you?
 

I'm happy as long as the 'add_missing_from' is disabled or changed so
that it doesn't affect SELECT. And yes, this extension looks good.
Perhaps consider changing the second FROM to USING (mimicking MySQL
instead of MS SQL server). I think it would lessen the risk of
introducing ambiguities in the parser (and it looks better than repeated
FROM's).
Regards,
Thomas Hallgren

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Reasoning behind process instead of thread based

On Wed, Oct 27, 2004 at 10:07:48PM +0200, Thomas Hallgren wrote:
 Threaded servers have one main advantate:
 Threads are lightweight processes and starting a new thread is faster
 than starting a new executable.
 
 A few more from the top of my head:

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.

 1. Threads communicate much faster than processes (applies to locking 
 and parallel query processing).
 2. All threads in a process can share a common set of optimized query plans.

PostgreSQL could do this too, but I don't think anyone's looked into
sharing query plans, probably quite difficult.

 3. All threads can share lots of data cached in memory (static but 
 frequently accessed tables etc.).

Table data is already shared. If two backends are manipulating the same
table, they can lock directly via shared memory rather than some OS
primitive.

 4. In environments built using garbage collection, all threads can share 
 the same heap of garbage collected data.
 5. A multi-threaded system can apply in-memory heuristics for self 
 adjusting heaps and other optimizations.
 6. And lastly, my favorite; a multi-threaded system can be easily 
 integrated with, and make full use of, a multi-threaded virtual 
 execution environment such as a Java VM.

I can't really comment on these.

I think PostgreSQL has nicely combined the benefits of shared memory
with the robustness of multiple processes...
-- 
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.


pgpCwesMuxN3P.pgp
Description: PGP signature

Re: [GENERAL] Reasoning behind process instead of thread based

Martijn van Oosterhout [EMAIL PROTECTED] writes:
 ... Signals are shared between threads. Now, you could ofcourse catch
 these signals but you only have one address space shared between all
 the threads, so if you want to exit to get a new process image (because
 something is corrupted), you have to close all connections.

Right.  Depending on your OS you may be able to catch a signal that
would kill a thread and keep it from killing the whole process, but
this still leaves you with a process memory space that may or may not
be corrupted.  Continuing in that situation is not cool, at least not
according to the Postgres project's notions of reliable software design.

It should be pointed out that when we get a hard backend crash, Postgres
will forcibly terminate all the backends and reinitialize; which means
that in terms of letting concurrent sessions keep going, we are not any
more forgiving than a single-address-space multithreaded server.  The
real bottom line here is that we have good prospects of confining the
damage done by the failed process: it's unlikely that anything bad will
happen to already-committed data on disk or that any other sessions will
return wrong answers to their clients before we are able to kill them.
It'd be a lot harder to say that with any assurance for a multithreaded
server.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Bug or stupidity

2004-10-27 Thread Bruno Wolff III

On Wed, Oct 27, 2004 at 22:10:05 +0200,
 2. Let's change so that add_missing_from is disabled by default and
 doesn't affect the DELETE statement at all.

That is supposed to happen. My memory was that 8.0 was the release that
the default was going to change, but if not then it should be 8.1.

I don't see any great reason to change the name at this point. That is
going to just cause more problems.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Question Regarding Locks

2004-10-27 Thread Terry Lee Tucker

Greetings:

I am working on converting a transportation application from a Progress 
database to PostgreSQL. This application will be hammered by about 75 users 
at any given time. Also, depending on the operation, there are many record 
updates that occur at the trigger level. 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. I spent some time 
looking at pg_locks hoping to be able to gain information from that table so 
as to programically return something to the user. The stuff I read didn't 
look promising. The only thing I've been able to come up with is having my 
own lock table and inserting a row into it containing the table name, the 
pid, the oid of the row, the user id, and a time stamp. Of course, I'm 
searching lock table entries before selecting for update and reporting a lock 
situation to the user if I find anything. I'm just wondering if I can use 
some system table or combination of tables to provide the feed back I need. 
Does anyone have any ideas about how to accomplish this? I searched the FAQ 
and didn't find anything.

 PostgreSQL 7.3.6-RH on i386-redhat-linux-gnu, compiled by GCC 
i386-redhat-linux-gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-39)

Thanks...
-- 
Quote: 32
The world we have created is a product of our thinking. It cannot be
 changed without changing our thinking.

 --Albert Einstein

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Bug or stupidity

Bruno Wolff III [EMAIL PROTECTED] writes:
 On Wed, Oct 27, 2004 at 22:10:05 +0200,
 2. Let's change so that add_missing_from is disabled by default and
 doesn't affect the DELETE statement at all.

 That is supposed to happen. My memory was that 8.0 was the release that
 the default was going to change, but if not then it should be 8.1.

add_missing_from was only added in 7.4; the default behavior goes all
the way back because we inherited it from PostQUEL.  It's probably
premature to flip the factory default after only one release cycle,
especially given the DELETE deficiency.  A reasonable position is to
flip the default one release cycle after we fix the DELETE syntax.

It is interesting that SQL2003 allows an alias on the UPDATE or DELETE
target table; that was definitely not there in SQL99 or earlier.  We'll
want to add that, for sure, but it is just a notational convenience.

There are several threads in the archives about how to fix the DELETE
syntax, but I don't think we ever really got consensus on what keyword
to use to introduce the auxiliary FROM clause.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] interval to seconds conversion. How?

2004-10-27 Thread Denis Zaitsev

How can I calculate the number of second in some interval?  Neither
the secods part, nor the seconds after midnight...  Just the full
quantity of the seconds.  I haven't find any function.

Thanks in advance.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Reasoning behind process instead of thread based

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.

2. All threads in a process can share a common set of optimized query plans.

PostgreSQL could do this too, but I don't think anyone's looked into
sharing query plans, probably quite difficult.
Perhaps. It depends on the design. If the plans are immutable once 
generated, it should not be that difficult. But managing the mutable 
area where the plans are cached again calls for expensive inter-process 
synchronization.

Table data is already shared. If two backends are manipulating the same
table, they can lock directly via shared memory rather than some OS
primitive.
Sure, some functionality can be achieved using shared memory. But it 
consumes more resources and the mutexes are a lot slower.

I think PostgreSQL has nicely combined the benefits of shared memory
with the robustness of multiple processes...
So do I. I've learned to really like PostgreSQL and the way it's built, 
and as I said in my previous mail, I'm not advocating a switch. I just 
react to the unfair bashing of multi-threaded systems.

Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] interval to seconds conversion. How?

2004-10-27 Thread Denis Zaitsev

On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
 How can I calculate the number of second in some interval?  Neither
 the secods part, nor the seconds after midnight...  Just the full
 quantity of the seconds.  I haven't find any function.
 
 Thanks in advance.

Oh, I'm sorry.  This is extract(epoch from interval).

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] '1 year' = '360 days' ????

2004-10-27 Thread Guy Fraser

Tom Lane wrote:
Doug McNaught [EMAIL PROTECTED] writes:
 

template1=# select '1 year'::interval = '360 days'::interval;
?column? 
--
t
(1 row)
   

Yeah, if you look at interval_cmp_internal() it's fairly obvious why.
I think that this definition is probably bogus, and that only intervals
that match exactly (equal months parts *and* equal seconds parts) should
be considered equal.  However the most obvious way to redefine it
(compare the months, and only if equal compare the seconds) would lead
to rather nonintuitive behaviors such as '1 year'  '1000 days'.
Anybody have any thoughts about a better way to map the multicomponent
reality into a one-dimensional sorting order?
(Note also that as Bruno was just mentioning, we really ought to have
months/days/seconds components, not just months/seconds; which makes the
comparison issue even more interesting.)
			regards, tom lane
 

As any of us who have ever researched how to calculate time know;
1) The amount of time in 1 year depends on the year due to 
leap years.
2) The amount of time in 1 month depends on the month and year 
because a month is an arbitrary number of days.
3) A week is a theological creation always equal to 7 days.

Using the Gregorian Calendar there are 10 missing days between 
Oct. 4, 1582 and Oct. 15, 1582 . Leap Years are (((every 4 years) 
except when modulo 100) except when modulo 400).

It is therefore not possible to define a Month or Year in Seconds, 
without knowing which Day, Month and Year you calculating.

Time constants :
1 Solar Day = 23 hours 56 minutes 4.091 seconds
1 Lunar Month = 27.32158 days
1 Tropical Year = 365.24215 Solar Days
1 Year in Gregorian time is : 
365 Days 5 Hours 49 Minutes 12 Seconds

As it is now obvious there is not any simple way to convert 
months to seconds since a month is an abstract number of days used 
to split four (13 week) seasons three ways plus one day every non 
leap year and two days every leap year.

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 ]. 

Trivia: In approximately 620 million years a day will be twice as 
long as it is today.


--
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] interval to seconds conversion. How?

Denis Zaitsev [EMAIL PROTECTED] writes:
 How can I calculate the number of second in some interval?  Neither
 the secods part, nor the seconds after midnight...  Just the full
 quantity of the seconds.  I haven't find any function.

EXTRACT(EPOCH FROM interval_value)

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] interval to seconds conversion. How?

2004-10-27 Thread Denis Zaitsev

On Wed, Oct 27, 2004 at 06:30:24PM -0400, Tom Lane wrote:
 Denis Zaitsev [EMAIL PROTECTED] writes:
  How can I calculate the number of second in some interval?  Neither
  the secods part, nor the seconds after midnight...  Just the full
  quantity of the seconds.  I haven't find any function.
 
 EXTRACT(EPOCH FROM interval_value)

Yes, I've already found it...  :)  Thanks anyway.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] '1 year' = '360 days' ????

2004-10-27 Thread Guy Fraser

Tom Lane wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
 

Wikipedia gives 365.242189670 days (86400 seconds) as the length of
the mean solar year in 2000. To give you some idea of how constant
that values is, Wikipedia claims that 2000 years ago the mean solar
year was about 10 seconds longer.  Using the above value I get there
is an average of 2629743 seconds in a month.
   

And yet another option is to note that in the Gregorian calendar there are
400*365+97 days or 400*12 months in 400 years, which gives 2629746 seconds
per month on average.
   

I like the latter approach, mainly because it gives a defensible
rationale for using a particular exact value.  With the solar-year
approach there's no strong reason why you should use 2000 (or any other
particular year) as the reference; and any value you did use would be
subject to both roundoff and observational error.  With the Gregorian
calendar as reference, 2629746 seconds is the *exact* answer, and it's
correct because the Pope says so ;-).
(Or, for the Protestants among us, it's correct because the SQL standard
specifies use of the Gregorian calendar.)
			regards, tom lane
 

Give or take one day every 4000 years. ;-)
--
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 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] interval to seconds conversion. How?

On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
 How can I calculate the number of second in some interval?  Neither
 the secods part, nor the seconds after midnight...  Just the full
 quantity of the seconds.  I haven't find any function.

I think you can do that using

EXTRACT(epoch FROM interval-value)

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
The problem with the future is that it keeps turning into the present
(Hobbes)


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Question Regarding Locks

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

Re: [GENERAL] interval to seconds conversion. How?

On Thu, 2004-10-28 at 04:08 +0600, Denis Zaitsev wrote:
 How can I calculate the number of second in some interval?  Neither
 the secods part, nor the seconds after midnight...  Just the full
 quantity of the seconds.  I haven't find any function.
 
 Thanks in advance.
 

SELECT extract(epoch FROM n);

n = interval

-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] Reasoning behind process instead of thread based

Tom Lane wrote:
Right.  Depending on your OS you may be able to catch a signal that
would kill a thread and keep it from killing the whole process, but
this still leaves you with a process memory space that may or may not
be corrupted.  Continuing in that situation is not cool, at least not
according to the Postgres project's notions of reliable software design.
There can't be any may or may not involved. You must of course know 
what went wrong.

It is very common that you either get a null pointer exception (attempt 
to access address zero), that your stack will hit a write protected page 
(stack overflow), or that you get some sort of arithemtic exception. 
These conditions can be trapped and gracefully handled. The signal 
handler must be able to check the cause of the exception. This usually 
involves stack unwinding and investingating the state of the CPU at the 
point where the signal was generated. The process must be terminated if 
the reason is not a recognized one.

Out of memory can be managed using thread local allocation areas 
(similar to MemoryContext) and killing a thread based on some criteria 
when no more memory is available. A criteria could be the thread that 
encountered the problem, the thread that consumes the most memory, the 
thread that was least recently active, or something else.

It should be pointed out that when we get a hard backend crash, Postgres
will forcibly terminate all the backends and reinitialize; which means
that in terms of letting concurrent sessions keep going, we are not any
more forgiving than a single-address-space multithreaded server.  The
real bottom line here is that we have good prospects of confining the
damage done by the failed process: it's unlikely that anything bad will
happen to already-committed data on disk or that any other sessions will
return wrong answers to their clients before we are able to kill them.
It'd be a lot harder to say that with any assurance for a multithreaded
server.
I'm not sure I follow. You will be able to bring all threads of one 
process to a halt much faster than you can kill a number of external 
processes. Killing the multithreaded process is more like pulling the plug.

Regards,
Thomas Hallgren
---(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] interval to seconds conversion. How?

On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
 How can I calculate the number of second in some interval?  Neither
 the secods part, nor the seconds after midnight...  Just the full
 quantity of the seconds.  I haven't find any function.

test= SELECT extract(epoch FROM '5 hours 42 minutes 35 seconds'::INTERVAL);
 date_part 
---
 20555

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Reasoning behind process instead of thread based

Thomas Hallgren [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Right.  Depending on your OS you may be able to catch a signal that
 would kill a thread and keep it from killing the whole process, but
 this still leaves you with a process memory space that may or may not
 be corrupted.

 It is very common that you either get a null pointer exception (attempt 
 to access address zero), that your stack will hit a write protected page 
 (stack overflow), or that you get some sort of arithemtic exception. 
 These conditions can be trapped and gracefully handled.

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 not sure I follow. You will be able to bring all threads of one 
 process to a halt much faster than you can kill a number of external 
 processes.

Speed is not even a factor in this discussion; or do you habitually
spend time optimizing cases that aren't supposed to happen?  The point
here is circumscribing how much can go wrong before you realize you're
in trouble.

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

[GENERAL] WARNING: column footype has type unknown

2004-10-27 Thread Ed L.


On 7.4.6, is there any problem with defining one column of a view to be a 
string literal?  For example ...

$ psql -c create view fooview as select 'bar' as footype
WARNING:  column footype has type unknown
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Or is this warning just noise in this case?

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] WARNING: column footype has type unknown

Ed L. [EMAIL PROTECTED] writes:
 On 7.4.6, is there any problem with defining one column of a view to be a 
 string literal?  For example ...

 $ psql -c create view fooview as select 'bar' as footype
 WARNING:  column footype has type unknown
 DETAIL:  Proceeding with relation creation anyway.
 CREATE VIEW

 Or is this warning just noise in this case?

Depending on what you expect to do with the view, you'd probably be
better off casting the literal to some specific type, perhaps text
or varchar.  An example of what you won't be able to do:

regression=# select distinct * from fooview;
ERROR:  failed to find conversion function from unknown to text

I think there was some discussion in the past of forcing the view column
to text type, but evidently nothing's been done about it yet.

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] WARNING: column footype has type unknown

2004-10-27 Thread Ed L.

On Wednesday October 27 2004 5:24, Tom Lane wrote:
 An example of what you won't be able to do:

 regression=# select distinct * from fooview;
 ERROR:  failed to find conversion function from unknown to text

Is that 8.0 you're working against there?  Here's my 7.4.6 installation:

$ psql -c create view fooview as select 'bar' as footype
WARNING:  column footype has type unknown
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

$ psql -c select * from fooview
 footype 
-
 bar
(1 row)



---(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] WARNING: column footype has type unknown

2004-10-27 Thread Ed L.

On Wednesday October 27 2004 5:34, Ed L. wrote:
 On Wednesday October 27 2004 5:24, Tom Lane wrote:
  An example of what you won't be able to do:
 
  regression=# select distinct * from fooview;
  ERROR:  failed to find conversion function from unknown to text

 Is that 8.0 you're working against there?  Here's my 7.4.6 installation:

 $ psql -c select * from fooview

Ah, sorry.  Just noticed the distinct.  Thanks.

Ed


---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Creating schema copy

2004-10-27 Thread Mariusz Czuada

Hi,

In a database I have to create new schemas with exactely the same structure as 
the default one. Of course, I could reverse a schema with pg_dump, then apply 
the script to the newly created one. The problem is the base schema sometimes 
changes so I should generate scripts every time. Also I need to do this from 
a stored function.

My question: is it possible to extend (in near future) CREATE SCHEMA syntax 
with feature like:
CREATE SCHEMA user_xxx_schema FROM default_user_schema [WITH CONTENT];
?
It should create *every* object existing in 'default_user_schema' in 
'user_xxx_schema' preserving of course names, constraints, triggers, etc. 
Optionally it could also copy table contents (but this is not very 
important).

Or is there any other (relatively simple and safe) way do do this *inside* a 
stored function (pl/pgsql)?

Regards,

Mariusz

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Creating schema copy

Mariusz =?iso-8859-2?q?Czu=B3ada?= [EMAIL PROTECTED] writes:
 My question: is it possible to extend (in near future) CREATE SCHEMA syntax 
 with feature like:
   CREATE SCHEMA user_xxx_schema FROM default_user_schema [WITH CONTENT];
 ?
 It should create *every* object existing in 'default_user_schema' in 
 'user_xxx_schema' preserving of course names, constraints, triggers, etc. 
 Optionally it could also copy table contents (but this is not very 
 important).

AFAICS this would require a code body approximately as large,
complicated, and maintenance-needy as pg_dump itself; and being inside
the backend, it could share little or no physical code with pg_dump.

So no, it's not very likely to happen in the near future.  pg_dump is
your best bet.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] compatibilityissues from 7.1 to 7.4

2004-10-27 Thread Joel

On Tue, 26 Oct 2004 13:30:49 +0200
Ian Barwick [EMAIL PROTECTED] wrote

 On Tue, 26 Oct 2004 18:22:55 +0900, Joel [EMAIL PROTECTED] wrote:
  I seem to remember reading a post on this, but searching marc does not
  seem to bring it up immediately.
  
  Company BBS is on postgresql, but it's still at 7.1. The guy in charge
  of it wants some ballpark estimates and warnings about upgrading to 7.4
  so he doesn't have to worry about the recent vulnerabilities.
  
  War stories? Things to watch out for?
 
 Off the top of my head: over-length data inserted into varchar fields
 will no longer be silently truncated, raising an error instead ( a big
 source of problems with web-based apps); also, the LIMIT x,y syntax
 will no longer work.
 
 Your best bet is fro someone who knows your system to go through the
 PostgreSQL release notes.

Thanks. 

The guy in charge of this bbs is, of course, looking to avoid work
(don't we all), so he was wondering about whether 7.1 was subject to
this vulnerability and the possible data loss bug.

I did a little research, and it looks like 7.1.3 is the last of the 7.1
line. Security Focus reports a boundary condition vulnerability for
7.1.3 from 2003. So it doesn't look wise to leave it at 7.1 forever, I
suppose.

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.

Any other suggestions? Any thoughts on the urgency of the move?

-- 
Joel [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] determine sequence name for a serial

I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.

For example,

CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);

\d foo
   Table public.foo
 Column |  Type   |  Modifiers
+-+-
 id | integer | not null default nextval('public.foo_id_seq'::text)
 bar| text|
Indexes:
foo_pkey primary key, btree (id)

Now, I have figured out how to get a list of all the sequences with:

foo= SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
  relname

 foo_id_seq
(1 row)

I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and 
pg_class in order to find the sequence for a specific field in public.foo.

Can anyone point me in the right direction? I am trying to get out of the habit of 
hard-coding the sequence names in my code. 

Now that I think of it, I am lacking 'public.' as well from my query. 

Ok, so how would I go about getting the sequence name for a SERIAL field on any given 
schema.table? I would like to build a function that would return this value if I pass 
it the schema and table (and fieldname is necessary)

Thanks,

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] determine sequence name for a serial

On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:
 I am trying to track down a method of determining what a sequence name
 is for a SERIAL is in postgresql.
 
 For example,
 
 CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
 
 \d foo
Table public.foo
  Column |  Type   |  Modifiers
 +-+-
  id | integer | not null default nextval('public.foo_id_seq'::text)
  bar| text|
 Indexes:
 foo_pkey primary key, btree (id)
 
 Now, I have figured out how to get a list of all the sequences with:
 
 foo= SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
   relname
 
  foo_id_seq
 (1 row)
 
 I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and 
 pg_class in order to find the sequence for a specific field in public.foo.
 
 Can anyone point me in the right direction? I am trying to get out of the habit of 
 hard-coding the sequence names in my code. 
 
 Now that I think of it, I am lacking 'public.' as well from my query. 
 
 Ok, so how would I go about getting the sequence name for a SERIAL field on any 
 given schema.table? I would like to build a function that would return this value if 
 I pass it the schema and table (and fieldname is necessary)
 
 Thanks,


I figured out how to get this:

foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
   adsrc

 nextval('public.foo_id_seq'::text)
(1 row)

However, this will break as soon as I do this:

foo= CREATE SCHEMA x;
CREATE SCHEMA
foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
NOTICE:  CREATE TABLE will create implicit sequence foo_id_seq for
serial column foo.id
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
ERROR:  more than one row returned by a subquery used as an expression

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.

Help. :-)

Thanks again,

-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] determine sequence name for a serial

On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:
 
 I figured out how to get this:
 
 foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
 pg_class WHERE relname = 'foo');
adsrc
 
  nextval('public.foo_id_seq'::text)
 (1 row)
 
 However, this will break as soon as I do this:
 
 foo= CREATE SCHEMA x;
 CREATE SCHEMA
 foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
 NOTICE:  CREATE TABLE will create implicit sequence foo_id_seq for
 serial column foo.id
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
 foo_pkey for table foo
 CREATE TABLE
 foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
 pg_class WHERE relname = 'foo');
 ERROR:  more than one row returned by a subquery used as an expression
 
 So, it was a nice attempt, but I am back to the need to of determining
 the sequence name using a schema and a table.
 

Also, I am trying to avoid assuming that the sequence name will be:
schema.table_id_seq

The goal is to determine the sequence name for any schema.table that has
a SERIAL sequence (because you can create a sequence with a different
name) ... and if the column name isn't 'id'

for example:
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'bar');
adsrc
-
 nextval('public.bar_nid_seq'::text)
(1 row)

The schema.table_id_seq wouldn't work under this scenario.

any thoughts or pointers?

Thanks,

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] compatibilityissues from 7.1 to 7.4

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.

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.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] determine sequence name for a serial

On Wed, Oct 27, 2004 at 09:33:21PM -0700, Robby Russell wrote:

 So, it was a nice attempt, but I am back to the need to of determining
 the sequence name using a schema and a table.

The schema of a table is stored in pg_class.relnamespace, which is an
Oid of the pg_namespace catalog.  With that and your previous query you
should be set.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
La felicidad no es mañana. La felicidad es ahora


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] determine sequence name for a serial

Robby Russell [EMAIL PROTECTED] writes:
 Ok, so how would I go about getting the sequence name for a SERIAL
 field on any given schema.table?

8.0 will have a function pg_get_serial_sequence to do this for you.
If you can't wait, the secret is to look in pg_depend for the dependency
link from the serial sequence to its column.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] determine sequence name for a serial

2004-10-27 Thread Jonathan Daugherty

# I figured out how to get this:
# 
# foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
#adsrc
# 
#  nextval('public.foo_id_seq'::text)
# (1 row)
# 
# However, this will break as soon as I do this:
# 
# foo= CREATE SCHEMA x;
# CREATE SCHEMA
# foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
# NOTICE:  CREATE TABLE will create implicit sequence foo_id_seq for
# serial column foo.id
# NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
# foo_pkey for table foo
# CREATE TABLE
# foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# ERROR:  more than one row returned by a subquery used as an
# expression

This should suffice to get you a string you can regex.  Other than the
default value setting for the serial, I don't see another link that
binds the serial to its sequence.

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;

-- 
  Jonathan Daugherty
  Command Prompt, Inc. - http://www.commandprompt.com/
  PostgreSQL Replication  Support Services, (503) 667-4564

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] determine sequence name for a serial