[GENERAL] Functions on tables

[Brendan Jurd]

In the object-relational context, the definition of a "relation" is
much the same as the idea of a "class"; the columns in a table are
analogous to the attributes of a class.  The names of Postgres' system
catalogs reflect this correlation (pg_class, pg_attribute).

Likewise, each tuple within a relation is like an instance of the class.

So I was thinking, why is it we have such a direct representation of
class attributes (in the form of columns), but no equivalent
representation of class methods?

Say you have the following table:

CREATE TABLE person (
id serial PRIMARY KEY,
firstname text NOT NULL,
lastname text NOT NULL
);

Then you define a function:

CREATE FUNCTION person_name(firstname text, lastname text) RETURNS text AS $$
SELECT $1 || ' ' || $2;
$$ LANGUAGE SQL IMMUTABLE;

So now you can do

SELECT id, person_name(firstname, lastname) AS name FROM person ORDER BY name;

That works fine, but wouldn't it be far more elegant if you could do
this instead:

CREATE TABLE person (
id SERIAL PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' ||
lastname; $$ LANGUAGE SQL IMMUTABLE
);

Now the function name() belongs to the "person" table: it is, in
effect, a method of the "person" class.  Which means we can do this:

SELECT id, name() FROM person ORDER BY name();

Just as with methods in an OO programming language, when you call
name() on a tuple of the "person" relation, it has access to the
attributes of that tuple (here firstname and lastname).  There is no
need to pass arguments to the function, nor any need to actually know
which attributes of "person" go into making up the return value of
name().  You could later decide to add an attribute for a person's
preferred name, or middle initial, and then factor that into the logic
of name() without the query author needing to know anything about it.

Of course there would be implementation challenges, and admittedly I
haven't considered those, but on the surface this feels like a good
idea.  It taps into some of the real usefulness of OOP, and it uses a
feature we already have: user-defined functions.

I look forward to your comments.

Regards,
BJ

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

  http://archives.postgresql.org/

Re: [GENERAL] Functions on tables

[Tom Lane]

"Brendan Jurd" <[EMAIL PROTECTED]> writes:
> That works fine, but wouldn't it be far more elegant if you could do
> this instead:

> CREATE TABLE person (
>  id SERIAL PRIMARY KEY,
>  firstname TEXT NOT NULL,
>  lastname TEXT NOT NULL,
>  FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' ||
> lastname; $$ LANGUAGE SQL IMMUTABLE
> );

90% of the value this would have is already available with views,
I think, without going outside bog-standard SQL:

create view ...
firstname || ' ' || lastname as name,
...

Also, there's already a Berkeley-era syntax hack in PG that gets much of
the rest: if x is of composite type, the notations x.y and y(x) are
interchangeable.  Thus:

regression=# create function name(person) returns text as $$
regression$# select $1.firstname || ' ' || $1.lastname
regression$# $$ language sql immutable;
CREATE FUNCTION
regression=# select person.name from person;
   name
--
 joe blow
(1 row)

> Now the function name() belongs to the "person" table: it is, in
> effect, a method of the "person" class.  Which means we can do this:
> SELECT id, name() FROM person ORDER BY name();

[ itch... ]  That seems to risk breaking a whole lot of existing code by
introducing name collisions --- the entire namespace of ordinary
functions is at risk as soon as you have any of these per-table
functions, if they can be called like that.

But having said all that, I think there are bits of SQL2003 that do some
of what you're after.  I don't think anyone has looked hard at what
would be involved in merging those new SQL features with historical
Postgres behaviors.

regards, tom lane

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

Re: [GENERAL] Functions on tables

[Brendan Jurd]

On 12/17/06, Tom Lane <[EMAIL PROTECTED]> wrote:

90% of the value this would have is already available with views,
I think, without going outside bog-standard SQL:


Views also work fine, but one of the big advantages of having table
methods is that all the things your table can do are contained within
the table definition.  Your table definition becomes like the API of a
class.  When you use separate views and/or functions to provide
methods for a table, your schema becomes pretty scattered (I know mine
is).  It can be non-trivial to find, say, all functions that relate to
a person tuple.


Also, there's already a Berkeley-era syntax hack in PG that gets much of
the rest: if x is of composite type, the notations x.y and y(x) are
interchangeable.  Thus:


