[SQL] How can I to solute this problem?

[Christopher Browne]

"LEON" <[EMAIL PROTECTED]> wrote:
>My jsp doesn't implement connection pool.It directly connects postgresql
by >jdbc.

>After I run the Ui some times, the UI(jsp) would report "ieSorry,too many
>clientslg" .The exception is SQLException.

>I must restart tomcat or postgresql I can continue to running my UI.

>May this problem be soluted?

Restarting servers seems a pretty awful approach.

You could put off the problem a little by increasing the number of
connections PostgreSQL will permit, but that only puts off the problem.

The RIGHT solution is to use a Java class implementing a connection pool
manager.
-- 
(reverse (concatenate 'string "ofni.smrytrebil@" "enworbbc"))
<http://dev6.int.libertyrms.info/>
Christopher Browne
(416) 646 3304 x124 (land)



---(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: [SQL] duplicate dates

[Christopher Browne]

Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify  
> the records in the database where the date/time fields are the same. How  
> can I do this? Do I need to create a view or temp table? Is there a way  
> to run through one table multiple times. 

If you plan to review the data multiple times, and the table is rather
large, then it probably makes sense to make a temp table.

  select a.* into temp table my_dupes from
 dated_table a, dated_table b
   where a.event_on = b.event_on
 -- Assuming that the primary key is on (field1, field2, field3)
 and a.key_field1 <> b.key_field1
 and a.key_field2 <> b.key_field2
 and a.key_field3 <> b.key_field3;

You could then rummage through my_dupes as needed.  Note that if there
are more than 2 simultaneous records, it will list all of them
multiple times :-(.

You may also want to be more precise about what you mean by 'date/time
fields are the same'.  They go down to fractions of a second, so you
shouldn't have a huge number of collisions.
-- 
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

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

Re: [SQL] INSERT INTO ... SELECT

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Silke Trissl) would write:
> I would like to insert into a table values from a table and user
> defined ones. Here is the example:
>
> I found this statement to insert values from another table:
>
> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER;
>
> But the test_table has another column, which should have the same
> value for all the customers.
>
> Is there something like
>
> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id,
> name from CUSTOMER:
>
> and if so, what ist the correct statement? If not, what is an
> alternative to insert a single row at a time?

You're close.

The constant term needs to be inside the SELECT.

Try:
  insert into test_table (int_id, cust_id, cust_name)
  select '1', id, name from customer;
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/sap.html
(eq? 'truth 'beauty)  ; to avoid unassigned-var error, since compiled code
  ; will pick up previous value to var set!-ed,
  ; the unassigned object.
-- from BBN-CL's cl-parser.scm

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

Re: [SQL] A simple way to Create type ...?

[Christopher Browne]

[EMAIL PROTECTED] (ow) writes:
> I had a look at "create type" docs and it seems somewhat complex, involving
> creation of functions and etc. I hope there's a simpler way for the following:
>
> How should one declare a new custom type, say, "AddressType" that corresponds
> internally to "varchar(50)". In other words, all columns that are assigned
> "AddressType" would internally be "varchar(50)".
>
> Example:
> create type AddressType ... -- how to do it in a simple way?
>
> create table ADDRESS
> {
>   address_id  int   not null,
>   street_address1 AdressTypenot null,
>   street_address2 AdressTypenot null,
>   
> )

CREATE TYPE is intended to do something a whole lot more
sophisticated than you want.

What you want instead is CREATE DOMAIN.

flexreg=# create domain addresstype varchar(50);
CREATE DOMAIN
flexreg=# create table address ( address_id integer not null, street1 addresstype not 
null, street2 addresstype);
CREATE TABLE
flexreg=# \d address
Table "public.address"
   Column   |Type | Modifiers 
+-+---
 address_id | integer | not null
 street1| addresstype | not null
 street2| addresstype | 
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"Although  Unix is  more reliable,  NT may  become more  reliable with
time"  --   Ron  Redman,  deputy  technical  director   of  the  Fleet
Introduction Division of the Aegis Program Executive Office, US Navy.

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

Re: [SQL] Automated Backup

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("Kumar") was seen spray-painting on a wall:
> Is there a way to automate the backup databases using pg_dump (like in SQL server)?

If you can come up with a way of automating the running of programs,
then I imagine that might be possible.  I have heard that a program
called "cron" might be used for this purpose.
-- 
"aa454","@","freenet.carleton.ca"
http://cbbrowne.com/info/x.html
People can be set wondering by loading obscure personal patchable
systems, and sending bug reports.  Who would not stop and wonder upon
seeing "Experimental TD80-TAPE 1.17, MegaDeath 2.5..."?  The same for
provocatively-named functions and variables in stack traces.
-- from the Symbolics Guidelines for Sending Mail

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

Re: [SQL] virus warning

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (Richard Huxton) wrote:
> I've been getting something similar myself. Roughly 100 per day.

Only 100, eh?  I have been seeing that many per hour, give or take...
-- 
"aa454","@","freenet.carleton.ca"
http://cbbrowne.com/info/x.html
People can be set wondering by loading obscure personal patchable
systems, and sending bug reports.  Who would not stop and wonder upon
seeing "Experimental TD80-TAPE 1.17, MegaDeath 2.5..."?  The same for
provocatively-named functions and variables in stack traces.
-- from the Symbolics Guidelines for Sending Mail

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

Re: [SQL] Datafiles for Databases

[Christopher Browne]

In the last exciting episode, [EMAIL PROTECTED] ("Kumar") wrote:
> I was looking for a structure like oracle or ms Sql server in Postgres. What I have 
> expected is individual
> datafiles for individual databases. But i cant fine such items in the 
> /usr/local/pgsql/data directory.
>
> Will the postgres create individual data file for databases?

No.  Just as with Oracle, PostgreSQL creates multiple files for each
database.

> How to get the datafile path of a database?

select oid, datname from pg_database;

The path for database 'datname' will be:
   $PGDATA/base/oid/
-- 
"aa454","@","freenet.carleton.ca"
http://cbbrowne.com/info/x.html
People can be set wondering by loading obscure personal patchable
systems, and sending bug reports.  Who would not stop and wonder upon
seeing "Experimental TD80-TAPE 1.17, MegaDeath 2.5..."?  The same for
provocatively-named functions and variables in stack traces.
-- from the Symbolics Guidelines for Sending Mail

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

Re: [SQL] auto_increment

[Christopher Browne]

In the last exciting episode, [EMAIL PROTECTED] ("Muhyiddin A.M Hayat") wrote:
> Ok, but if i do rollback, the auto_increment don't roolback.

Right, it's not supposed to.

Think about the situation where you have 5 clients connecting to the
database and adding records to this table.

The current functionality of sequences means that with a little
cacheing of values, they can all be hammering the table with inserts
and never need to worry about what the other is doing.

If the increment was rolling back by one when an INSERT was rolled
back, that would mean that the cache size was just 1, and access to
that sequence would have to be serialized across all accessors, which
would slow it down incredibly.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/oses.html
"Let me blow that up a bit more for you."
-- Colin Powell, Discussing a picture of the intelligence compound in
   Iraq

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

Re: [SQL] selecting duplicate records

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (Christoph Haller) wrote:
>> 1. How to select duplicate records only from a single table using a
> select
>> query.
>>
> e.g.
> select sid,count(sid) from location group by sid having count(sid)>1;
>
> Do you get the idea?
> Your request is pretty unspecific, so if this is not what you're asking
> for,
> try again.

The aggregate is likely to perform horrifically badly.  Here might
be an option:

Step 1.  Find all of the duplicates...

select a.* into temp table sid from some_table a, some_table b
  where a.oid < b.oid and
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
 ...
a.fieldn = b.fieldn;

Step 2.  Look for the matching entries in the source table...

select a.* from some_table a, sid b
  where
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
 ...
a.fieldn = b.fieldn;

[There's a weakness here; if there are multiple dupes, they may get
picked multiple times in the second query :-(.]
-- 
If this was helpful,  rate me
http://cbbrowne.com/info/rdbms.html
As Will Rogers would have said, "There is no such thing as a free
variable."  -- Alan Perlis

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

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

Re: [SQL] does postgresql execute unions in parallel?

[Christopher Browne]

[EMAIL PROTECTED] (Gaetano Mendola) writes:
> teknokrat wrote:
>> If I have several selects joined with unions does postgresql
>> execute the concurrently or not?
>
> nope.

I was talking with Jan about that very idea yesterday; this would seem
to be the place where PostgreSQL might take some (possibly even nearly
magical :-)) benefit from threading.

The usual way that people expect to use threading is for each
connection to have a thread.

If, instead, every _join_ had a thread, that would allow producers of
data to look for their data quasi-independently, passing result sets
upwards towards the return set to whatever thread was waiting to
consume the data.  

This would allow one complex query to take over a whole horde of
processors :-).

The "magic" part would be if the system decided, "The SEQ SCAN on the
table I'm looking at is a big one; let's split it into 4 chunks, doing
a virtual UNION ALL, and thereby filter bits of it in parallel on 4
CPUs."  That would provide many of the benefits Informix claimed from
"fragmentation" without having to fragment the table :-).
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/lsf.html
Rules of the  Evil Overlord #187. "I will not  hold lavish banquets in
the middle of  a famine. The good PR among the  guests doesn't make up
for the bad PR among the masses."  

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

   http://archives.postgresql.org

Re: [SQL] indexing timestamp fields

[Christopher Browne]

teknokrat <[EMAIL PROTECTED]> writes:
> Is it a good idea to index timestamp fields? what about date fields in
> general?

If you need to order by a timestamp, then it can be worthwhile.

If that timestamp can be null, and is rarely populated, then you might
get a _big_ benefit from creating a partial index as with:

 create index by_some_date on my_table(some_date) where some_date is
   not null;
-- 
"cbbrowne","@","libertyrms.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

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

Re: [SQL] indexing timestamp fields

[Christopher Browne]

teknokrat <[EMAIL PROTECTED]> writes:
> Christopher Browne wrote:
>
>> teknokrat <[EMAIL PROTECTED]> writes:
>>
>>>Is it a good idea to index timestamp fields? what about date fields in
>>>general?
>> If you need to order by a timestamp, then it can be worthwhile.
>> If that timestamp can be null, and is rarely populated, then you
>> might
>> get a _big_ benefit from creating a partial index as with:
>>  create index by_some_date on my_table(some_date) where some_date is
>>not null;
>
> I have a lot of queries of the " where timestamp < some date " type
> and was wondering if an index would improve performance. None of the
> timestamps are null and they are always populated

There isn't a single straight answer on this.  
It _might_ help; it might not.  

- It might be that adding "timestamp" to some existing index would be
better still.

- If the table is real big, and the "ts < other_ts" doesn't restrict
things very much, then you may merely sit in between an index scan
that touches every page of the table and a Seq Scan that does the
same.

Try creating the index, and do some EXPLAIN ANALYZE queries to see
what happens; that should give you an idea as to how effective this
is.
-- 
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

---(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: [SQL] Which is faster SQL or PL/PGSQL

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] ("George A.J") wrote:
> i am converting an MSSQL database to Postgres. there is a lot of procedures to 
> convert.
>
> which language is best for functions, SQL or plpgsql.
>
> which is faster . i am using postgres 7.3.2

Hmm?  This doesn't seem to make much more sense than the question of
what colour a database should be ("Mauve has more RAM...").

SQL and pl/pgsql are quite distinct.  If you have procedures that
require programmed logic, with things like variables and loops, SQL
generally cannot do that, and you will HAVE to use one of the embedded
languages.

If raw speed is at issue, it is quite likely that rewriting the
procedures in C would lead to code that is faster still.

But the real question is of what language you *need* to implement in.
For certain sorts of simple procedures, SQL may suffice; as complexity
grows, you will need to use one of the other languages, whether
plpgsql, plperl, plpython, C, C++, and such, and the question won't be
of speed; it will be of necessity.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
Never hit someone head on, always sideswipe.  Never say, "Foo's last
patch was brain-damaged", but rather, "While fixing the miscellaneous
bugs in 243.xyz [foo's patch], I found"
-- from the Symbolics Guidelines for Sending Mail

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

Re: [SQL] Max input parameter for a function

[Christopher Browne]

After a long battle with technology,[EMAIL PROTECTED] ("Kumar"), an earthling, wrote:
> While trying to allocate about 36 input parameters, I got an error saying that the 
> max input parameter for a function is only 32.
>
> Is it right? How to overcome this? Because I wanna insert records into a table of 55 
> columns with a lot of NULL able columns.
>
> I am using Postgres 7.3.4 on RH Linux 7.2.
>
> Note: I can't use 2 functions , one to insert 32 rows into the table
> first and the update the remaining columns with other
> function. Because there are only 27 columns that are not null.

The default compiled in is to limit the number of parameters to 32.
You can recompile it and modify the number of input parameters.

Somehow, the notion of a table with 55 columns strikes me as a
situation where normalization is likely to be BADLY needed...
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/linux.html
Rules of  the Evil Overlord  #156. "If I  have the hero and  his party
trapped, I will  not wait until my Superweapon  charges to finish them
off if more conventional means are available."


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

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

Re: [SQL] index and min()

[Christopher Browne]

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted:
> SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to
> complete.
>
> On the mssql server this takes less than 1 second.

If you have NO index on inserttime, then the best that can be done is
the sequential scan that you saw.

If there is an index on inserttime, then the PostgreSQL idiom that
provides a _massive_ speedup is the query:

 select inserttime from acc_pb order by inserttime limit 1;

In theory, it ought to be a nifty idea to have a way of automatically
transforming the min(inserttime) query into what I showed, but that is
likely to be difficult to do in general, and nobody has yet proposed
an implementation, so you'll have to do that yourself.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/x.html
"Linux  and  other  OSS  advocates  are making  a  progressively  more
credible argument  that OSS software is  at least as robust  -- if not
more  -- than  commercial  alternatives." -  Microsoft lamenting  Open
Source Software in the "Halloween Document"

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

Re: [SQL] numeric and float converts to int differently?

[Christopher Browne]

[EMAIL PROTECTED] (SZUCS Gábor) writes:
> As Tom pointed out, it isn't a floating point failure -- it is how rounding
> float is implemented. I assume anything with less than 15 digits can be
> exactly represented as float.

No, "decimal" fractions cannot ever be exactly represented in floating
point because since they use powers of two, you wind up with repeated
fractions.

1/3 is approximately 0.333, but you cannot present that exactly in
decimal.

In the very same way, 1/10 is approximately equal to
0.001001001001001001001001001001 (as a binary 'fraction'); the '001'
part is a repeating group, and wherever you terminate it, you lose
exactness.
-- 
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

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

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

Re: [SQL] A tricky sql-query...

[Christopher Browne]

Quoth [EMAIL PROTECTED] (Mark Stosberg):
> On 2003-10-22, Timo <[EMAIL PROTECTED]> wrote:
>>
>> You can't have any recursion in an pure sql-query, can you?
>
> It depends on how you think of recursion, I'd say. You join on the same
> table a number of times, by giving it a different alias each time. You 
> have to manually specify (or generate with application code) all these 
> aliases and joins, though. Sometimes people use this technique to
> implement tree structures in SQL.  

There is apparently an SQL 1999 feature that expressly supports
recursion.

WITH RECURSIVE 
Q1 AS SELECT ... FROM ... WHERE ...
Q2 AS SELECT ... FROM ... WHERE ...
  SELECT ... FROM Q1, Q2 WHERE ...

See example here...



There is presumably some work ongoing; support for WITH RECURSIVE is
on the TODO list, and has been discussed before...

http://archives.postgresql.org/pgsql-hackers/2003-05/msg00657.php
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
"Listen,  strange women, lyin'  in ponds,  distributin' swords,  is no
basis  for a  system of  government. Supreme  executive  power derives
itself from a mandate from  the masses, not from some farcical aquatic
ceremony."  -- Monty Python and the Holy Grail

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

Re: [SQL] SQL to PLC to diverter gate, can this be done?

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Razorbak71) would write:
> First, I know nothing about PLC's, but I was wondering is there a
> way to extract data from a SQL database file, feed that to a PLC is
> some manner, and then have the PLC trigger a diverter gate based on
> the value in the table exceeding a certain value, to divert parts to
> another location.
>
> Am I off base, or is there something out there that will allow or
> help me to do this.  Any help would be greatly appreciated.

It all depends on how you are able to program the PLC.  They tend to
have pretty proprietary tools that are very much dependent on the
vendor.

My little brother works for Omron, and does some pretty fancy things
with their PLCs.  But since the languages Omron uses aren't the same
as are used with similar products from (say) Siemens, knowing one
platform doesn't necessarily help with the other.  (They commonly use
languages resembling BASIC...)

I think you need to know a LOT more about the PLC system in question,
and that knowledge is not likely to be found here.  What you want is
probably feasible in some sense, although it's likely to be rather
fiddly.  

And your use of the term "SQL database file" suggests some confusion
on your part as to how the SQL side would work.  While PostgreSQL
implements tables in files, you don't read data from the files, but
instead ask the "postmaster" to give it to you.  You might need to
drop the data into a separate file outside the database in order to
get it into a form that the PLC can get at...
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/linux.html
"Microsoft is a cross between the Borg and the Ferengi. Unfortunately,
they  use  Borg  to  do  their  marketing  and  Ferengi  to  do  their
programming."  -- Simon Slavin in asr

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

   http://archives.postgresql.org