I didn't know about that, and you're right, this actually delivers a
lot of the notational convenience that I'm looking for.  But again,
you don't win the ability to look at your table definition and
immediately understand what methods the table exposes.

It looks like you can't use this hack to conveniently handle methods
that have arguments.  For example, say you had a method to return a
person's birthday in a given year, defined as "birthday(person, int)
returns date".  You wouldn't be able to write person.birthday(2007) in
a query and get the expected result.


[ itch... ]  That seems to risk breaking a whole lot of existing code by
introducing name collisions --- the entire namespace of ordinary
functions is at risk as soon as you have any of these per-table
functions, if they can be called like that.



What if we used scope resolution?  That is, Postgres first looks for
functions which are local to the table, and if it doesn't find a
match, then looks for functions in the normal namespace, perhaps
raising a notice to warn the user of the ambiguity.

Say there was a function name() in the public namespace.  You can then
easily resolve the ambiguity by specifying either person.name() or
public.name().

Most front-end queries have more than one table in them, so in
practice I think you'll almost always be using table aliases and
identifying your methods explicitly anyway, e.g.:

SELECT p.name(), a.label() as address
FROM person p INNER JOIN address a ON p.postal_address = a.id;

... so I'm not passionately attached to the idea of being able to call
the method without prefixing the table name.

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