Re: [SQL] transaction management in plpgsql functions

[Christopher Browne]

After takin a swig o' Arrakan spice grog, Cris Carampa <[EMAIL PROTECTED]> belched 
out...:
> It seems that transaction management statements (begin work...commit
> work) are not allowed into plpgsql functions. Is it true? If true,
> what happens if I put a DML statement into a function? Is it
> automatically commited every time the function executes? Is there no
> way to rollback the changes?

The "problem" with using BEGIN/COMMIT in plpgsql is fundamentally that
those functions have to be _started_ in the context of a transaction,
so by the time they get started, there is already a transaction in
progress.

If-and-when support for nested transactions gets into place, you would
presumably be able to have nested transactions inside functions.

What happens may be a little different from what you think; things are
not COMMITted when the function executes, but rather when the COMMIT
takes place /on the transaction in which the function runs/.

Thus...

BEGIN;
INSERT INTO T1 (4, 5);
INSERT INTO T2 (6, 7, NOW());
SELECT FUNNY_FUNCTION(4,5,6,7, NOW());
DELETE FROM T1;
DELETE FROM T2;
COMMIT;

All of the changes commit as of the COMMIT statement at the end, and
not before.

If you had DML creating table T3 in FUNNY_FUNCTION, then T3 would not
become visible to other users until the COMMIT, although the current
transaction could readily add/modify records before the COMMIT.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/sap.html
If you're sending someone some Styrofoam, what do you pack it in?

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

Re: [SQL] help me...

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (ron_tabada) wrote:
> Hello everyone, Good day! Could anyone help me translate this query
> in Microsoft Access to Postgresql. I'm having a difficulty. Pls...
>
> Query1:
> SELECT items.description, Sum(supplieditems.qty) AS SumOfqty
> FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno
> GROUP BY items.description;
>
> Query2:
> SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty
> FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER 
> JOIN customer ON
> [OtherItem].[Itemno]=[customer].[itemcode]
> GROUP BY [items].[description];
>
> Query3:
> SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], 
> [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain
> FROM Query1, Query2;
>
> I have translated Query1 and Query2 in POSTGRESQL but I don't know
> how to implement Query3.

Apparently you have discovered the nearest equivalent to "VIEWs" in
Access.

I can suggest two ways:

1.  Define "query1" and "query2" as PostgreSQL views, as with...

  create view query1 as 
 SELECT items.description, Sum(supplieditems.qty) AS SumOfqty
 FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno
 GROUP BY items.description;

  create view query2 as [omitted details].

  Query 3 should work perfectly well when it has the two views to work
  with.

2.  Subselects...

 SELECT Query1.SumOfqty, Query2.SumOfqty, Query1.SumOfqty-Query2.SumOfqty AS remain
 FROM 
(select stuff for query 1) as query1,
(select stuff for query 2) as query2;

Approach #1. seems more appropriate, as it uses the views to keep the
queries all simple.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/nonrdbms.html
Incrementally extended heuristic algorithms tend inexorably toward the
incomprehensible.

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

Re: [SQL] Programatically switching database

[Christopher Browne]

Clinging to sanity, [EMAIL PROTECTED] (Tom Lane) mumbled into her beard:
> ow <[EMAIL PROTECTED]> writes:
>> My concern though ... wouldn't pgSql server collapse when faced with
>> transaction spawning across 100M+ records?
>
> No.  You're extrapolating from Oracle-specific assumptions again.

Or from MySQL-specific assumptions :-).

It seems reasonable (absent of particular knowledge to the contrary)
that the size of a transaction might be _expected_ to be some sort of
constraint; it is quite surprising that it isn't, and I don't think
that's purely based on the mistake of assuming that the whole world
does things exactly like Oracle.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://cbbrowne.com/info/postgresql.html
Rules  of  the  Evil  Overlord  #89.  "After  I  captures  the  hero's
superweapon, I  will not immediately  disband my legions and  relax my
guard because I believe whoever holds the weapon is unstoppable. After
all,   the  hero  held   the  weapon   and  I   took  it   from  him."


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

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

Re: [SQL] Stored Procedures

[Christopher Browne]

[EMAIL PROTECTED] (beyaRecords - The home Urban music) wrote:
> I am new to this list and the world of postgreSQL, and would like to
> know how create stored procedures in postgreSQL.

Have you considered looking at the documentation?  

It is fairly well documented there.

% man "CREATE FUNCTION" 

is quite likely to provide you with the documentation you require.
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/postgresql.html
"We have no need   to punish Pascal programmers.  Pascal  programming,
like chastity, is its own punishment.  The only way I could imagine to
make their wretched  state any worse would  be to make them  use Ada."
-- Scott Fahlman

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

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

Re: [SQL] comparing nulls

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Kenneth Gonsalves) would 
write:
> On Tuesday 20 January 2004 19:26, Chris Bowlby wrote:
>>  Under 7.3.x this option was removed, you need to test via:
>>
>>  SELECT * from table where field IS NULL;
> thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around 
> with these thangs?

Because there is a desire to have PostgreSQL conform with public
standards such as SQL-1999.  

The use of "IS NULL" conforms with SQL standards; the use of "= NULL"
does not.
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/advocacy.html
"Let's face it  -- ASCII text is  a far richer medium  than most of us
deserve."  -- Scott McNealy

---(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: [SQL] update more than 1 table (mysql to postgres)

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("treeml") was seen spray-painting on a wall:
> I am migrating from MySQL to Postagres. I have problem with postgres
> updating 2 tables with one statement.
>
> In MySQL I can update 2 tables (parent, child) with a statement like this
>
> UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET
> parent.field1 = 'company',
> child.field2 = 'john'
>  WHERE child.pid = 7
>
> Or I can also do
> UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john'
> WHERE
> parent.pid = child.foreign_key
> AND child.pid = 7
>
>
> But I couldn't do that in Postgres,
> Only one table is allowed in an update statement. I tried to create a view,
> and updating the view,  but that was not allowed.   I could do 2 SQL
> updates, but I am sure there is a better way to do this.  Anyone have any
> idea. Appreciated.

Well, what's going on here is that PostgreSQL is conforming to the SQL
standards.  The syntax MySQL is providing is clearly a deviation from
standards, and once you head down the "we're ignoring standards" road,
you cannot have _any_ expectations of things functioning similarly
(or, for that matter, FUNCTIONING AT ALL) with another database
system.

There are several _possible_ solutions to this; which one is
preferable is certainly in the eye of the beholder:

1.  Do two UPDATEs inside a transaction.  Perhaps...

begin;
update parent set field1 = 'company' where exists (select *
  from child where foreign_key = parent.pid and child.pid = 7);
update child set field2 = 'john' where pid = 7 and exists
  (select * from parent where parent.pid = child.foreign_key);
commit;

2.  You might set up a view that joins the tables, such as 

  create view combination as 
   select parent.pid as parent_pid, child.pid as child_pid,
 parent.field1, child.field2 
   from parent, child 
   where parent.pid = child.foreign_key;

  and then create a RULE allowing updates to this view that allows
  updating whichever fields that it is appropriate to allow this on.

  This will involve fairly fancy footwork, unfortunately.  It's
  doable, but there's something of a learning curve...

In either case, there are still two update statements; in the
updatable VIEW situation, they hide a little "out of VIEW" (if you'll
pardon the pun!).
-- 
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/languages.html
Rules  of the  Evil Overlord  #41. "Once  my power  is secure,  I will
destroy all those pesky time-travel devices."


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

Re: [SQL] timestamptz - problems

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("Mark Roberts") was seen spray-painting on a wall:
> Hi im using 'timestamptz' in a function called: 'getdate' to enter
> the start and finish time into a db field, however the date appears
> to be inserted into the db in a random format, i wish for it to only
> be entered into the db as DD-MM- (European,UK). I understand
> this is probably a very simple problem to resolve but thus far I
> have failed, can anyone plz help.

It seems to me that you're asking the wrong question.

The data type you should normally use for handling timestamps is
"timestamptz," which records the date, time and time zone.

It is certainly _NOT_ inserted in a "random format;" there is NO
ambiguity about what the date and time types in PostgreSQL store in
the database.

What _might_ vary is how a date happens to be _displayed_, and that is
something that you might reasonably want to control.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://www3.sympatico.ca/cbbrowne/x.html
This Bloody Century
"Early this century there was a worldwide socialist revolution. The
great battles were then between International Socialism, National
Socialism, and Democratic Socialism. Democratic Socialism won because
the inertia of democracy prevented the socialism from doing as much
damage here. Capitalism first reemerged from the ashes of National
Socialism, in Germany and Japan. It is now reemerging from the ashes
of International Socialism.  Next?

After all, inertia works both ways..."
-- Mark Miller

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

Re: [SQL] postgresql multiple insert slow

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Michael L. Hostbaek") 
would write:
> I runs fine - and I get no errors - but it takes almost 25 minutes to
> complete.. I tried running the script while just grabbing the rows from
> the oracle database and writing to a text file - and then it only takes
> a couple of minutes .. So it must be the INSERT command that chokes - is
> there a better way to do it ? 
>
> Any advise much appreciated.

The problem here is that each insert implicitly sets up a transaction
BEGIN and COMMIT.

If you were to wrap the loop with a BEGIN at the start and a COMMIT at
the end, you would likely find it would complete much, much faster.
-- 
output = ("cbbrowne" "@" "acm.org")
http://www3.sympatico.ca/cbbrowne/wp.html
"Java and C++ make you think that the new ideas are like the old ones.
Java is the most distressing thing to hit computing since MS-DOS."
-- Alan Kay

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

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

Re: [SQL] Field list from table

[Christopher Browne]

"Jan Pips" <[EMAIL PROTECTED]> wrote:
> How can I, using SELECT, get the full list of fields from a given table?

portfolio=# select column_name, data_type from information_schema.columns where 
table_catalog = 'portfolio' and table_schema = 'public' and table_name = 'stocktxns';
 column_name |data_type 
-+--
 symbol  | character varying
 date| timestamp with time zone
 price   | numeric
 quantity| numeric
(4 rows)

information_schema.columns has additional columns to provide
information about numeric precision, string widths, and such...
-- 
"cbbrowne","@","ntlug.org"
http://www.ntlug.org/~cbbrowne/postgresql.html
Rules  of the  Evil Overlord  #4.  "Shooting is  not too  good for  my
enemies." 

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

   http://archives.postgresql.org

Re: [SQL] User defined types -- Social Security number...

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (Michael Chaney) wrote:
> Look, you're thinking way too hard on this.  An SSN is a 9-digit number,
> nothing more.  There are some 9-digit numbers which aren't valid SSN's,
> and you might want to get fancy and create a constraint for that.
>
> Regardless, you are making a *major* mistake of confusing data
> storage with rendering.  It is common to *render* an SSN as
> xxx-xx- and its cousin the FETID (Federal Employers Tax ID) as
> xx-xxx.  To store the dashes makes no sense.  They're in the
> same place each time, it's wasted data.
>
> Store the SSN as an "integer".  When you begin to think about this
> correctly, the "leading zeros" problem disappears since that is also a
> *rendering* issue.

Well put.

The one thing that is a bit unfortunate is that 32 bit ints aren't
quite big enough for this.  You need 1 extra digit :-(.

> When you pull the data out, either fix it up in your programming
> language to the format that you wish, or use the to_char function as
> shown above in your select statements.

Using a view to hide the "physical" representation is also an idea.

A full scale type definition could make for an even more efficient
approach that makes the implementation appear invisible.

> To help you think about this whole issue, consider the timestamp
> datatype.  Timestamps are stored as a Julian date internally.  I
> suspect that they use a double-floating point as the actual format,
> but regardless the point is that it's a number.  Rather than storing

Actually, it's an "int64"; a 64 bit integer, on platforms that support
that type.  It's a "double" only on platforms that do not support that
type.

> It's easier to use that as a basic format from which we can render
> it in any way we wish.

Indeed.
-- 
If this was helpful,  rate me
http://cbbrowne.com/info/spiritual.html
"I owe the government $3400 in  taxes.  So I sent them two hammers and
a toilet seat."  -- Michael McShane

---(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: [SQL] Trying to make efficient "all vendors who can provide all items"

[Christopher Browne]

Clinging to sanity, [EMAIL PROTECTED] mumbled into her beard:
> My mind is drawing a blank.  Please consider:
> TABLE 1: items: list of items in catalog
> item_id  |  item_description
>
> TABLE 2: vendors: list of vendors who provide 1 or more items
> vendor_id |  vendor_name
>
> TABLE 3: item_vendors: record existence indicates vendor can provide item
> item_id  |  vendor_id
>
>
> QUESTION:
> I have a list of say 5 items, and I want to find all vendors who can provide
> ALL 5 items
>
> Solution 1:
> SELECT vendor_id
> FROM vendors
> WHERE EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_1')
>   AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_2')
>   AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_3')
>   AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_4')
>   AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_5')
>
> Solution 2:
> SELECT vendors.vendor_id
> FROM vendors, items AS item_1, items AS item_2, items AS item_3, items AS
> item_4, items AS item_5
> WHERE items_1.vendor_id = vendors.vendor_id AND items_1.item_id = 'item_1'
>   AND items_2.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_2'
>   AND items_3.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_3'
>   AND items_4.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_4'
>   AND items_5.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_5'
>
> Yep, both my solutions are pretty ugly, especially in situations where my
> list of items that need to be provided grow large.
>
> There must be a better way.  Can anyone help me with this?

I'd suggest putting in another table containing the items that you
want to check against...

create table list_items (
  item_id text not null unique
);
insert into list_items (item_id) values ('item_1');
insert into list_items (item_id) values ('item_2');
insert into list_items (item_id) values ('item_3');
insert into list_items (item_id) values ('item_4');
insert into list_items (item_id) values ('item_5');

select v.vendor_id, v.vendor_name from
vendors v,
(select vendor_id, count(*) from
   list_items l, item_vendors iv where
   iv.item_id = l.item_id
   group by vendor_id
   having count(*) = 5) as vendors_sat
where v.vendor_id = vendors_sat.vendor_id;

Extend it to 20, and the query only need change "5" to "20"...
-- 
If this was helpful,  rate me
http://www.ntlug.org/~cbbrowne/unix.html
Rules of the Evil Overlord #56.  "My Legions of Terror will be trained
in basic marksmanship. Any who  cannot learn to hit a man-sized target
at 10 meters will be used for target practice."


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

   http://archives.postgresql.org

Re: [SQL] plpgsql vs. SQL in stored procedures

[Christopher Browne]

[EMAIL PROTECTED] (Ivan Sergio Borgonovo) wrote:
> Is there a way to declare variables and use IF in plain SQL, not in
> plpgsql inside stored procedures?

The forthcoming support for recursive queries using a WITH clause
might provide, after a fashion, a way to declare variables.

As for IF, there is a functional equivalent to it in the form of the
SQL standard CASE statement.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www.ntlug.org/~cbbrowne/linuxxian.html
"How should I know if it  works?  That's what beta testers are for.  I
only  coded  it."   (Attributed  to  Linus Torvalds,  somewhere  in  a
posting)

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

Re: [SQL] User defined types -- Social Security number...

[Christopher Browne]

In the last exciting episode, "Greg Patnude" <[EMAIL PROTECTED]> wrote:
> Thanks Josh -- I understand that there are valid and invalid SSN's -- 
> similar rules apply to zip codes and area codes...
>
> I tried this:
>
> SELECT to_char(123456789, '000-00-');
> which yields 123-45-6789 -- nicely, I might add...
>
> the trick is getting postgreSQL to do this without having to create an ON
> SELECT and ON UPDATE TRIGGER...
>
> an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick...
>
> SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-" --
>
> I do agree that there are valid ranges -- my main concern is being able to
> store any leading zeros - I just need to make sure that something "looks"
> like a valid SSN in the formattig
> (nnn-nn-) and that I can store / retrieve it with the approoriate
> format -- what I am really trying to accomplish is an "input mask"...
>
> I hadn't considered using a Domain have to look at that

Strongly recommended; that allows applying the validation in many
places without having to repeat validation "code."

If you will be using really a lot of these values, and indexing on
them, it even may be worth looking at a custom type.

A performance "win" would come in using a compact data type.  For
instance, for 9 digit national ID numbers, you can do a LOT better
than an 11 byte string.  (Aside: Anything bigger than 34 bits would
do, demonstrating that it is a regrettable loss that 36 bit computer
systems went the way of the dodo...)
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
I found out why  cats drink out of the toilet. My  mother told me it's
because it's cold in there. And I'm like: How did my mother know THAT?
--Wendy Liebman

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

Re: [SQL] Secure DB Systems - How to

[Christopher Browne]

In the last exciting episode, [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
> Sarah Tanembaum wrote:
>
>>I was wondering if it is possible to create a secure database system
>>usingPostgreSQL/PHP combination?
>>
>>I have the following in mind:
>>
>>I wanted to store all my( and my brothers and sisters) important document
>>information such as birth certificate, SSN, passport number, travel
>>documents, insurance(car, home, etc) document, and other important documents
>>imagined in the database.
>>
>>The data will be entered either manually and/or scanned(with OCR). I need to
>>be able to search on all the fields in the database.
>>
>>We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
>>maintained. The data should be synchronize/replicate between those
>>computers.
>>
>>Well, so far it is easy, isn't it?
>>
>>Here's my question:
>>
>>a) How can I make sure that it secure so only authorized person can
>>modify/add/delete the information? Beside transaction logs, are there any
>>other method to trace any transaction(kind of paper trail)?
>>
>>
> There can be multiple solutions to your problem.
>
> The security and logging may be implemented either at
> database level or application level. That is a call you have to
> take.

Doing it at the database level means having to trust anyone that has
administrative access to the database system.

The only way for this to NOT require trusting the administrators is to
store data in some sort of encrypted form, where the data is NOT
visible except when someone decrypts it within the client application.

The main work published on the subject is _Translucent Databases_, by
Peter Wayner.  Here are a bunch of links that give a pretty good idea
of what it's about.

http://www.oreillynet.com/pub/a/network/2002/08/02/simson.html
http://www.wayner.org/books/td/
http://www.wayner.org/books/td/faq.php
http://www.linux-mag.com/2003-12/databases_01.html

They discuss it from the perspective of using Java as the "client
application" layer; presumably PHP offers some cryptographic tools to
allow doing similar things...

http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial1.html
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/languages.html
"To do is to be."  -- Aristotle
"To be is to do."  -- Socrates
"Do be do be do."  -- Sinatra
"Do be a do bee."  -- Miss Sally of Romper Room fame.
"Yabba dabba do."  -- Fred Flintstone
"DO...BEGIN..END"  -- Niklaus Wirth

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

Re: [SQL] what is maximum size of "text" datatype in postgres?

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Smit") would write:
> i wanted to know, what is maximum size of "text"
> datatype in postgres.
> It is given as "unlimited characters" in the postgres
> documentation. 
> But it doesn't store characters more than 5000. The
> connection get lost if i tried to store more than 5000
> characters.
>
> I had read somewhere on forum that its max limit is 8
> bytes, and we can increase it to 32 bytes.

8 bytes is certainly not the right answer.

Creating a little table, and throwing some wide strings at it...

tutorial=# create table widetext (main text);
CREATE
tutorial=# insert into widetext values ('0123456789... repeated a
bunch of times...');
INSERT 2625519 1
... increasing size each time, for several instances ...

tutorial=# select length(main) from widetext;
 length 

   1100
   5500
  11000
(3 rows)

That's way more than 8 bytes; that's way more than 5000 bytes.

> Do any one know how to increse it? or do anyone know any other way
> to solve this problem.

What tool are you using to insert the data?

Is it possible that it has some buffer size limitation that is biting
you?
-- 
"cbbrowne","@","ntlug.org"
http://linuxfinances.info/info/
"My experience  as a member  of the  APB (Accounting Principles Board)
taught   me many lessons.A major one was  that  most  of us have a
natural tendency and an incredible talent  for processing new facts in
such a way that our prior conclusions remain intact."
-- Charles Horngren

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

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

Re: [SQL] How to check postgres running or not ?

[Christopher Browne]

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] ("Sandeep 
Gaikwad") transmitted:
> Hello Sir,
> I want to know how to check whether postgres database
> is running or not ? when  I give command like ./postmaster -i &,
> whether all databases in that postgres will run or any one [default] ? 
> If any one, then how to detect that database ?

Well, the way I usually check on what databases are running is thus:

[EMAIL PROTECTED]:/tmp/mm5/doc> netstat -an | grep PG  
   Saturday 13:18:30
unix  2  [ ACC ] STREAM LISTENING 2793 
/var/run/postgresql/.s.PGSQL.5432

One could presumably script things further to get more out of that; it
doesn't normally seem worthwhile to do so...
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://www.ntlug.org/~cbbrowne/postgresql.html
"As long as war is regarded as wicked, it will always have
 its fascination.  When it is looked upon as vulgar,
 it will cease to be popular."
--Oscar Wilde

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

Re: [SQL] How to check postgres running or not ?

[Christopher Browne]

Clinging to sanity, [EMAIL PROTECTED] (Worik) mumbled into her beard:
> [snip]
>> Just to enforce the test is better looking for the entire executable
>> path:
>> ps aux | grep /usr/bin/postmaster | grep -v grep
>>
>
> Does not work for me!
>
> [EMAIL PROTECTED]:~$ ps aux | grep /usr/bin/postmaster | grep -v grep
> [EMAIL PROTECTED]:~$ ps aux | grep postmaster | grep -v grep
> postgres   670  0.1  0.6  8544 1688 pts/1S12:33   0:00
> /usr/lib/postgresql/bin/postmaster
> [EMAIL PROTECTED]:~$
>
> So...
> " ps aux | grep postmaster | grep -v grep "
> is more reliable(?)

There's a very slightly clever modification that gets rid of the extra
grep...

Try.. 

 [appropriate ps command for your platform] | egrep '[p]ostmaster'

That egrep _won't_ match its own command line.
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/multiplexor.html
He doesn't have much of a reputation, or so I've heard. 

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

Re: [SQL] Export tab delimited from mysql to postgres.

[Christopher Browne]

Quoth [EMAIL PROTECTED] (Theo Galanakis):
>     Could you provide a example of how to do this?
>
>     I actually ended up exporting the data as Insert statements,
> which strips out cf/lf within varchars. However it takes an eternity
> to import 200,000 records... 24 hours infact Is this normal?

I expect that this results from each INSERT being a separate
transaction.

If you put a BEGIN at the start and a COMMIT at the end, you'd
doubtless see an ENORMOUS improvement.

That's not even the _big_ improvement, either.  The _big_ improvement
would involve reformatting the data so that you could use the COPY
statement, which is _way_ faster than a bunch of INSERTs.  Take a look
at the documentation to see the formatting that is needed:

http://techdocs.postgresql.org/techdocs/usingcopy.php
http://www.faqs.org/docs/ppbook/x5504.htm
http://www.postgresql.org/docs/7.4/static/sql-copy.html
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/lsf.html
Question: How many surrealists does it take to change a light bulb?

Answer: Two, one to hold the giraffe, and the other to fill the bathtub
with brightly colored machine tools.

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

Re: [SQL] Scheduling Jobs In PostgreSQL

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Chethana Kuloor) 
wrote:
> Is it possbile to schedule jobs in postgres?

No.  

You may schedule jobs using cron.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://linuxfinances.info/info/spiritual.html
"If you were to implement this in  ML, most of you would say : SCREAM,
run from the room in terror, but we could set it as a tickable problem
whatever, and  give it  to part 1A  [first year students]...  and that
idea seems rather pleasing doesn't it?"  -- Arthur Norman

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

Re: [SQL] create stored procedure from temporary table

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("Nurdin") was seen spray-painting on a wall:
> i was wondering, how to create dynamic table like temporary table
> but create from function and the result from function toocos i
> want count same calculation from other table migh be a multiple
> table so i need looping and join table...

Well, you can define a temporary table inside a stored procedure using
EXECUTE, as long as all of the operations that touch that table are
kept inside dynamic SQL invoked using EXECUTE...
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/oses.html
"Bonus!  The lack of multitasking is one of the most important reasons
why DOS destroyed Unix in the marketplace." -- Scott Nudds

---(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: [SQL] Found Large Files.. what objects are they?

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Yudie") would write:
> I found bunch of large files (more than 1 gb) in one of database directory.
>
> The files looks like this:
>
>
> 69233123
>
> 69233123.1
>
>
> 69233123.2
>
> 69233123.3
>
> 69233123.4
>
> ...and so on.
>
> These large files very delay the dumping process.
>
> Anyone know what it could be & how to delete the object related? How to find 
> a table by oid?

The phenomenon you are seeing occurs when there is a table with a
great deal of data.

Look for the table via the query:

  select * from pg_class where oid = 69233123;

Presumably these files are for a table that contains multiple GB of
data.  Or they could be for an index on a very large table.

If the data in the table/index is useless to you, you might consider
dropping the table/index.
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/wp.html
DO IT -- it's easier to get forgiveness than permission.

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

Re: [SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE

[Christopher Browne]

Try:
   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT 
1;

You append KEYWORD and a '%' together using ||.  You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will contain the query

   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%'' LIMIT 1;
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/sgml.html
C is almost a real language. (see assembler) Even the name sounds like
it's gone through  an optimizing  compiler.  Get  rid of  all of those
stupid brackets and we'll talk. (see LISP)

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

Re: [SQL] plpgsql.. SELECT INTO ... WHERE FIELD LIKE

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("Yudie") was seen spray-painting on a wall:
> How in plpgsql use LIKE with a variable?
>
> let say I want to do this query:
>  SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
>
> keyword is a variable, in this case I want to find name like 'Jo%'
>
> CREATE OR REPLACE FUNCTION custlike(text) RETURNS INT4 AS'
>   DECLARE
>     keyword ALIAS FOR $1;
>     RS RECORD;
>   BEGIN
>     SELECT INTO RS id FROM customer WHERE firstname like keyword% LIMIT 1;
>     IF FOUND THEN
>   RETURN RS.id;
>     ELSE
>    RETURN NULL;
>     END IF;
>  END'
> LANGUAGE 'PLPGSQL';

Try:
   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT 
1;

You append KEYWORD and a '%' together using ||.  You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will wind up containing the query:

   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%' LIMIT 1;
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/sgml.html
C is almost a real language. (see assembler) Even the name sounds like
it's gone through  an optimizing  compiler.  Get  rid of  all of those
stupid brackets and we'll talk. (see LISP)

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

Re: [SQL] postgressql.org

[Christopher Browne]

[EMAIL PROTECTED] (Christoph Haller) writes:
> Has anybody accidentally hit this "postgressql.org"? 
> I did. 
>
> I don't know if freeloader is the correct english term for the
> german "Trittbrettfahrer", but that's what it looks like.

This is pretty common; people "speculatively" grab domain names
involving misspellings of more-or-less prominent domain names in the
hopes that someone will offer them money for the domain.

Probably the best known one where you get _wildly_ unexpected results
is if you were to visit "whitehouse.com" expecting to get information
about the US White House (home of the US president), instead of
"whitehouse.gov", its actual address.

"postgressql.org" seems of pretty dubious value; I wouldn't worry too
much about it.
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

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

Re: [SQL] Postgres performance

[Christopher Browne]

[EMAIL PROTECTED] (PFC) writes:
>> The reason PostgreSQL is slower is because it (and by extension the team
>> behind it) cares about your data.
>
>   Sure, postgres is (a bit but not much) slower for a simple
> query like SELECT * FROM one table WHERE id=some number, and
> postgres is a lot slower for UPDATES (although I heard that it's
> faster than MySQL InnoDB)...

There is a _HUGE_ set of misconceptions here.

  1.  The speed difference is only repeatedly true for simple selects
  when done against MyISAM tables.

  2.  That speed difference for UPDATEs is only true if you are talking
  about ONE MySQL(tm) client doing updates against MyISAM tables.

  MyISAM does not support row locks; if multiple clients are
  trying to update a table, they must fight for a single table
  lock, with the result that updating tables doesn't scale _at
  all_ with MySQL(tm) for the default table type.

If you only have one process touching the database, MySQL(tm) can
therefore look quite a lot better than PostgreSQL.  Move to 2 clients
and it's not quite so good.  Move to 100 concurrent clients all trying
to do updates and you may discover that you simply can't do that...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.

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

   http://www.postgresql.org/docs/faq

Re: [SQL] Nested Sets

[Christopher Browne]

After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Troels Arvin) 
belched out:
> On Sat, 16 Apr 2005 14:14:57 -0400, Andrew Sullivan wrote:
>> You shouldn't need to do anything special around table
>> locking.
>
> - Except of one wants "True Serializability" (see chapter 12.2 in the
> manual). But I don't know if it's possible to handle table locking from
> within a user defined function.

"Inside stored functions" is one of the ultimate examples of places
where you are certain to be honest-to-goodness inside a transaction.

You can't change transactions while inside a function; all the
in-the-function processing is sure to take place in one transaction's
context.

In theory, savepoints may ultimately change that a little bit, in that
you might have portions of processing in different subtransactions.

But nonetheless Andrew's point remains valid: There is no need to do
any special locking surrounding processing that goes on inside a
stored procedure because it is all suitably embedded in a transaction.
-- 
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://linuxdatabases.info/info/slony.html
"The problem with the current Lisp Machine system is that nothing ever
calls anything anymore."  -- KMP

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

   http://archives.postgresql.org

Re: [SQL] Debet-Credit-Balance Calculation

[Christopher Browne]

Oops! [EMAIL PROTECTED] ("Muhyiddin A.M Hayat") was seen spray-painting on a 
wall:
> everything is ok, but when record > 100 that query eat all my
> cpu process and take a long time, i have wait for 3 mimutes
> but query doesn't finish. (pgsql-8.0-1 running on Dual Xeon 2.8 and
> 2GB of RAM)

What you're asking for is fairly much inherently exceedingly
expensive, and that's not really a PostgreSQL issue, it would be much
the same with any database.


The cost of the balance calculation for the first row may be 1.
For row 2, it's 1+1 = 2.
For row 3, it needs the balance from #2, so cost = 2+1 = 3.

Those add up, so the cost leaps thus:
 Individual costs RowAggregate
  1  1
  1 + 2 = 3  4
  1 + 2 + 3 = 6 10
 1 + 2 + 3 + 4 = 10 20
 and so forth...

The "naive" algorithm for this essentially results in the cost of the
query increasingly with O(n^3) where n is the number of elements in
the table.

You can get closer to O(n) by cacheing balances, but that will _not_
fall in an obvious way from an SQL query.

There is an easy way to do this; write a plpgsql set returning
function which adds the balance to the last column of the table.  That
query will always have a cost in both time and memory proportional to
the size of the table, and the memory cost may bite you as table size
grows...
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/x.html
"It's like  a house   of cards  that   Godzilla  has  been  blundering
through."  -- Moon, describing how system messages work on ITS

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

Re: [SQL] How to select from many database ??

[Christopher Browne]

Oops! [EMAIL PROTECTED] (Art - Feping) was seen spray-painting on a wall:
> i want to select from many databases, for example , i want to select table
> master in database  A, and table master in database B. 
> can i do it like this :   " select * from  A.Master, B.Master "  just like
> in SQL Server ??
> or can somebody help me how to select many database in postgresSQL ?? Thank
> u

There is a module called "dblink" in the contrib area that can do
this.  You obviously don't get to maintain all the "goodies" of
integrity constraints and MVCC 'synchronization' of data.

If you find you have applications where you are accessing multiple
databases like this, you should consider whether or not they could
perhaps be joined into one database by putting the data into several
namespaces or schemas.

That _may_ not be possible, but it's worth investigating, as it is
likely to be _way_ more efficient...
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/lsf.html
PASCAL  is  not  a  language.  It  was  an  experiment  combining  the
flexibilty of  C with that  of a drug-crazed  penguin. It is  also the
'language' of choice  of many CS professors who  aren't up to handling
REAL programming. Hence, it is not a language.

---(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: [SQL] Make COUNT(*) Faster?

[Christopher Browne]

> I'm also claiming that a true count for any active table is
> meaningless and am *not* suggesting that effort be spent on trying
> to produce such a true count.

That's a pretty big assumption that would in fact be WRONG.

We have managers interested in counting the number of objects we have
around (As a domain registry, what objects would you imagine those
might be :-)), and they're keen on possibly even being able to
reconcile those counts from day to day based on transaction activity.

Leaping into some sort of vague guesstimation would destroy the
ability to do any kind of analysis of activity, and I daresay enrage
them.

There may be times that a really rough guess can suffice; there are
other times when exactness is absolutely vital.

Creating a "fast but WRONG COUNT(*)" which prevented getting the exact
answer that the present implementation provides would be a severe
misfeature.
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://linuxdatabases.info/info/rdbms.html
"The  test of a  principle  is whether it  applies  even to people you
don't like." -- Henry Spencer

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

   http://archives.postgresql.org

Re: [SQL] REINDEX DATABASE

[Christopher Browne]

> Hello
>
> Would you like to advice to use REINDEX DATABASE on regular basis ?
>
> if (yes)
> how it should be connected with VACUUM FULL ANALYZE which is run
> regularly ?  (reindex before vacuum or vacuum before reindex?)
>
> else
> haw to determine _when_ to run REINDEX ?

If you are doing ordinary VACUUM ANALYZE frequently enough, it
shouldn't be necessary to either VACUUM FULL or REINDEX.

Back in the 7.2 days, there were sorts of update patterns that would
mandate reindexing every so often, as you could get cases where index
pages would be very sparsely populated.  That was alleviated in
version 7.3, I believe, and was clearly evident in 7.4.

You know you need to REINDEX if analysis of an index shows that it is
sparsely populated.  This generally shows up if you do an analyze on
the table and find an index has more pages than tuples.

But if you run VACUUM reasonably frequently, this shouldn't be
necessary...
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of  the Evil  Overlord #153.  "My Legions of  Terror will  be an
equal-opportunity employer. Conversely, when  it is prophesied that no
man  can defeat  me, I  will  keep in  mind the  increasing number  of
non-traditional gender roles." 

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

   http://archives.postgresql.org

Re: [SQL] How to secure PostgreSQL Data for distribute?

[Christopher Browne]

> Premsun Choltanwanich wrote:
>
>> Dear All,
>>   I need to distribute my application that use PostgreSQL as
>> database to my customer. But I still have some questions in my mind
>> on database security. I understand that everybody  who get my
>> application database will be have a full control permission on my
>> database in case that PostgreSQL already installed on their computer
>> and they are an administrator on PostgreSQL. So that mean data,
>> structure and any ideas contain in database will does not secure on
>> this point. Is my understanding correct?
>>   What is the good way to make it all secure? Please advise.
>
> If your customer can access the data, they can access the data.  If
> they have control over the system, they can access the system.
>
> I guess you could build some sort of encryption into your client, but
> that seems pretty easy to circumvent.
>
> The short answer is that there is no good way to do this.  If you are
> worried about this, the technology isn't going to save you.  No
> technology will save you.  Instead, I would highly suggest discussing
> the matter with an attourney and see if there is a legal remedy that
> might provide adequate protection.

It looks as though the Original Poster is in Thailand; if the customer
is elsewhere in Asia, it might become challenging to find a context
where "legal jurisdiction" or "legal remedy" are well enough defined
for this to work out well.

It may be that the prime issue is whether or not the customer is
trustworthy or not; if the answer is "not," and legal remedies are not
easy to get, then there are essentially two choices:

1.  Do not distribute the database.

The customer must access the database from the vendor's site.  

At the extreme end of this, the application would not directly submit
database queries, but rather redefine the application in a
client/server fashion where the customer side submits requests via
some protocol that does not expose anything about the database schema.

2.  If the customer is REALLY not able to be trusted, then maybe they
can't be a customer.
-- 
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.

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

Re: [SQL] Design question: Scalability and tens of thousands of tables?

[Christopher Browne]

> On Nov 4, 2005, at 9:47 AM, [EMAIL PROTECTED] wrote:
>
>> The problem is I am very concerned about scalability with having a
>> different
>> table created for each custom object.  I want to design to site to
>> handle
>> tens of thousands of users.  If each user has 3-5 custom objects the
>> database would have to handle tens of thousands of tables.
>>
>> So it would appear that this is a broken solution and will not
>> scale.  Has
>> anyone designed a similar system or have ideas to share?
>
> Well, before you discount it, did you try out your design? You could
> do initial segregation of user's tables into separate schemas (say,
> schema 'a' -> 'z' according to username or some better hashing
> routine like brute-force round-robin assignment at user creation
> time). Assignment of objects -> schema would be one additional column
> in your centralized user directory table(s).