Re: [GENERAL] Subcribing to this list, what`s the secret?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> I seem to have a natural knack for hitting the ruts around here, which
> is exciting. I've tried to subscribe to the list using both yahoo and
> gmail email accounts and the webform always reports "The email address
> you entered does not appear to be valid."

Sorry, there was a recent DNS problem that was causing some false
negatives when verifying email addresses. Please try it again, or
simply mail me the addresses you wish to have subscribed, and
whether you would like them set as "nomail" or not, and I'll take care
of it. The same offer to anyone else having recent subscription problems.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200612141112
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFFgXf1vJuQZxSWSsgRAhLfAKCxniJBwl04lUhqW4pm+W00yRGLUQCfZRnT
PBFyyRoQGjCluwEyqIMzprc=
=3+i3
-END PGP SIGNATURE-



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

Re: [GENERAL] Subcribing to this list, what's the secret?

[Ian Harding]

If you have a dot in your gmail username, take it out.  Gmail ignores
it and validation scripts often puke on it.

Then use that email as your reply to, not some nonexistent carp.

- Ian

On 12/13/06, wheel <[EMAIL PROTECTED]> wrote:

I seem to have a natural knack for hitting the ruts around here, which
is exciting. I've tried to subscribe to the list using both yahoo and
gmail email accounts and the webform always reports "The email address
you entered does not appear to be valid." I would like to use either
yahoo or gmail because I am very interested in keeping spam down on my
main email accounts (see Jan 7 2006 thread 'E-mail harvesting on PG
lists?'). I've been posting via a newsreader but it seems that there is
some strange pattern with messages not making it on the the list using
that pipe...all of my posts show on the google archive of this list but
a good number (25%?) seem to be missing from the mailing list archives
that are found on the postgres site. Example, it seems that both of my
responses to Scott Marlowe never made it to the list, but are somehow
listed in google, and on the newsgroup. Scott was PO'd that I didn't
respond to his first post, that got me to check into why he didn't.

I posted about this issue, the failure of the website subscription form
to accept (at least) an @yahoo.com email acct, and no one replied
(though that was one of the posts that didnt' seem to make it onto the
mailing list, but is on google & newsgroup). Can someone tell me what
the secret is, or check the webform at postgresql.org and make sure it's
in order?

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

   http://archives.postgresql.org/



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

Re: [GENERAL] [pgsql-www] Subcribing to this list, what's the

[Arturo Perez]

In article <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED] ("Joshua D. Drake") wrote:

> 
> I also have not been able to duplicate the problem from multiple yahoo
> and gmail tests. I did however note that postgresql.org will not let you
> subscribe as [EMAIL PROTECTED] . Is .not even a valid suffix?
> 
> Joshua D. Drake
> 

I had a similar problem as the OP when I tried to subscribe to the docs 
mailing list.  And the email I used was the same as the one I used to 
subscribe to this list.

I put in the email, checked not to receive email nor digest (as I use 
NNTP) and it wouldn't take the email.  Again, it was the same email Iuse 
for the other pgSQL lists.

-arturo

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

[GENERAL] Stored Procedure and Trigger they puzzle me

[Lars Heidieker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,

I just started to write my first stored procedure in plpgsql and  
installed a trigger for it.


The two Tables are:

CREATE TABLE ltlocation (
  "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
  name varchar(30) NOT NULL default '',
  "description" varchar(254) NOT NULL default '',
  "parent" int4,
  type int2 NOT NULL default '0',
  PRIMARY KEY  (id)
)  ;

just to hold a tree Structure and the second one is:

CREATE TABLE ltlocationpath (
  "ltlocation_id" int4 NOT NULL default '0',
  "ltlocancester_id" int4 NOT NULL default '0',
  PRIMARY KEY  (ltlocation_id,ltlocancester_id)
)  ;

where the second one holds a materialized path view of the first one.
These constraints are defined:

ALTER TABLE ONLY ltlocation ADD CONSTRAINT parent_fkey
		FOREIGN KEY (parent) REFERENCES ltlocation(id) ON UPDATE CASCADE ON  
DELETE CASCADE;

ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocation_fkey
		FOREIGN KEY (ltlocation_id) REFERENCES ltlocation(id) ON UPDATE  
CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocancester_fkey
		FOREIGN KEY (ltlocancester_id) REFERENCES ltlocation(id) ON UPDATE  
CASCADE ON DELETE CASCADE;


The Stored Procedure is:

CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$
DECLARE
workid integer := 0;
BEGIN   
IF tg_op = 'UPDATE' THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
END IF;

workid := new.id;
WHILE workid > 0 LOOP
BEGIN
			EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,  
ltlocancester_id) '

|| 'VALUES (' || new.id || ', ' || workid || 
')';
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;

SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
RETURN new;
END;
$$ LANGUAGE plpgsql;

And the Trigger is defined as:

CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON  
ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();


The strange thing is:
insert is OK (materialized path gets populated)
update of parent column is OK old values get delete and new ones get  
inserted
but if the exception handling of the unique_violation exception is  
removed an update on the id column fails, with
an duplicate pkey violation an the self reference in the materialized  
path eg for the values (25, 25)


It works OK with ignoring the exception but why is the exception  
thrown in the first place.


The postgresql version 8.1.5

- --

Viele Grüße,
Lars Heidieker

[EMAIL PROTECTED]
http://paradoxon.info

- 

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
 -- Friedrich Nietzsche



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)

iD4DBQFFgbkKcxuYqjT7GRYRArhdAJ9s9uGGJX34mD2hGXgZxF78ZbBXIgCY6RvE
jhAObk1zUpvAZ4gGnFAk5w==
=qyV9
-END PGP SIGNATURE-

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

Re: [GENERAL] [Oledb-dev] A major rewrite of the Postgres OLE DB Provider.

[Shachar Shemesh]

Jeremy Lea wrote:
>  - Runs multiple queries (using PQexec) if you pass a command with no
>parameters and IID_NULL for the return record set.
>   
You should be aware that there is a security advantage to NOT being able
to do that, in fact.

On the plus side, it does allow servers older than 7.4 to work with the
provider, under certain cases. On the don't-care side, so what?
>  - Proper type handling.
>   
I'd love an explanation in what, you think, was missing.
>  - Much cleaner code (IMHO), no more STL or exceptions.
>   
A matter of taste. I think this design breaks every OOD instruction out
there (read - no switch-case on child classes).
>  - IMultipleRowsets.  libpq can't return multiple results.  What was
>there was a complete hack.
>   
No, it was an interface between provider and server. It was documented,
and was used.
>  - Parsing of parameters on stored procedures.  This was a hack,
Yes, but the hack is in the OLE-DB's specs. Quite a number of programs
don't work without it.
>  which
>was being done at the wrong place in the code.
>   
I'd love it if you elaborated on that.
>  - Support for some types (_text, utinyint, varcharci)
>  - TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is
>no OLE DB type for this (so you can't go backwards).
>   
If you cannot support data-types that are implemented in PG, and have a
reasonable representation in OLE DB, what makes this type support "proper"?

Also, if I understand my very brief passing over the code correctly, if
I have two coloumns, one of type timestamp and one of type timestamptz,
and I read both in, and then send a parametrized update for both to the
same values I read, one of them will change.
>  - check threading and locking.
>   
Unless you switched the provider from "apartment" mode (and I see you
haven't), you don't need to. It will run in a single thread anyways.
> I hope this is useful to someone.  I would welcome any patches to add
> other features.  I'm managing this in a local CVS repository at the
> moment...  Not quite sure what I'm going to do about this and the
> PgFoundry project yet.
>   
Then allow me to make a suggestion, if I may.

We're already (and I am, at least to some extent, to blame for the lack
of activity here) in a pretty bad place. Having two forks on a project
that only lists one developer means something has gone very wrong with
the project already. In fact, we now have as many forked versions as we
do developers. If the mailing list was swarming with rejected patches,
I'd understand this situation. As is, I think this was totally uncalled for.

The way to manage open source projects is to "release often, release
early". This gives you early access to feedback (and, at the very worst,
is still not worse off than keeping a local copy). For example, if you
started sending patches when you just started off, I'm sure a more
reasonable discussion of what is the best way for the type system to be
like, thus possibly leading to an implementation that was:
1. Single - i.e. less confusing for the users.
2. Better than either existing implementations.

It's obviously too late for that now. What I suggest is this:
Open a new pgfoundry project for your provider. Please also rename it.
It is confusing enough to have two driver implementations. Having two
driver implementations with the same DLL name is really too much.

I think that it would be best if you did NOT open a new mailing list for
the fork, but used oledb-devel, at least initially. I think that it
would be best, long term, to only have one provider, and a unified list
will help get the community to vote on which one it should be.

Finally, a word of caution. The ATL code is copyrighted by Microsoft.
The license under which they gave you the code (appears as part of
EULA.TXT in Visual Studio 6) is NOT LGPL compatible (nor any other free
license compatible, but you are bound to LGPL by my copyright). This
means, as far as I can tell, that you are not allowed to copy code from
ATL and include it, modified or not, in the provider's code. Had you
send me those pieces of code as patches, they would have been rejected
immediately for that reason alone.

Here is the relevant section from the VS6 EULA (emphasis mine):
> 3.8Redistributable Code-Visual C++ and Visual Studio: Microsoft
> Foundation Classes (MFC), Template Libraries (ATL), and C runtimes
> (CRTs).  If this EULA accompanies Visual C++ or Visual Studio, then in
> addition to the rights granted in Section 1, Microsoft grants you the
> right to use and modify the source code version of those portions of
> the SOFTWARE PRODUCT that are identified as MFC, ATL, or CRTs
> (collectively, the "VC Redistributables"), for the sole purposes of
> designing, developing, and testing your software product(s). Provided
> you comply with Section 4.1 and you rename any files created by you
> that are included in the Licensed Product (defined below), Microsoft
> grants you a nonexclusive, royalt

Re: [pgsql-www] [GENERAL] Subcribing to this list, what's the

[Richard Broersma Jr]

> I also have not been able to duplicate the problem from multiple yahoo
> and gmail tests. I did however note that postgresql.org will not let you
> subscribe as [EMAIL PROTECTED] . Is .not even a valid suffix?

I wasn't able to create an account for pg_docs mailing list the other day.  I 
figured that
everyone was tired of me reporting what I incorrectly thought were bugs in the 
documentation. ;-)

Regards,

Richard Broersma Jr.

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

Re: [GENERAL] Failed to set permision Win2k3-PG Installation Issues

[objectmodelol]

[EMAIL PROTECTED] wrote:
> Everytime I try to install Postgre 8.2, I get the following error:
>
>
> Failed to set permissions on the installed files. Please see the
> logfile.
>
> The thing is, I'm telling PG to use my Administrator account (the
> account I'm currently logged in as) and I'm pretty sure it has access
> to everything.
>
> Any idea?


It installed on my AMD machine (single core) just fine but on my Core 2
Duo (single cpu, dual core)...it fails with "failed to set
permissions".

Maybe it's a bug with dual core machines??


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

[GENERAL] Authenticating with x509 certificate

[Andre Lebedev]

Hello.

Based on the information I was able to find so far, it seems that
Postgres has the capability of authenticating users through x509
certificate. But I can't find instructions on how to do it. 

Any help will be greatly appreciated!

Regards,
Andre.

Re: [GENERAL] out of memory woes

[mwrynn]

Thank you all for the replies. Overcommit is indeed disabled - the
reason we disabled it is that this very same process caused the Linux
oom-killer to kill processes. This was perhaps two months ago. The
setting was changed to, and is currently set to: vm.overcommit_memory=2
...All has been well until recently. I will research overcommit
further.

Tom, below is the information you requested.

Thank you,
Mark

/* SCHEMA DEFINITIONS */

dev_stage=> \d sm_mbs_geo_pool_distribution
  Table
"secmaster.sm_mbs_geo_pool_distribution"
   Column| Type  |
   Modifiers
-+---+
 geo_distribution_id | integer   | not null default
nextval('sm_mbs_geo_pool_distribution_geo_distribution_id_seq'::regclass)
 issue_id| integer   | not null
 pool_prefix | character varying(2)  | not null
 pool_number | character varying(7)  | not null
 distribution_type   | character varying(20) |
 state   | character varying(3)  |
 as_of_date  | date  | not null
 loan_count  | integer   |
 loan_count_ratio| numeric(5,2)  |
 percent_of_upb  | numeric(5,2)  |
 aggregate_upb   | numeric(16,3) | not null
 cusip   | character(9)  |
 agency  | character(3)  |
 origination_year| character(4)  |
Indexes:
"sm_mbs_geo_pool_distribution_pkey" PRIMARY KEY, btree
(geo_distribution_id)
"sm_mbs_geo_pool_distribution_id_state_upb" btree (issue_id, state,
percent_of_upb) CLUSTER
"sm_mbs_geo_pool_distribution_state_lcnt" btree (issue_id, state,
loan_count)
"sm_mbs_geo_pool_distribution_state_pct_idx" btree (state,
percent_of_upb, loan_count)
Foreign-key constraints:
"sm_mbs_geo_pool_distribution_issue_id_fkey" FOREIGN KEY (issue_id)
REFERENCES sm_mbs_pool_detail(issue_id) ON DELETE CASCADE

dev_stage=> \d sm_mbs_loan_distribution
   Table
"secmaster.sm_mbs_loan_distribution"
Column| Type  |
   Modifiers
--+---+-
 rec_type | character(1)  | not null
 agency   | character(3)  | not null
 pool_number  | character(7)  | not null
 cusip| character(9)  | not null
 issue_id | integer   | not null
 eff_date | date  | not null
 value| character(6)  | not null
 display_sort_order   | character(4)  | not null
 rpb  | numeric(20,2) | not null
 pct_rpb  | numeric(5,2)  | not null
 loans| integer   | not null
 loan_distribution_id | integer   | not null default
nextval('sm_mbs_loan_distribution_loan_distribution_id_seq'::regclass)
Indexes:
"sm_mbs_loan_distribution_pkey" PRIMARY KEY, btree
(loan_distribution_id)
"sm_mbs_loan_distribution_idx" UNIQUE, btree (issue_id, rec_type,
value)
"sm_mbs_loan_distribution_idx1" btree (rec_type, value, pct_rpb)
"sm_mbs_loan_distribution_rec_type_loans" btree (issue_id,
rec_type, value, loans)
"sm_mbs_loan_distribution_rec_type_pct_rpb" btree (issue_id,
rec_type, value, pct_rpb) CLUSTER
"sm_mbs_loan_distribution_rec_type_rpb" btree (issue_id, rec_type,
value, rpb)


/* MEMORY CONTEXT DUMP AND ERROR MESSAGE */

TopMemoryContext: 58800 total in 7 blocks; 6480 free (6 chunks); 52320
used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used
SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks;

[GENERAL] strange slow performance

[michael . guiard]

Hi !
I have a strange slow performance problem depending on client computer.
I have a postgresql server (8.1.3) running on an XP system (P4 3GHz,
1Go RAM).

I have 3 other computers from which I make request on the database : 2
XP(named 1 & 2) and 1 2K.

>From one XP(1) and from the 2K computer, I have no slow performance
problem. I ask for a select of 4000 records, the request is processed
in less than 1 seconde.
>From the last XP (2) computer, the same request takes more than 10
seconds. From this computer I have no other network problem, I can
copy, paste network files without any performance problem.

The 3 clients use odbc driver 8.01.02.00.

I tried to install the postgre sql server on the xp (2) and make the
same request from 3 other computers : no problem, the process takes
less than 1 second from all clients

what goes wrong ??
thanks for your help

Mike


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

Re: [pgsql-www] [GENERAL] Subcribing to this list, what's the secret?

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> wheel wrote:
>> I seem to have a natural knack for hitting the ruts around here, which 
>> is exciting. I've tried to subscribe to the list using both yahoo and 
>> gmail email accounts and the webform always reports "The email address 
>> you entered does not appear to be valid."

> I did see your previous email but thought you must have been doing
> something wrong.

Per this morning's discussion, there was a DNS misconfiguration that
prevented the webserver from resolving email addresses.  It's claimed
to be corrected now.

regards, tom lane

---(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: [GENERAL] Subcribing to this list, what's the secret?

[Bruce Momjian]

wheel wrote:
> I seem to have a natural knack for hitting the ruts around here, which 
> is exciting. I've tried to subscribe to the list using both yahoo and 
> gmail email accounts and the webform always reports "The email address 
> you entered does not appear to be valid." I would like to use either 
> yahoo or gmail because I am very interested in keeping spam down on my 
> main email accounts (see Jan 7 2006 thread 'E-mail harvesting on PG 
> lists?'). I've been posting via a newsreader but it seems that there is 
> some strange pattern with messages not making it on the the list using 
> that pipe...all of my posts show on the google archive of this list but 
> a good number (25%?) seem to be missing from the mailing list archives 
> that are found on the postgres site. Example, it seems that both of my 
> responses to Scott Marlowe never made it to the list, but are somehow 
> listed in google, and on the newsgroup. Scott was PO'd that I didn't 
> respond to his first post, that got me to check into why he didn't.
> 
> I posted about this issue, the failure of the website subscription form 
> to accept (at least) an @yahoo.com email acct, and no one replied 
> (though that was one of the posts that didnt' seem to make it onto the 
> mailing list, but is on google & newsgroup). Can someone tell me what 
> the secret is, or check the webform at postgresql.org and make sure it's 
> in order?

I did see your previous email but thought you must have been doing
something wrong.  Going to:

http://www.postgresql.org/community/lists/subscribe

and entering my gmail address worked, and I tried a username that
contained a period, and that worked too.

Interestingly, I can't find your previous email that I saw in our
archives, or in google archives.  What email address where you trying?

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] How to check constraints before call of simple_heap_insert()?

[Vanyel]

But SPI_exec() takes string containing an SQL command :-(
I need to pass previously prepared HeapTuple variable.
Best regards :-)


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

Re: [GENERAL] [Oledb-dev] A major rewrite of the Postgres OLE DB Provider.

[Konstantin Izmailov]

Great! I will take a look and let you know. I had rewritten the Provider
about a year ago as well. However, I did not have time to clean-up most of
the new bugs and decided to keep the code for myself. There is certain
interest in the US to the more capable Provider.

On 12/15/06, Jeremy Lea <[EMAIL PROTECTED]> wrote:


Hi all,

Because of various things, I have needed to move some stuff that I've
been working on onto a Postgres DB.  It makes very heavy use of ADO from
Excel, and so I was running into a number of little annoyances with the
existing OLE DB provider...  Especially I could not get parameterised
queries to run, nor could I run multiple commands in one block (which I
need for speed to load big chunks of data).

So, I started hacking on the Provider, and ended up almost completely
rewriting it.  I have placed a copy at:

http://people.freebsd.org/~reg/pgoledb-20061215.zip

Things that it has gained:

- Support for the IColumnsRowset interface, which means that ADO's
  client cursor engine doesn't need to parse the SQL to make up UPDATE
  and INSERT queries.
- Support for IDBInfo and IDBSchemaRowset interfaces, which also help
  ADO CCE.
- Runs multiple queries (using PQexec) if you pass a command with no
  parameters and IID_NULL for the return record set.
- Proper type handling.
- Passing all of the connection string options (hostaddr, ssl, etc.)
- Lots of bug fixes.
- Much cleaner code (IMHO), no more STL or exceptions.

Things it has lost:

- IMultipleRowsets.  libpq can't return multiple results.  What was
  there was a complete hack.
- Parsing of parameters on stored procedures.  This was a hack, which
  was being done at the wrong place in the code.
- Support for some types (_text, utinyint, varcharci)
- TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is
  no OLE DB type for this (so you can't go backwards).
- Probably some other things...

I've been testing the code some, and it's working with my application,
but there are probably still many bugs.  I would love it if people could
also test it and report errors!  I'm trying to get the OLE DB
conformance tests up and running, and hopefully I will find more bugs
that way.

I still need to:
- redo some of the Schema support (realised my design was bad...)
- tidy up some of the tracing.
- prepared queries.
- there are a number of properties where I'm not sure of the right
  value.
- add more support for pg_types (especially the array/vector types).
- check threading and locking.
- check error reporting.
- probably a few other things...

I hope this is useful to someone.  I would welcome any patches to add
other features.  I'm managing this in a local CVS repository at the
moment...  Not quite sure what I'm going to do about this and the
PgFoundry project yet.

Regards,
-Jeremy
___
Oledb-devel mailing list
Oledb-devel@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/oledb-devel

[GENERAL] Performance of outer joins?

[Benjamin Smith]

I have a situation that can be summarized to the following: 

-- day in 20061215 format 
Create table calendar (
day integer unique not null
); 

Create table customers (
id serial unique not null, 
name varchar, 
address varchar, 
); 

Create table deliveries (
customers_id integer not null references customers(id), 
calendar_day integer not null references calendar(day), 
delivered bool not null default false, 
unique(customers_id, calendar_id)
); 

Imagine tens of thousands of customers, a few million deliveries. A query 
that's structurally similar to the following query is rather slow. It's 
taking over 30 seconds, climbing fast on reasonable HW. (SMP Opteron, 10k 
SCSI, 4 GB RAM) If I remove the outer join, performance is < 1 second. 

SELECT customers.id as customers_id, 
customers.name AS customers_name, 
calendar.day AS calendar_day, 
CASE WHEN (deliveries.delivered IS NULL) THEN 'n/a' 
WHEN (deliveries.delivered=TRUE) THEN 'yes'
ELSE 'no' END AS delivered
FROM customers 
JOIN calendars ON 
(
-- GIVE A CALENDAR OF POSSIBLE DAYS FOR DELIVERIES 
calendar.day < 20061201
AND calendar.day >= 20060101
) 
LEFT OUTER JOIN deliveries ON 
( 
customers.id=deliveries.customers_id 
AND deliveries.calendar_day=calendar.day 
) 
; 

What can I do to improve the performance of this oft-used query? Is there a 
better way to do this, or am I doomed to looping thru results and parsing the 
results in code? 

Thanks, 

-Ben 

-- 
"I kept looking around for somebody to solve the problem. 
Then I realized I am somebody" 
   -Anonymous

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

[GENERAL] Regular expression and Group By

[Clodoaldo]

Regular expression and Group By

There is a varchar column which I need to group by an "uppered"
substring inside '[]' like in 'xxx[substring]yyy'. All the other lines
should not be changed.

I can do it using a union. I would like to reduce it to a single
query, but after much thought I can't. So I'm asking the regex experts
a hand.

This is how I do it:

-- drop table test_table;
create table test_table (tname varchar, value integer);
insert into test_table values ('[ab]x', 1);
insert into test_table values ('[ab]y', 2);
insert into test_table values ('[Ab]z', 3);
insert into test_table values ('w[aB]', 8);
insert into test_table values ('[abx', 4);
insert into test_table values ('ab]x', 5);
insert into test_table values ('xyz', 6);
insert into test_table values ('Xyz', 7);

select
  count(*) as total,
  tname,
  sum(value) as value_total
from (

  select
 substring(upper(tname) from E'\\[.*\\]') as tname,
 value
  from test_table
  where tname ~ E'\\[.*\\]'

  union all

  select tname, value
  from test_table
  where tname !~ E'\\[.*\\]'

) as a
group by tname
order by tname;

The result which is correct:

total | tname | value_total
---+---+-
4 | [AB]  |  14
1 | [abx  |   4
1 | ab]x  |   5
1 | xyz   |   6
1 | Xyz   |   7
(5 rows)

Regards,
--
Clodoaldo Pinto Neto

---(end of broadcast)---
TIP 1: 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] Failed to set permision Win2k3-PG Installation Issues

[objectmodelol]

Everytime I try to install Postgre 8.2, I get the following error:


Failed to set permissions on the installed files. Please see the
logfile.

The thing is, I'm telling PG to use my Administrator account (the
account I'm currently logged in as) and I'm pretty sure it has access
to everything.

Any idea?


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

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

Re: [GENERAL] Failed to set permision Win2k3-PG Installation Issues

[Magnus Hagander]

[EMAIL PROTECTED] wrote:
> [EMAIL PROTECTED] wrote:
>> Everytime I try to install Postgre 8.2, I get the following error:
>>
>>
>> Failed to set permissions on the installed files. Please see the
>> logfile.
>>
>> The thing is, I'm telling PG to use my Administrator account (the
>> account I'm currently logged in as) and I'm pretty sure it has access
>> to everything.
>>
>> Any idea?
> 
> 
> It installed on my AMD machine (single core) just fine but on my Core 2
> Duo (single cpu, dual core)...it fails with "failed to set
> permissions".
> 
> Maybe it's a bug with dual core machines??

That would be *very* unlikely. It's almost certainly something else
different between these machines, or the accounts used to install from.
Nothing with the permissions stuff should make any difference whatsoever
 depending on number of CPUs or cores.

//Magnus

---(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: [GENERAL] Authenticating with x509 certificate

[Magnus Hagander]

Andre Lebedev wrote:
> Hello.
> 
> Based on the information I was able to find so far, it seems that
> Postgres has the capability of authenticating users through x509
> certificate. But I can't find instructions on how to do it.
> 
> Any help will be greatly appreciated!
> 

It does not, unfortunately (yet - IIRC someone was working on it). It
can be configured to *require* a trusted client-side X509 certificate,
but it can't be used for authenticating the user.

To require a client certificate, create a list of root certs to match
against - see http://www.postgresql.org/docs/8.2/static/ssl-tcp.html.

//Magnus

---(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: [GENERAL] Performance of outer joins?

[Tom Lane]

Benjamin Smith <[EMAIL PROTECTED]> writes:
>   FROM customers 
>   JOIN calendars ON 
>   (
>   -- GIVE A CALENDAR OF POSSIBLE DAYS FOR DELIVERIES 
>   calendar.day < 20061201
>   AND calendar.day >= 20060101
>   ) 

Haven't you written a cross-product join here?  Seems like a bad
idea if you'd like it to be fast.  Especially since you then left-joined
it to something else, meaning that every row in the cross product
must in fact generate output.  Do you really want to be deluged with
a table showing all the customers you DIDN'T deliver to on each day,
for every day in the year?

regards, tom lane

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

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

Re: [GENERAL] Functions on tables

[TJ O'Donnell]

I wholeheartedly support the approach BJ is advocating.
The notion that methods (functions) and variables (tables)
can be defined together is a very useful OO approach.
I too find it difficult to recall which functions "belong"
to which tables.  Of course, some of my functions are very
generic and wouldn't appropriately "belong" to any one table,
but many are meant solely to operate on data in one type of
object (table).

I've taken to using schemas to collect together functions and tables
that "belong" together.  This requires the use of the schema name,
as you say BJ,

... so I'm not passionately attached to the idea of being able to call
the method without prefixing the table name.


In my approach, the schema name becomes the object name and the 
functions "belong" to the schema.  Most OO approaches only allow one

definition of variables (tables), and I can easily allow each schema
to have only one table.  But I can also use multiple tables.
The extra tables can be instances, much like BJ's use of rows as
instances.  Using separate tables allows me to have groups of
instances that are grouped together for some reason.  I can also
have tables that are sub-classes of the original table.

TJ
http://www.gnova.com/

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

Re: [GENERAL] out of memory woes

[Tom Lane]

[EMAIL PROTECTED] writes:
> Tom, below is the information you requested.

Well, the table definitions look ordinary enough, but this is odd:

> SPI Exec: 528474160 total in 69 blocks; 309634880 free (9674592
> chunks); 218839280 used

Something's leaking a lot of memory within a SPI call, which means
either a plpgsql-invoked query or a SPI_execute-or-sibling call
from a C-language function.  I can see from the memory dump that
you're using some of both, but no details beyond that.  Also,
it seems the actual failure is not coming from CLUSTER at all,
but during an attempt to report a message via RAISE:

> <2006-12-13 14:41:46 EST>ERROR:  out of memory
> <2006-12-13 14:41:46 EST>DETAIL:  Failed on request of size 20.
> <2006-12-13 14:41:46 EST>CONTEXT:  SQL statement "SELECT   $1 [ $2 ]"
>   PL/pgSQL function "cluster_load_tables" line 31 at raise

So I think you're gonna have to show us the functions you're using ...

regards, tom lane

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