I don't imagine this would help much with the *true* problem, which is
that a lot of queries inside the DB would have tens of thousands of
tables to go thru rather than (say) hundreds.

If you have 10,000 tables, that means 10,000 entries in pg_class.

Associating them with 26 (or 260) namespaces does nothing to assist
any queries that still have to scan through 10K pg_class entries.
-- 
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxfinances.info/info/wp.html
"Whenever you  find that you  are on the  side of the majority,  it is
time to reform." -- Mark Twain

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

Re: [SQL] Extract table columns in tabular form

[Christopher Browne]

> How do you extract postgresql column names, types and comments in tabular 
> form,
> using an SQL command?. I know they are stored in one of the system tables, but
> I don't know which.

- Table names are in pg_class

- Column names are in pg_attribute, associated to pg_class via
   "where pg_attribute.attrelid = pg_class.oid"

- Comments are in pg_description...  There seem to be links to both
  pg_class and pg_attribute

- Type info is in pg_type, associated to pg_attribute via "atttypid"

There are attributes that you normally don't see, and "dropped"
attributes still exist in pg_attribute, so things are a tad more
complex than what I have said, but the above 4 tables should provide
you all you need...
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://linuxdatabases.info/info/slony.html
Never criticize anybody until  you have walked  a mile in their shoes,
because by that time you will be a mile away and have their shoes.
-- email sig, Brian Servis

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

   http://archives.postgresql.org

Re: [SQL] Merging rows into one result?

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jesper K. 
Pedersen") would write:
> Is it possible to use SQL to merge data into one result?
>
> A theorethical example to explain:
>
> tbl_test (
>   id integer,
>   information varchar(25))
>
> id | information
> ---+--
> 1  | Yo
> 2  | Go away
> 1  | Stay put
> 3  | Greetings
>
> Please note id is not unique and not a primary key.
>
> and I wonder if there is any functions to "merge" data (sort of
> concat'ing).
> A normal: select information from tbl_test where id=1
> would result in the rows
>  Yo
>  Stay put
>
> I would like a single row result in the format of:
>  Yo Stay put
>
> Any ideas on this?

Sure, you could create a custom aggregate to append them using spaces.

Look in the PostgreSQL documentation under "CREATE AGGREGATE."  If you
check the online version at PostgreSQL.org, there are comments showing
examples...
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/wp.html
--Despite Pending :Alarm--

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

   http://www.postgresql.org/docs/faq

Re: [SQL] Good examples of calling slony stored procedures

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Mark Adan") 
wrote:
> I looked there already and didn't find what I needed.  I saw this web
> page from cbbrowne and he briefly talked about using "bare metal" slony
> functions, but doesn't have any examples.

If you look at the source code for the slonik utility, it shows how
the functions actually get used.

In most cases, slonik.c submits fairly simple requests using the
functions in slony1-funcs.sql.

For instance, the slonik MOVE SET (id=1, old origin=11, new origin=22);
command runs a bunch of C "deteriorata" that is a wrapper for:

   select _slony_schema.moveset(1, 22);

Plenty of the commands are about as simple as that.

And this means that, for these "simple" operations, if you want to
submit them via SQL queries, there's a very thin veiling you need to
do to submit the functions as SQL selects.

There are more complex cases, such as FAIL OVER, SET ADD TABLE,
EXECUTE SCRIPT, UPDATE FUNCTIONS, and WAIT FOR EVENT, where there is
considerably complex logic in addition to what is in the stored procs.

Looking at slonik.c is the best thing I can suggest you do...
-- 
"cbbrowne","@","cbbrowne.com"
http://linuxdatabases.info/info/x.html
Signs  of a   Klingon  Programmer  #6: "Debugging?   Klingons  do  not
debug. Our software does not coddle the weak."

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

   http://archives.postgresql.org

Re: [SQL] mail alert

[Christopher Browne]

t...@tim-landscheidt.de (Tim Landscheidt) writes:
> Alvaro Herrera  wrote:
>
>>> > It's on Windows
>
>>> I'd go with notify and a listener written in C using c-client to send
>>> emails, but only because I've used those before.
>
>> I wouldn't write it in C but rather Perl or Python, but whatever suits
>> your fancy should work (Visual Basic anyone?).  The advantages to using
>> a listener program instead of doing it in a trigger or something like
>> that are:
>
>> - transaction semantics are kept; you don't send an email only to find
>> out your transaction has been rolled back for whatever reason, and then
>> send a second email when the transaction is replayed
>
>> - you don't block the database system just because your mail server is
>> down
>
>> - the email can be sent on whatever schedule fits the listener program
>
>> - the listener client can run elsewhere, not only in the database server
>
>> - any further external processing can take place at that time, without
>> bothering the database server
>
>> - other stuff I don't recall ATM
>
> The main disadvantage in using a listener is that it is your
> responsibility to make sure that the listener is listening
> 24/7 - from before the database accepts other connections,
> through network failures, bugs, etc. - otherwise notifica-
> tions will be lost. Therefore I find it much more reliable
> (and easier to program) to copy the relevant data to a table
> "mailqueue" (or whatever) and then process that queue every
> other minute.

Actually, I don't think there's any real disagreement here...

 - The *important* bit is to make sure that the data required to
   generate the email is queued in the database.

 - Whether you poll or use notify/listen is *way* less important.

You could implement the "listener process" a number of ways:

  - It could be a "cron" that wakes up every so often
to do whatever work is outstanding

  - It could be a "polling daemon" that sleeps for a while between
iterations.

That seems a little nicer than the "cron" approach in that it
eliminates a troublesome scenario, namely the case where there's a
lot of work to do (flooded queue?)  so that processing takes longer
than the polling interval, leading to the risk that a second "cron"
starts up while the previous one is still working.

  - It could be a "listening daemon" that listens for notifications to
indicate that work is outstanding

That is a little better than the "polling daemon" in that it doesn't
need to wait the full polling period to start processing new work.

Any of those three approaches are quite viable, as long as you're
careful to cover scenarios like:
 - daemon falling over
 - accidentally starting multiple "queue processors"
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Field or record level encryption / decryption

[Christopher Browne]

hengkyliwand...@gmail.com (Hengky Lie) writes:
> Anyone know what function i can use to encrypt /  decrypt field or
> record ?
>
> When record saved, it saved in encrypt format. When i need to read
> data, i just call decrypt function.
>
> I am using MS Access 2003 and Postgresql 8.3

I'd suggest referring to the fine manual...
http://www.postgresql.org/docs/8.3/static/pgcrypto.html
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] sql error creating function

[Christopher Browne]

Quoth [EMAIL PROTECTED] ("TJ O'Donnell"):
> When I psql -f f.sql
> I get the following error:
> psql:f.sql:10: ERROR:  relation "fragset" does not exist
> CONTEXT:  SQL function "fragments"
>
>> cat f.sql
> Create Or Replace Function fragments(character varying)
>  Returns setof character varying
> As $$
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1c1');
> Select smiles from fragset;
>
> $$ Language SQL;
>
> But, if I paste into a running psql the commands:
>
> Create Temporary Table fragset (smiles character varying);
> Insert into fragset Values ('COCNC');
> Insert into fragset Values ('COCNCc1c1');
> Select smiles from fragset;
>
> it works fine.
>
> What is wrong in the function definition?
> I'm using 8.1.3

Presumably it's that the query plan is evaluated based on the state of
the database *before* the function runs, at which point table
'fragset' does not exist.  Creation of the temp table will work fine,
but the references to it don't, because when the query (to run the
whole thing) is planned, the table doesn't exist.

What you need to do is to generate the various queries on fragset as
dynamic queries, invoked via "execute."  That will cause the planner
to run on each statement...
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/wp.html
"We believe Windows 95 is a walking antitrust violation"
-- Bryan Sparks

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

   http://archives.postgresql.org

Re: [SQL] RES: Lock Problem

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (André José 
Guergolet) would write:
> Sorry, I have a table with 360 rows, in this table I control the state of 
> machines on network:
>
>
> IpState   StateDate
> 172.20.0.39   Running 2006-08-23 00:00:00
> 172.20.0.59   Running 2006-08-23 00:00:00
> 172.20.0.72   Running 2006-08-23 00:00:00
> 172.20.0.84   Running 2006-08-23 00:00:00
> 172.20.0.35   Running 2006-08-23 00:00:00
> 172.20.0.17   Running 2006-08-23 00:00:00
> 172.20.0.28   Running 2006-08-23 00:00:00
> 172.20.0.39   Running 2006-08-23 00:00:00
> 172.20.0.14   Running 2006-08-23 00:00:00
> 172.20.0.33   Running 2006-08-23 00:00:00
> 172.20.0.19   Running 2006-08-23 00:00:00
>
> My system, checks if my script is running in each machine at this
> table, this table has 360 rows and has 50-100 updates per minute in
> columns STATE and STATEDATE.

Question: How often are you vacuuming this table?

If, as you indicate, you're updating about 1/4 of the table each
minute, you should probably VACUUM the table about once a minute.

If you only VACUUM it once an hour or once a day, those 360 tuples
will be spread across 200,000 pages, and need a VACUUM FULL and a
REINDEX to draw the table back down to a decent size.

You can see how badly the table has grown by running the SQL:
  VACUUM VERBOSE machinestates;

This will list various statistics; generally, if you have many more
pages than tuples, there's probably a problem with how often you're
vacuuming...

> I list this states with a webpage. This webpage updates the list
> every 10 seconds. My page executes only "select * from
> machinestates".
>
> If I stop the updates, I never get my page stopped at the select
> command.

I don't understand what you mean by that.

In PostgreSQL, readers don't block writers and writers don't block
readers (unless you expressly ask for them to do so).

> I read about "DIRTY Transaction", is it the way?
> I have another solution?

I'm not sure you have successfully described either the observed
phenomenon or the nature of the problem; I know I don't yet quite
understand what seems wrong.

Sometimes language gets in the way; that seems possible here.  I also
think you're trying to solve the problem before understanding what it
is.

Please try to explain further what phenomenon you are observing; as
details emerge, hopefully someone will recognize what is going on.
Trying to fix it isn't the right thing to do at this point; just try
to explain what you see happening.  Getting to the point where someone
recognizes what is happening is really the goal.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/lsf.html
"Just because the code is intended to cause flaming death is no reason
to get sloppy and leave off  the casts." - Tim Smith, regarding sample
(F0 0F C7 C8) Pentium Death code on comp.os.linux.advocacy

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Create Assertion -- Question from a newbie

[Christopher Browne]

[EMAIL PROTECTED] (Andrew Sullivan) wrote:
> On Wed, Aug 30, 2006 at 04:47:29PM -0400, Gregory Stark wrote:
>> 
>> Do *any* databases implement SQL standard Assertions?
>
> Somebody must've had an implementation, or it'd never have made it
> into the spec ;-)

Supposedly, RDB had this feature...  Of course, it disappeared into
the Oracle empire...
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/slony.html
Signs of a Klingon Programmer - 18. "Perhaps it IS a  good day to die!
I say we ship it!"

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

Re: [SQL] recursive SQL and with clause

[Christopher Browne]

After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (chrisj) belched 
out:
> I was just wondering when we could reasonably expect recursive SQL to be
> added to Postgres?
>
> I saw some posts from 2004 that made it sound like it was imminent,
> but I guess something went wrong?  I believe the WITH clause is a
> pre-requisite for recursive SQL, however I do find the WITH clause
> also very useful in decomposing very complex SQL into understandable
> chunks.

If I recall correctly, Jim Nasby was considering it; he hadn't heard
much interest back, and got to other things in the 8.2 cycle.

There has been some discussion on strategy for an approach to it for
the 8.3 cycle; you can expect that not to progress much 'til 8.2.0
gets released.

Bringing it back up on pgsql.hackers, some time soon thereafter, would
be not the worst idea of all time :-).
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/wp.html
Programming  is one   of  the   most  difficult  branches of   applied
mathematics;  the   poorer   mathematicians hadbetter  remain pure
mathematicians.  -- Edsger W. Dijkstra

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Desc Commnad in pgsql?

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (VG) wrote:
> Hello All,
> I like to know how can I achieve the same functionality that is give by desc 
> commnad in mysql or oracle.
> Also specify me the book related to pgsql as a beginner.
> Presently my task is going to be communication of ruby with pgsql
> Thanks in advanced.

If you were referring to the usage of "desc" to indicate "descending
order", as in:
   select * from some table order by id desc
well, that's pretty standard SQL usage, and will work much as it
would in MySQL(tm) or Oracle.

If you're looking for ways to "describe" a table, there are two
mechanisms:

  1.  SQL standard (probably SQL:1993) describes an
  "information_schema" which contains tables or views that allow
  querying database metadata in a fairly standard fashion.

  PostgreSQL supports that.

  2.  Probably easier and friendlier, albeit nonportable, is to use
  the psql "\d" command.

  Here's an example:

[EMAIL PROTECTED]:~> psql ledgersmb 
Saturday 
12:51:13
Welcome to psql 8.1.10, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

ledgersmb=# \d acc_trans
Table "public.acc_trans"
 Column |  Type   |  Modifiers  

+-+-
 trans_id   | integer | 
 chart_id   | integer | not null
 transdate  | date| default date('now'::text)
 source | text| 
 cleared| boolean | default false
 fx_transaction | boolean | default false
 project_id | integer | 
 memo   | text| 
 invoice_id | integer | 
 amount | numeric | 
 entry_id   | bigint  | not null default 
nextval('acctrans_entry_id_seq'::regclass)
Indexes:
"acc_trans_pkey" PRIMARY KEY, btree (entry_id)
"acc_trans_chart_id_key" btree (chart_id)
"acc_trans_source_key" btree (lower(source))
"acc_trans_trans_id_key" btree (trans_id)
"acc_trans_transdate_key" btree (transdate)
Foreign-key constraints:
"$1" FOREIGN KEY (chart_id) REFERENCES chart(id)
"$2" FOREIGN KEY (chart_id) REFERENCES chart(id)
"$3" FOREIGN KEY (chart_id) REFERENCES chart(id)
"$4" FOREIGN KEY (chart_id) REFERENCES chart(id)
"acc_trans_chart_id_fkey" FOREIGN KEY (chart_id) REFERENCES chart(id)

You can query the schemas for all sorts of objects, complete with
tab-completion; type "\?" at the psql prompt to see all of the
internal psql commands.  There is a whole section entitled
"Informational" that shows modifiers to \d to query various sorts of
objects.

For instance:
\dt will list all tables
\ds will list all sequences
\dv will list all views
and there's a further cast of ~20 variants for various different sorts
of objects.
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/postgresql.html
"Java and C++ make you think that the new ideas are like the old ones.
Java is the most distressing thing to hit computing since MS-DOS."
-- Alan Kay

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql