Re: [HACKERS] Release Scheduales: 7.2.4 & 7.3.2

[Tom Lane]

Justin Clift <[EMAIL PROTECTED]> writes:
> Have we determined that Tom's patch (the one that Josh wrote up) is 
> indeed necessary?

Necessary or not, it's in ;-)

regards, tom lane

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

http://archives.postgresql.org

[HACKERS] Odd subselect in target list behavior WRT aggregates

[Mike Mascari]

Hello. I have some code which generates subselects in the target
list of a query and then aggregates the results. The code allows
the user to determine the attributes of the aggregation. If the
user chooses to aggregate on the same value twice, I get the
"Sub-SELECT" error. If the user chooses a different second
attribute of aggregation, no error occurs. Is that correct
behavior? The only difference between Query #1 and Query #2 is
that the second subselect in the target list of Query #2
aggregates on the 'day' of a sale as opposed to the 'hour':

Query #1


SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
 FROM sales
 WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('hour', sales.active)
 FROM sales
 WHERE p.purchase = sales.purchase) as field2

FROM purchases p
WHERE ...
GROUP BY 2,3;

ERROR:  Sub-SELECT uses un-GROUPed attribute p.purchase from
outer query

Query #2


SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
 FROM sales
 WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('day', sales.active)
 FROM sales WHERE p.purchase = sales.purchase) as field2

FROM purchases p
WHERE ...
GROUP BY 2,3;

 agg   | field1 | field2
---++
1. | 2002-12-27 18:00:00-05 | 2002-12-27 00:00:00-05

I also failed to mention in the original post that this is
PostgreSQL version 7.2.1.

Any help or instruction would be greatly appreciated.

Mike Mascari
[EMAIL PROTECTED]





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

Re: [HACKERS] Release Scheduales: 7.2.4 & 7.3.2

[Justin Clift]

Marc G. Fournier wrote:

On Wed, 22 Jan 2003, Robert Treat wrote:



On Wed, 2003-01-22 at 14:23, Marc G. Fournier wrote:


If anyone has any 'last minute' issues they would like to see in either,
please speak now or forever hold your peace :)



Can someone post a "changelog" for these releases? Also what tags will
be created/used in CVS?



REL7_2_4
REL7_3_2

I'll make sure I don't forget to tag them this time :)


Have we determined that Tom's patch (the one that Josh wrote up) is 
indeed necessary?

;-)

Regards and best wishes,

Justin Clift


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


---(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: [HACKERS] [PATCH] psql visibility clarification patch

[Tom Lane]

"D. Hageman" <[EMAIL PROTECTED]> writes:
> The goal of this patch is a solution to the issue that I found concerning 
> table visibility.  The problem with the way psql currently lists tables in 
> a database is that it limits it to only the tables currently in the search 
> path.

That's the intended behavior.  I don't think that "\dt foo" should show
any tables other than the same "foo" you'd get from an unqualified
reference to "foo".  If you want to know about foos that are not in
your search path, you can do "\dt *.foo".

Your proposed patch essentially eliminates the distinction between
\dt foo and \dt *.foo.  This doesn't seem like a step forward to me.
Perhaps what's really needed is a documentation patch explaining when
to use each?

regards, tom lane

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

Re: [HACKERS] Cast and Schemas don't work as expected

[Tom Lane]

"Eduardo Stern" <[EMAIL PROTECTED]> writes:
> The CREATE CAST command is't supporting schemas on the type parameters.

Yah.  Are you enough of a yacc wizard to fix the reduce/reduce conflicts
that arise when GenericType is made to include qualified names?  I spent
a couple days beating on that, without success.

regards, tom lane

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

http://archives.postgresql.org

Re: [HACKERS] New buildin function

[Rod Taylor]

On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
> Hi!
> 
> What about adding new function:
> pg_uid()
> pg_session_uid()
> 
> as reference to internal function GetUserId() and GetSessionUserId().
> 
> These can help useful, for instance in row based securety.

Do CURRENT_USER and SESSION_USER not give those values?

-- 
Rod Taylor <[EMAIL PROTECTED]>

PGP Key: http://www.rbt.ca/rbtpub.asc



signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Cast and Schemas don't work as expected

[Christopher Kings-Lynne]

That's probably because CASTS are database-wide and are not in schemas.

Chris

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Eduardo Stern
> Sent: Sunday, 19 January 2003 11:07 PM
> To: [EMAIL PROTECTED]
> Subject: [HACKERS] Cast and Schemas don't work as expected
>
>
> The CREATE CAST command is't supporting schemas on the type parameters.
>
> A query such as:
>
> CREATE CAST (public.lo AS oid) WITH FUNCTION newoid (public.lo)
> AS IMPLICIT;
>
> Gives an error. And the problem happens when you create a CAST without the
> schema notation, and then try to do a dump/restore cycle.
>
>
> []s
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


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

Re: [HACKERS] Release Scheduales: 7.2.4 & 7.3.2

[Marc G. Fournier]

On Wed, 22 Jan 2003, Robert Treat wrote:

> On Wed, 2003-01-22 at 14:23, Marc G. Fournier wrote:
> > If anyone has any 'last minute' issues they would like to see in either,
> > please speak now or forever hold your peace :)
> >
>
> Can someone post a "changelog" for these releases? Also what tags will
> be created/used in CVS?

REL7_2_4
REL7_3_2

I'll make sure I don't forget to tag them this time :)


---(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: [HACKERS] C++ coding assistance request for a

[Greg Copeland]

On Wed, 2003-01-22 at 23:40, Justin Clift wrote:
> Justin Clift wrote:
> > Greg Copeland wrote:
> > 
> >> Have you tried IBM's OSS visualization package yet?  Sorry, I don't seem
> >> to recall the name of the tool off the top of my head (Data Explorer??)
> >> but it uses OpenGL (IIRC) and is said to be able to visualize just about
> >> anything.  Anything is said to include simple data over time to complex
> >> medical CT scans.
> > 
> > 
> > Cool.
> > 
> > Just found it...  IBM "Open Visualization Data Explorer":
> > 
> > http://www.research.ibm.com/dx/
> 
> That seems to be a very outdated page for it.  The new pages for it (in 
> case anyone else is interested) are at:
> 
> http://www.opendx.org
> 
> :-)
> 
> Regards and best wishes,
> 
> Justin Clift


Yep!  That's the stuff!  Sorry I wasn't more specific.  Just been a
while since I'd looked at it.

I'd love to know how well it works out for you.  Especially love to see
any pretty pictures you create with it.  ;)


Regards,

Greg


-- 
Greg Copeland <[EMAIL PROTECTED]>
Copeland Computer Consulting


---(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: [HACKERS] New buildin function

[Peter Eisentraut]

Olleg Samoylov writes:

> What about adding new function:
> pg_uid()
> pg_session_uid()
>
> as reference to internal function GetUserId() and GetSessionUserId().

CURRENT_USER, SESSION_USER

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

http://archives.postgresql.org

[HACKERS] [PATCH] psql visibility clarification patch

[D. Hageman]

Attached is a patch that I would like to submit for discussion.  
The goal of this patch is a solution to the issue that I found concerning 
table visibility.  The problem with the way psql currently lists tables in 
a database is that it limits it to only the tables currently in the search 
path.  If it isn't visible, then you will never see the table.  This can 
cause problems for a person that is trying to learn a database for the 
first time or something along those lines unless they are familiar with 
the pg_catalog.  I think a better solution to handling the issue of 
visibility is shown below.  It will list all of the tables of the database 
and show another column to give indication of the visibility of the table.  

eecs=> \d
List of relations
   Schema   |  Name  |   Type   | Visible | Owner 
++--+-+---
 term_029   | schedule   | table| f   | dba
 term_029   | schedule_preceptor | table| f   | dba
 term_029   | schedule_preceptor_seq | sequence | f   | dba
 term_029   | schedule_seq   | sequence | f   | dba
 term_029   | schedule_student   | table| f   | dba
 term_029   | schedule_student_seq   | sequence | f   | dba
 term_032   | schedule   | table| t   | dba
 term_032   | schedule_preceptor | table| t   | dba
 term_032   | schedule_preceptor_seq | sequence | t   | dba
 term_032   | schedule_seq   | sequence | t   | dba
 term_032   | schedule_student   | table| t   | dba
 term_032   | schedule_student_seq   | sequence | t   | dba

-- 
//\\
||  D. Hageman<[EMAIL PROTECTED]>  ||
\\//
diff -ruN pgsql-server/src/bin/psql/describe.c 
pgsql-server.dhageman/src/bin/psql/describe.c
--- pgsql-server/src/bin/psql/describe.c2003-01-07 14:56:06.0 -0600
+++ pgsql-server.dhageman/src/bin/psql/describe.c   2003-01-23 14:37:39.0 
+-0600
@@ -1274,10 +1274,11 @@
  "SELECT n.nspname as \"%s\",\n"
  "  c.relname as \"%s\",\n"
  "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 
'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' END as 
\"%s\",\n"
+ " pg_catalog.pg_table_is_visible(c.oid) as 
+\"%s\",\n"
  "  u.usename as \"%s\"",
  _("Schema"), _("Name"),
  _("table"), _("view"), _("index"), 
_("sequence"),
- _("special"), _("Type"), _("Owner"));
+ _("special"), _("Type"), _("Visible"), 
+_("Owner"));
 
if (verbose)
appendPQExpBuffer(&buf,
@@ -1326,7 +1327,7 @@
 
processNamePattern(&buf, pattern, true, false,
   "n.nspname", "c.relname", NULL,
-  "pg_catalog.pg_table_is_visible(c.oid)");
+  NULL );
 
appendPQExpBuffer(&buf, "ORDER BY 1,2;");
 


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

[HACKERS] New buildin function

[Olleg Samoylov]

Hi!

What about adding new function:
pg_uid()
pg_session_uid()

as reference to internal function GetUserId() and GetSessionUserId().

These can help useful, for instance in row based securety.

-- 
Olleg Samoylov


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

[HACKERS] Cast and Schemas don't work as expected

[Eduardo Stern]

The CREATE CAST command is't supporting schemas on the type parameters.

A query such as:

CREATE CAST (public.lo AS oid) WITH FUNCTION newoid (public.lo) AS IMPLICIT;

Gives an error. And the problem happens when you create a CAST without the
schema notation, and then try to do a dump/restore cycle.


[]s

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

http://archives.postgresql.org

Re: [HACKERS] Threads

[mlw]

Greg Copeland wrote:

  On Thu, 2003-01-23 at 09:12, Steve Wampler wrote:
  
  
On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:


  Also remember that in even well developed OS's like FreeBSD, all a
process's threads will execute only on one CPU.
  

I doubt that - it certainly isn't the case on Linux and Solaris.
A thread may *start* execution on the same CPU as it's parent, but
native threads are not likely to be constrained to a specific CPU
with an SMP OS.

  
  
You are correct.  When spawning additional threads, should an idle CPU
be available, it's very doubtful that the new thread will show any bias
toward the original thread's CPU.  Most modern OS's do run each thread
within a process spread across n-CPUs.  Those that don't are probably
attempting to modernize as we speak

AFAIK, FreeBSD is one of the OSes that are trying to modernize. Last I looked
it did not have kernel threads.

  
  

Re: [HACKERS] Foreign key wierdness

[Didier Moens]

Dave Page wrote:


From what Tom has said in his reponse, I think the answer for you Didier

is to remap your integer columns to int8 instead of int4 and see what
happens. When I get a couple of minutes I will look at putting a Serials
as... Option in the type map.



Thanks Dave, for all of your invested time.

I think the value of tools such as pgAdmin, which provide an almost 
bumpless cross-platform migration path, cannot be underestimated.


Regards,
Didier

--

Didier Moens
-
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
http://www.dmb.rug.ac.be



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

Re: [HACKERS] Windows Build System

[Curtis Faith]

Curtis Faith wrote:
> > The Visual C++ Workspaces and Projects files are actually 
> > text files that have a defined format. I don't think the format is 
> > published but it looks pretty easy to figure out.

Hannu Krosing wrote:
> will probably change between releases

Even if the format changes, the environment always has a converter that
updates the project and workspace files to the new format. In other
words, Visual C++ 6.0 reads 5.0 projects, 7.0 reads 6.0, etc.

The format is mostly a bunch of options specifications (which wouldn't
get touched) followed by a set of named groups of source files. Even if
the overall format changes, it will be much more likely to change in the
specifications rather than the way lists of source file formats are
specified.

A conversion script would only need to:

1) Read in the template file (containing all the options specifications
and Visual C++ speficic stuff, debug and release target options,
libraries to link in, etc.) This part might change with new versions of
the IDE and would be manually created by someone with Visual C++
experience.

2) Read in the postgreSQL group/directory map, or alternately just
mirror the groups with the directories.

3) Output the files from the PostgreSQL directories in the appropriate
grouping according to the project format into the appropriate space in
the template.

An excerpt of the format follows:

# Begin Group "Access"
# Begin Group "Common"
# PROP Default_Filter "cpp;c;cxx"
# Begin Source File

SOURCE=.\access\common\heaptuple.c
# End Source File
# Begin Source File

SOURCE=.access\common\indextuple.c
# End Source File

... other files in access\common go here
# End Group

# Begin Group "Index"

# PROP Default_Filter "cpp;c;cxx"
# Begin Source File

SOURCE=.\access\index\genam.c
# End Source File
# Begin Source File

SOURCE=.access\index\indexam.c
# End Source File

... other files in access\index go here
# End Group

# End Group


As you can see, this is a really easy format, and the folder/group
mapping with directories is pretty natural and probably the way to go.

Using the approach I outline, it should be possible to have the Unix
make system automatically run the BuildWindowsProjectFile tool whenever
any makefile changes so the Windows projects would stay up to date
without additional work for Unix developers.

Hannu Krosing also wrote:
> (also I dont think you can easily compile C source on a
> C# compiler) ;/

I don't think it makes much sense target a compiler that won't compile
the source, therefore, if what you say is true, we shouldn't bother with
targeting C#. Why does this matter? I didn't propose targeting Visual
C#.NET.

- Curtis




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

Re: [HACKERS] Foreign key wierdness

[Didier Moens]

Hi all,

Dave Page wrote:


If you really think the schema qualification has something to 
do with it, try issuing the ADD FOREIGN KEY command manually 
in psql, with and without schema name.
   


Well to be honest I'm having a hard time believing it, but having looked
at this in some depth, it's the only thing that the 2 versions of
pgAdmin are doing differently. Even the PostgreSQL logs agree with that.
I'm relying on Didier for test results though as I don't have a test
system I can use for this at the moment.

But it gives us something to try - Didier can you create a new database
please, and load the data from 2 tables. VACUUM ANALYZE, then add the
foreign key in psql using the syntax 1.4.2 uses. Then drop the database,
and load exactly the same data in the same way, VACUUM ANALYZE again,
and create the fkey using the qualified tablename syntax.



I did some extensive testing using PostgreSQL 7.3.1 (logs and results 
available upon request), and the massive slowdown is NOT related to 
qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the 
following change :

pgAdminII 1.4.2 :
---
CREATE TABLE articles (
   article_id integer DEFAULT 
nextval('"articles_article_id_key"'::text) NOT NULL,
...

test=# \d articles
   Table "public.articles"
Column  | Type  |  
Modifiers
-+---+-
article_id  | integer   | not null default 
nextval('"articles_article_id_key"'::text)
...

pgAdminII 1.4.12 :

CREATE TABLE articles (
   article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
NOT NULL,
...

test=# \d articles
   Table "public.articles"
Column  | Type  |  
Modifiers
-+---+-
article_id  | bigint| not null default 
nextval('"articles_article_id_key"'::text)
...


With two tables each containing some 20.000 entries, the fk creation 
time between both of them increases from ~ 1.8 secs to ~ 221 secs.


Regards,
Didier

--

Didier Moens
-
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
tel ++32(9)2645309 fax ++32(9)2645348
http://www.dmb.rug.ac.be



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

Re: [HACKERS] Foreign key wierdness

[Didier Moens]

Dear Tom, Dave,


Tom Lane wrote:


Ah-hah, and I'll bet that the column being linked to this one by the
foreign key constraint is still an integer?



It sure is ; being a PostgreSQL novice (BTW : many thanks to the whole 
of the PG development team for such an excellent product), I got on this 
track by means of 
http://archives.postgresql.org/pgsql-sql/2001-05/msg00395.php .


With two tables each containing some 20.000 entries, the fk creation 
time between both of them increases from ~ 1.8 secs to ~ 221 secs.
 


Seems odd that the cost would get *that* much worse.  Maybe we need to
look at whether the FK checking queries need to include explicit casts
...


Well, I reproduced the slowdown with some 20 to 30 different tables.
Anyway, glad I could be of some help, albeit only by testing some 
(probably quite meaningless) border cases ...  :)


Regards,
Didier

--

Didier Moens
-
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
tel ++32(9)2645309 fax ++32(9)2645348
http://www.dmb.rug.ac.be



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

[HACKERS] On Commit Triggers

[Antonio Scotti]

I am using PostgreSQL for some application and I've come in the need of 
a Before Commit and an After Commit trigger. PostgreSQL currently 
doesn't support them and I am wondering if it will ever be possible for 
developers to add them.

Antonio Scotti


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] ECPG, threading and pooling

[Michael Meskes]

On Thu, Jan 23, 2003 at 08:58:24PM +0530, Shridhar Daithankar wrote:
> Well, when using libpq, I create a pool of PGconn*. What pool I create with 
> ecpg? Looking at ecpglib.h, it seems that I need to create a pool of char *

Yes. You use it as EXEC SQL AT :connection_var SELECT ...

> That is correct but I did not find any example of how to use a particular 
> connection in an SQL statement. It would be a fairly trivial guess that the 
> connection name is one of the arguments to ECPGdo. But how the SQL statement 
> look like, I don't have a clue. Also I don't know know how to use ECPGsetconn 
> to set a connection. No documentation on that.

You don't call either one yourself. Let ecpg do that job. Some of the
test cases in the source tree use AT. Just look under .../ecpg/test.

> I can guess that ECPG maintains a mapping between connection name and PGconn* 
> object for a user. If possible it would be very helpful to form a ecpg 
> connection on top a user supplied PGconn* object.

Why? It's possible, but you have to work with structures not meant to be
used outside the lib.

Michael
-- 
Michael Meskes
Email: [EMAIL PROTECTED]
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---(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: [HACKERS] Postgresql source

[radha . manohar]

Can you please tell me how can I download all the source codes for 
postgresql??

-Radha Manohar

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

http://archives.postgresql.org

Re: [HACKERS] [GENERAL] Translation of the PostgreSQL manuals to Spanish is under way

[Claudio Lapidus]

Hello Justin,

Perhaps I'll be able to offer some help. I'm a fair translator, IMHO.

Christian, please contact me directly to see what can be done.

regards,
cl.





Enviado por:  [EMAIL PROTECTED]


Destinatarios: PostgreSQL Advocacy Mailing List
   <[EMAIL PROTECTED]>, PostgreSQL Hackers Mailing List
   <[EMAIL PROTECTED]>, PostgreSQL General Mailing List
   <[EMAIL PROTECTED]>
CC: Christian Kuroki <[EMAIL PROTECTED]>

Asunto:   [GENERAL] Translation of the PostgreSQL manuals to
   Spanish is under way
Clasificación:


Hi everyone,

Christian Kuroki <[EMAIL PROTECTED]> is a senior member of a team that
is translating the PostgreSQL manuals to Spanish.

There isn't a website for it yet (it will be created fairly soon) but
the team is making good progress, and the manuals will be kept updated
with the main PostgreSQL manuals.

Does anyone out here who knows/writes Spanish have a bit of time to
assist them with translation?  These manuals will be available to all,
just the same as with PostgreSQL itself, and will greatly assist in
improving PostgreSQL for the Spanish Community.

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


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

http://www.postgresql.org/users-lounge/docs/faq.html


Http://www.telecom.com.ar



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

Re: [HACKERS] createlang failed!

[John Liu]

Thanks, fixed. The problem was
caused -
there's a postgreSQL came with the
Redhat server, but I tried to install one
only used by a specific user, I set
LD_LIBRARY_PATH in this user's profile.
pg_config --pkglibdir points to the correct
lib dir, but when I run createlang plpgsql template1
it keeps failed.

I've to run -
createlang --pglib=Dir plpgsql template1

then:
createlang -l template1
Procedural languages
  Name   | Trusted?
-+--
 plpgsql | t
(1 row)

But here's another observation -
I still can't create plpgsql in the mydb
(createdb mydb) even template1 supports plpgsql.
I've to run
createlang --pglib=Dir --dbname=DB plpgsql.

I guess it's me to make it messy.

johnl


> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 9:15 AM
> To: John Liu
> Cc: [EMAIL PROTECTED]
> Subject: Re: [HACKERS] createlang failed!
>
>
> "John Liu" <[EMAIL PROTECTED]> writes:
> > When I run createlang plpgsql template1
> > on linux 7.3 (pg version 7.3.1), it failed -
>
> > createlang plpgsql template1
> > ERROR:  stat failed on file '$libdir/plpgsql': No such file or directory
> > createlang: language installation failed
>
> What does 'pg_config --pkglibdir' say?  Is there a plpgsql.so in there?
> Is there any additional information about the failure in the
> postmaster's log?  (If you are routing the postmaster's stderr to
> /dev/null, now is a good time to stop doing that --- what I'm wondering
> about is dynamic-linker errors, which will only appear on stderr.)
>
>   regards, tom lane


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

Re: [HACKERS] [pgsql-advocacy] Translation of the PostgreSQL manuals to

[Alberto Caso]

El vie, 17-01-2003 a las 03:03, Justin Clift escribió:
> Hi everyone,
> 
> Christian Kuroki <[EMAIL PROTECTED]> is a senior member of a team that 
> is translating the PostgreSQL manuals to Spanish.
> 

[...]

> Does anyone out here who knows/writes Spanish have a bit of time to 
> assist them with translation?  These manuals will be available to all, 
> just the same as with PostgreSQL itself, and will greatly assist in 
> improving PostgreSQL for the Spanish Community.
> 

I'm a member of another team that is also traslating PostgreSQL docs
into Spanish, as part of the work of the spanish team of "the Linux
Documentation Project" (http://es.tldp.org).

You can find our website at http://es.tldp.org/Postgresql-es/web/ and
have a look at the progress of the traslation at
http://es.tldp.org/Postgresql-es/web/prioridades.htm

Maybe we can join efforts to speed up the traslation (I added our
mailing list to the CC to let the rest of the team know).

Regards,

-- 
Alberto Caso Palomino
Adaptia Soluciones Integrales
http://www.adaptia.net
[EMAIL PROTECTED]





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

http://archives.postgresql.org

Re: [PATCHES] targetlist functions part 1 (was [HACKERS] targetlist

[Peter Eisentraut]

I wrote:

> The SQL 200x draft defines a new clause TABLE (  expression> ) as a possible , where the  expression> is required to be a function call.  At the end this just boils
> down to UNNEST, though.  UNNEST is defined in terms of a hairy recursive
> join subquery with a LATERAL( ) around it.  LATERAL(subquery) is the same
> as just (subquery) except that the scope clauses are different.  So I
> think this is probably what we ought to look at.

I have stared at this some more and it is indeed what we're looking for.
The hairy recursive join is only so that they can get the WITH ORDINALITY
feature (which basically adds a "line number" column to the output) in
there in a closed form.  If you simplify it, the command

SELECT ... FROM TABLE( func(...) ) ...

resolves to

SELECT ... FROM table_generated_by_func ...

As for the question of where nonconstant argument values come from, this
is addressed as well.  The general form of this feature is the lateral
derived table, for example

SELECT ... FROM table1, LATERAL(select ...), table2 ...

as opposed to simply

SELECT ... FROM table1, (select ...), table2 ...

In the second form the subquery cannot access outside values.  In the
first form, the subquery can access range variables in FROM items defined
to its left.

The table function calls are a special case of this, meaning that in

SELECT .. FROM table1, TABLE( func(args) ), table2 ...

the "args" can refer to table1 but not to table2.

Oracle implements exactly this feature.  (See
.)
If there are doubts about the semantics we could try it out there.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] Options for growth

[scott.marlowe]

On 23 Jan 2003, Hannu Krosing wrote:

> Curt Sampson kirjutas N, 23.01.2003 kell 17:42:
> > If the OS can handle the scheduling (which, last I checked, Linux couldn't,
> 
> When did you do your checking ? 
> (just curious, not to start a flame war ;)
> 
> >  at least not without patches), eight or sixteen
> > CPUs will be fine.

Yeah, take a look here:

http://www.sgi.com/servers/altix/

64 CPUs seems scalable enough for me.  :-)  When can we expect BSD to run 
on this system and use all 64 CPUs efficiently?


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE

[Curt Sampson]

On Fri, 23 Jan 2003, Hannu Krosing wrote:

> > 1. [OIDs are] not a relational concept.
> so are other system tuples (cid, tid, tableiod, ...).

But there's a key difference here; nobody's advertising these others as
any sort of row identifier: i.e., a candidate key. And besides, I wouldn't
object at all to getting rid of these, except that they store essential
system information and I can't figure out how to get rid of them. :-)

> It is an OO concept.

Well, it's not, because we have an OID wrap-around problem, so an OID is
actually not an OID at all, but simply an arbitrary number tacked on to
a row. Other rows, in the same or other tables can have the same OID.

> > 2. The OID wraparound problem can get you.
> put an unique index on OID column.

That still doesn't make it a real OID, because you can't guarantee that
two rows in different tables won't have the same OID.

> > 3. Other SQL databases don't do this.
> Ask Date, hell tell you that SQL is evil, i.e. not relational ;)

I did, he said that, and I agreed with him. :-) So now we have something
that's evil because it's not relational and also evil because it's not
SQL. Double-yuck!

> > 5. We should default to what gives us better performance, rather than
> > worse.
> Not if it breaks anything ;)

I disagree. We have to weigh the cost of the breakage versus the
benefits in each individual circumstance. We've broken plenty of things
before because we felt it was better to do so than maintain backward
compatability.

Because of its history as a research tool, there's a lot of experimental
stuff in postgres that, in hindsight, we can say didn't work so well.
When we find something that's not working so well, we should at least
consider making some sort of move toward the "right thing," rather than
continuing to do the wrong thing forever just for the sake of backwards
compatability.

Summary: I don't want to hear absolutes like "we should never break
backwards compatibility." I want to hear arguments that the cost of
breaking backwards compatability is X, and the benefit of the new way of
doing things is Y, and here is why you think X > Y.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

http://archives.postgresql.org

Re: [HACKERS] Threads

[Greg Copeland]

On Thu, 2003-01-23 at 09:12, Steve Wampler wrote:
> On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> > 
> > Also remember that in even well developed OS's like FreeBSD, all a
> > process's threads will execute only on one CPU.
> 
> I doubt that - it certainly isn't the case on Linux and Solaris.
> A thread may *start* execution on the same CPU as it's parent, but
> native threads are not likely to be constrained to a specific CPU
> with an SMP OS.

You are correct.  When spawning additional threads, should an idle CPU
be available, it's very doubtful that the new thread will show any bias
toward the original thread's CPU.  Most modern OS's do run each thread
within a process spread across n-CPUs.  Those that don't are probably
attempting to modernize as we speak.

-- 
Greg Copeland <[EMAIL PROTECTED]>
Copeland Computer Consulting


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

Re: [HACKERS] Options for growth

[Curt Sampson]

On Fri, 23 Jan 2003, Hannu Krosing wrote:

> Curt Sampson kirjutas N, 23.01.2003 kell 17:42:
> > If the OS can handle the scheduling (which, last I checked, Linux couldn't,
>
> When did you do your checking ?
> (just curious, not to start a flame war ;)

This was perhaps a year or so ago. IBM had some patches to fix a lot of
the scheduler problems. I wouldn't be surprised if things are in a much
better state now.

Anyway, there are lots of work-arounds. Find the appropriate patches if
the kernel still doesn't have them, run Solaris, whatever

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE

[Curt Sampson]

On Thu, 23 Jan 2003, Ross J. Reedstrom wrote:

> So in the longer term, we need to provide a replacement. Arguably, the
> primary key for a table is the right replacement, but we don't _require_
> a pkey, so what to do in cases where this isn't one?

You're stuck. SQL breaks with relational theory in this way; tables
need not have candidate keys, and thus you can have duplicate rows in
a table. (I.e., mathamatically, a table is not a set, it's a bag.) The
implications of this start to go on and on, but let's not get into that
here.

> Also, the pkey can be _any_ column(s), of _any_ type, which could be
> inconvenient for returning as the result of an insert, for example
> (imagine a text field as pkey, with a _huge_ block of text just
> written into it ...)

Well, this could be worked around to some extent, with some hackery.
But in the end I reckon it's much easier just to have the object system
force you to declare specific a specific object-ID column, if that's
what it takes. So long as you've got a candidate key, even if it's not
the primary key, you're fine.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE

[Hannu Krosing]

Curt Sampson kirjutas N, 23.01.2003 kell 13:34:
> On Tue, 21 Jan 2003, Tom Lane wrote:
> 
> > We've gotten a couple of complaints now about the fact that 7.3 doesn't
> > include an OID column in a table created via CREATE TABLE AS or SELECT
> > INTO.  Unless I hear objections, I'm going to revert it to including an
> > OID, and back-patch the fix for 7.3.2 as well.
> 
> I object. I personally think we should be moving towards not using OIDs
> as the default behaviour, inasmuch as we can, for several reasons:

I re-object

> 1. It's not a relational concept.

so are other system tuples (cid, tid, tableiod, ...).

It is an OO concept.

> 2. The OID wraparound problem can get you.

put an unique index on OID column.

> 3. Other SQL databases don't do this.

Ask Date, hell tell you that SQL is evil, i.e. not relational ;)

> 4. It's hidden, rather than exposed, and hidden things are generally a
> bad idea.

AFAIK carrying hidden weapons is forbidden in most of USA, in Europe you
usually are forbidden to carry hand-weapons _exposed_  ;)

> 5. We should default to what gives us better performance, rather than
> worse.

Not if it breaks anything ;)

> > See discussion a couple days ago on pgsql-general, starting at
> > http://archives.postgresql.org/pgsql-general/2003-01/msg00669.php
> 
> There didn't seem to be many people clamouring to have it back.
> 
> The ideal sitaution for me would be to have WITHOUT OIDS be the default
> for all table creations, and but of course allow WITH OIDS for backward
> compatability. But yeah, I know that this can introduce problems with
> old dumps, and may not be entirely easy to implement.

If you need a no-OID table, and INSERT INTO it.

> cjs
-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

Re: [HACKERS] Foreign key wierdness

[Hannu Krosing]

Tom Lane kirjutas K, 22.01.2003 kell 22:30:
> Didier Moens <[EMAIL PROTECTED]> writes:
> > I did some extensive testing using PostgreSQL 7.3.1 (logs and results 
> > available upon request), and the massive slowdown is NOT related to 
> > qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the 
> > following change :
> 
> > pgAdminII 1.4.2 :
> > ---
> > CREATE TABLE articles (
> > article_id integer DEFAULT 
> > nextval('"articles_article_id_key"'::text) NOT NULL,
> > ...
> 
> > pgAdminII 1.4.12 :
> > 
> > CREATE TABLE articles (
> > article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
> > NOT NULL,
> > ...
> 
> Ah-hah, and I'll bet that the column being linked to this one by the
> foreign key constraint is still an integer?

This should at least give out a NOTICE or ABORT or generate a functional
index, not a plain one.

> > With two tables each containing some 20.000 entries, the fk creation 
> > time between both of them increases from ~ 1.8 secs to ~ 221 secs.
> 
> Seems odd that the cost would get *that* much worse.  Maybe we need to
> look at whether the FK checking queries need to include explicit casts
> ...
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

Re: [HACKERS] Terrible performance on wide selects

[Hannu Krosing]

Dann Corbit kirjutas N, 23.01.2003 kell 02:22:
> [snip]
> > So (for instance) if you have 12 variable fields, you would 
> > store 12 integers at the start of the record.
> 
> Additionally, you could implicitly size the integers from the properties
> of the column.  A varchar(255) would only need an unsigned char to store
> the offset, but a varchar(8) would require an unsigned int.

I guess that the pointer could always be 16-bit, as the offset inside a
tuple will never be more (other issues constrain max page size to 32K)

varchar(8) will use TOAST (another file) anyway, but this will be
hidden inside the field storage in the page)

> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

http://archives.postgresql.org

Re: [HACKERS] Options for growth

[Hannu Krosing]

Curt Sampson kirjutas N, 23.01.2003 kell 17:42:
> If the OS can handle the scheduling (which, last I checked, Linux couldn't,

When did you do your checking ? 
(just curious, not to start a flame war ;)

>  at least not without patches), eight or sixteen
> CPUs will be fine.
> 
> cjs
-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE AS/SELECT INTO

[Ross J. Reedstrom]

On Thu, Jan 23, 2003 at 10:03:28AM -0500, Tom Lane wrote:
> Curt Sampson <[EMAIL PROTECTED]> writes:
> > I object. I personally think we should be moving towards not using OIDs
> > as the default behaviour, inasmuch as we can, for several reasons:
> 
> All these objections are global in nature, not specific to CREATE TABLE
> AS.  The argument that persuaded me to do something here is that CREATE
> TABLE AS should not be different from CREATE TABLE's default behavior.
> 
> I have no problem with moving towards lack-of-OIDs as the default
> behavior for both statements, in the long run, if we can get past the
> compatibility issues.  But I don't think OIDs in user tables are costing
> us anything much, so I'm not prepared to take any big compatibility hit
> to change the default ...

Agreed as to taking the compatability hit in the 7.3 branch (you _were_
talking about changing 7.3, weren't you?) But I think Curt and D'Arcy
have a point: what OIDs are costing the DBAs and PostgreSQL developing
community is the pain of having an 'almost' solution in place. OIDs have
always been the unwanted child in PostgreSQL: the 'pure relational' people
don't want them, and the Object people are misled into thinking we've got
a _real_ object id. On the relational side, they've stood in for proper
use of primary keys (as D'Arcy points out), partly because it's so _easy_
to misuse them that way: the wire protocol returns the OID for free in
some cases, and the interface libraries make it easy to get at.

So the immediate case, changing the default (in 7.3) to match the CREATE
TABLE case makes sense. However, we need to wean developers off using
OIDs.  I've been working with Diedrich Vorberg on a thin python object
relational mapping interface (his Object Relational Membrane - ORM)
and this was a central problem: you _need_ a unique id for an object,
and the oid seemed so natural ... 

So in the longer term, we need to provide a replacement. Arguably, the
primary key for a table is the right replacement, but we don't _require_
a pkey, so what to do in cases where this isn't one?  Also, the pkey
can be _any_ column(s), of _any_ type, which could be inconvenient for
returning as the result of an insert, for example (imagine a text field
as pkey, with a _huge_ block of text just written into it ...)

Ross

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

Re: [PERFORM] [HACKERS] Terrible performance on wide selects

[Curt Sampson]

On Thu, 23 Jan 2003, Daniel Kalchev wrote:

> Does this mean, that constructing tables where fixed length fields are
> 'before' variable lenght fields and 'possibly null' fields might increase
> performance?

This, I believe, is why DB2 always puts (in physical storage) all of the
fixed-length fields before the variable-length fields.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

http://archives.postgresql.org

Re: [HACKERS] Options for growth

[Curt Sampson]

On Thu, 16 Jan 2003, D'Arcy J.M. Cain wrote:

> Due to the fact that we are growing out of our current system
> (PostgreSQL on PCs) we are looking for ways to expand and one of the
> suggestions has been to toss PostgreSQL in favour of Oracle with
> Remote Access Cluster (RAC) software. The theory is that you can just
> plug machines into the cluster if the database appears to be straining
> and they automagically take over some of the load.
> ...
> My idea is to create a new middleware layer that allows me to split
> things up based on various criteria without changing my application.

It's a basic principle of clustering that doing it in an application-
aware way will always be more efficient than trying to hide it from the
application.

If you've not read it already, I strongly suggest reading _In Search of
Clusters_ by Gregory F. Pfister.

> And finally, if you had your dream machine to run on, what would it
> be? We are also looking outside of PC hardware but we are concerned
> about not having access to that nice, cheap, generic hardware for when
> we need to grow again or for redundant backup.

If you can manage to stick with PC hardware, you are going to save a
*lot* of money. If you're considering buying a reasonably well loaded
Sun E6000 or similar, it's well worth spending twenty or thirty thousand
dollars on a big PC system and spending some time to see if that will do
the trick before you shell out a couple hundred thousand for the Sun.

As for how well postgres uses multiple CPUs: so long as you've got lots
of connections with the load distributed among them, it's dependent on
the OS, postgres. If the OS can handle the scheduling (which, last I
checked, Linux couldn't, at least not without patches), eight or sixteen
CPUs will be fine.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] Threads

[Shridhar Daithankar]

On Thursday 23 January 2003 08:42 pm, you wrote:
> On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> > Also remember that in even well developed OS's like FreeBSD, all a
> > process's threads will execute only on one CPU.
>
> I doubt that - it certainly isn't the case on Linux and Solaris.
> A thread may *start* execution on the same CPU as it's parent, but
> native threads are not likely to be constrained to a specific CPU
> with an SMP OS.

I am told that linuxthreads port available on freeBSD uses rfork and is 
capable of using multiple CPUs within a single process.

Native freeBSD threads can not do that. Need to check that with freeBSD5.0.

 Shridhar



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Options for growth

[Curt Sampson]

On Wed, 22 Jan 2003, Sean Chittenden wrote:

> > > By the way, I too wonder which supported OS platform would support
> > > over 4GB of memory on a PC..
> >
> > Linux? I don't think there's any problem handling more than 4G
> > memory in the system. On 32bit architectures, there's of course the
> > 3G (I think) per process limit, but as postgres uses multiprocess
> > and not multithreading, this issue doesn't hit so soon. Of course,
> > if the per process memory is the problem, you'd have to go to 64bit.
>
> Heh, don't kid yourself.  x86 can only handle 4GB of memory
> addressing.  The hack that Linux uses is to swap out 2GB sections of
> RAM to a 4GB+ memory range, then copy the memory range it needs down
> into usable memory space.  Can we say large page tables?  :)
>
> You need an actual 64bit CPU to access more than 4GB of RAM without
> paying for it through the nose.  -sc

No, you do not. If you need to access more than two to three GB
(depending on the OS) of RAM on a 32-bit machine *within a single
process* (as mentioned above), you have a problem. But this problem does
not necessarially involve copying; you could use, e.g., mmap to remap
chunks of your address space.

If you have multiple processes, and your OS is sensibly written, no
memory copying is necessary on the process side. All you do is change
the page tables, and the appropriate physical memory, no matter where in
the physical address space it resides, will be mapped into the 32-bit
virtual memory address space.

That's not to say that there might not be other issues with I/O on, say,
32-bit PCI buses. IIRC, typically PCI bus controllers use physical,
not virtual addresses on the bus for DMA, so you're going to have to
use bounce buffers if you wish a 32-bit PCI card to do I/O outside the
bottom 4 GB of memory. But on the other hand, if you're spending the
money on a motherboard that can take more than 4 GB of RAM, you're
almost certainly getting a few 64-bit PCI slots, and probably you'd also
be spending the money to buy 64-bit PCI disk controllers.

This is not to say you shouldn't go for a 64-bit system, especially
given that the AMD ones are probably going to get awfully cheap fairly
soon. But postgres itself is today not equipment to take any more
advantage of one than it is of a 32-bit system with a greater than
32-bit physical address space. (And there's been doubt about whether
the techniques that would take advantage of this would provide all that
much of a performance improvement, anyway. Still, it seems to me that
it would be pretty cool, when you're doing I/O on a table, just to say,
with one system call, "mmap this entire file containing the table into
my address space," and not have to worry about running out of address
space when you do this on multiple large tables. (And yes, I know this
would actually be, "map this 1 GB chunk of this large table" in the
current postgres implemenation.)

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] ECPG, threading and pooling

[Shridhar Daithankar]

On Thursday 23 January 2003 08:40 pm, you wrote:
> On Thu, Jan 23, 2003 at 02:40:33PM +0530, Shridhar 
Daithankar<[EMAIL PROTECTED]> wrote:
> > I would like to know if ECPG is thread safe and I can use an arbitrary
> > PGconn* object pulled from a connection pool.
> Not sure what you mean with this?

Well, when using libpq, I create a pool of PGconn*. What pool I create with 
ecpg? Looking at ecpglib.h, it seems that I need to create a pool of char *

> > Do I need to use connection name? I mean how do I store connection name
> > in an array or so?
> How else would you decide against whcih connection to run the statement?

That is correct but I did not find any example of how to use a particular 
connection in an SQL statement. It would be a fairly trivial guess that the 
connection name is one of the arguments to ECPGdo. But how the SQL statement 
look like, I don't have a clue. Also I don't know know how to use ECPGsetconn 
to set a connection. No documentation on that.

I can guess that ECPG maintains a mapping between connection name and PGconn* 
object for a user. If possible it would be very helpful to form a ecpg 
connection on top a user supplied PGconn* object.

I just downloaded 7.3.1 to take a look at. I hope to make a contribution if 
possible.
 
 Shridhar



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

http://archives.postgresql.org

Re: [HACKERS] Threads

[Steve Wampler]

On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> 
> Also remember that in even well developed OS's like FreeBSD, all a
> process's threads will execute only on one CPU.

I doubt that - it certainly isn't the case on Linux and Solaris.
A thread may *start* execution on the same CPU as it's parent, but
native threads are not likely to be constrained to a specific CPU
with an SMP OS.

-- 
Steve Wampler <[EMAIL PROTECTED]>
National Solar Observatory

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] ECPG, threading and pooling

[Michael Meskes]

On Thu, Jan 23, 2003 at 02:40:33PM +0530, Shridhar 
Daithankar<[EMAIL PROTECTED]> wrote:
> I would like to use ECPG as it is relatively easy to code. However my 
> application is multithreaded and also uses connecion pools.

I'm afraid it needs some work to be thread-safe. sqlca is defined
statically. No big deal it seems to implement a thread safe version but
I haven't yet found the time.

> I would like to know if ECPG is thread safe and I can use an arbitrary PGconn* 
> object pulled from a connection pool.

Not sure what you mean with this?

> Do I need to use connection name? I mean how do I store connection name in an 
> array or so?

How else would you decide against whcih connection to run the statement?

Michael
-- 
Michael Meskes
Email: [EMAIL PROTECTED]
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE AS/SELECT INTO

[Tom Lane]

Curt Sampson <[EMAIL PROTECTED]> writes:
> I object. I personally think we should be moving towards not using OIDs
> as the default behaviour, inasmuch as we can, for several reasons:

All these objections are global in nature, not specific to CREATE TABLE
AS.  The argument that persuaded me to do something here is that CREATE
TABLE AS should not be different from CREATE TABLE's default behavior.

I have no problem with moving towards lack-of-OIDs as the default
behavior for both statements, in the long run, if we can get past the
compatibility issues.  But I don't think OIDs in user tables are costing
us anything much, so I'm not prepared to take any big compatibility hit
to change the default ...

regards, tom lane

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

Re: [HACKERS] Threads

[Curt Sampson]

On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:

> Also remember that in even well developed OS's like FreeBSD, all a
> process's threads will execute only on one CPU.

I would say that that's not terribly well developed. Solaris will split
a single processes' threads over multiple CPUs, and I expect most other
major vendors Unixes will as well. In the world of free software, the
next release of NetBSD will do the same. (The scheduler activations
system, which support m userland to n kernel threads mapping, was
recently merged from its branch into NetBSD-current.)

>From my experience, threaded sorts would be a big win. I managed to
shave index generation time for a large table from about 12 hours to
about 8 hours by generating two indices in parallel after I'd added a
primary key to the table. It would have been much more of a win to be
able to generate the primary key followed by other indexes with parallel
sorts rather than having to generate the primary key on one CPU (while
the other remains idle), wait while that completes, generate two more
indices, and then generate the last one .

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Terrible performance on wide selects

[Tom Lane]

Daniel Kalchev <[EMAIL PROTECTED]> writes:
> Does this mean, that constructing tables where fixed length fields are 
> 'before' variable lenght fields and 'possibly null' fields might increase 
> performance?

There'd have to be no nulls, period, to get any useful performance
difference --- but yes, in theory putting fixed-length columns before
variable-length ones is a win.

I wouldn't bother going out to rearrange your schemas though ... at
least not before you do some tests to prove that it's worthwhile.

regards, tom lane

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

Re: [PERFORM] [HACKERS] Terrible performance on wide selects

[Tom Lane]

Hannu Krosing <[EMAIL PROTECTED]> writes:
> as  tuple (int,int,int,varchar,varchar)
> which is currently stored as
> (intdata1, intdata2, intdata3, (len4, vardata4), (len5,vardata5))
> should be rewritten on storage to
> (ptr4,ptr5),(intdata1, intdata2, intdata3, vardata4,vardata5)

I do not see that this buys anything at all.  heap_getattr still has to
make essentially the same calculation as before to determine column
locations, namely adding up column widths.  All you've done is move the
data that it has to fetch to make the calculation.  If anything, this
will be slower not faster, because now heap_getattr has to keep track
of two positions not one --- not just the next column offset, but also
the index of the next "ptr" to use.  In the existing method it only
needs the column offset, because that's exactly where it can pick up
the next length from.

But the really serious objection is that the datatype functions that
access the data would now also need to be passed two pointers, since
after all they would like to know the length too.  That breaks APIs
far and wide :-(

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Terrible performance on wide selects

[Tom Lane]

Hannu Krosing <[EMAIL PROTECTED]> writes:
>> i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus
>> keep count of how many are actually valid,

> Additionally, this should also make repeted determining of NULL fields
> faster - just put a NULL-pointer in and voila - no more bit-shifting and
> AND-ing to find out if the field is null.

Right, the output of the operation would be a pair of arrays: Datum
values and is-null flags.  (NULL pointers don't work for pass-by-value
datatypes.)

I like the idea of keeping track of a last-known-column position and
incrementally extending that as needed.

I think the way to manage this is to add the overhead data (the output
arrays and last-column state) to TupleTableSlots.  Then we'd have
a routine similar to heap_getattr except that it takes a TupleTableSlot
and makes use of the extra state data.  The infrastructure to manage
the state data is already in place: for example, ExecStoreTuple would
reset the last-known-column to 0, ExecSetSlotDescriptor would be
responsible for allocating the output arrays using the natts value from
the provided tupdesc, etc.

This wouldn't help for accesses that are not in the context of a slot,
but certainly all the ones from ExecEvalVar are.  The executor always
works with tuples stored in slots, so I think we could fix all the
high-traffic cases this way.

regards, tom lane

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

http://archives.postgresql.org

BAD sig (was: Re: [HACKERS] v7.3.1 psql against a v7.2.x database...)

[Adrian 'Dagurashibanipal' von Bidder]

The keep-annoying-everybody-until-it-really-works caompain

gpg: armor header: Version: GnuPG v1.2.1 (FreeBSD)
gpg: Signature made Mit 22 Jan 2003 18:43:21 CET using DSA key ID 8C3ABF0C
gpg: BAD signature from "Rod Taylor (Database Developer) <[EMAIL PROTECTED]>"

On Mit, 2003-01-22 at 18:43, Rod Taylor wrote:
> X-Mailer: Ximian Evolution 1.2.1 

Arrgh! I really thought evo should be able to verify its own signatures
by now.

Same evo version on my side. I could not see any special oddities in
your mail.

cheers
-- vbi

-- 
featured product: SpamAssassin - http://spamassassin.org



signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE

[D'Arcy J.M. Cain]

On Thursday 23 January 2003 06:34, Curt Sampson wrote:
> The ideal sitaution for me would be to have WITHOUT OIDS be the default
> for all table creations, and but of course allow WITH OIDS for backward

Why not make it a configuration option?  I can actually think of a third 
behaviour that would make sense for me.  Have it so that OIDs are created by 
default if there is no primary key defined and not if there is.

-- 
D'Arcy J.M. Cain|  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

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

http://archives.postgresql.org

Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE

[Curt Sampson]

On Tue, 21 Jan 2003, Tom Lane wrote:

> We've gotten a couple of complaints now about the fact that 7.3 doesn't
> include an OID column in a table created via CREATE TABLE AS or SELECT
> INTO.  Unless I hear objections, I'm going to revert it to including an
> OID, and back-patch the fix for 7.3.2 as well.

I object. I personally think we should be moving towards not using OIDs
as the default behaviour, inasmuch as we can, for several reasons:

1. It's not a relational concept.

2. The OID wraparound problem can get you.

3. Other SQL databases don't do this.

4. It's hidden, rather than exposed, and hidden things are generally a
bad idea.

5. We should default to what gives us better performance, rather than
worse.

> See discussion a couple days ago on pgsql-general, starting at
> http://archives.postgresql.org/pgsql-general/2003-01/msg00669.php

There didn't seem to be many people clamouring to have it back.

The ideal sitaution for me would be to have WITHOUT OIDS be the default
for all table creations, and but of course allow WITH OIDS for backward
compatability. But yeah, I know that this can introduce problems with
old dumps, and may not be entirely easy to implement.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] Survey results from the PostgreSQL portal page

[Curt Sampson]

On Sun, 19 Jan 2003, [ISO-8859-1] Hans-J$B|(Brgen Sch$Bv(Bnig wrote:
(B
(B> >+ people measure postgresql by the speed of bulk imports
(B>
(B> This is a good point. I can complete agree. What we might need is
(B> something called "SQL Loader" or so. This may sound funny and it doesn't
(B> make technical sense but it is an OBVIOUS way of importing data. People
(B> often forget to use transactions or don't know about COPY.
(B
(BEven "doing it right," postgres 7.2 was significantly slower than MySQL
(Bfor bulk data imports, at least for tables with relatively narrow rows.
(BI was going to put this down to higher row overhead, except that it was
(Bnowhere near raw file I/O speed, either.
(B
(BSo this could use improvement, if it's not been improved already.
(B
(BThere's room for performance increases in a lot of other areas, too, but
(Bin the end, a lot of people just don't design their databases for good
(Bperformance. And I've killed enough non-postgres database servers in my
(Blife to know that if you don't really know what you're doing, you can
(Beasily make the performance of any DBMS suck. :-)
(B
(BPersonally, I think there's still a fair amount of room in the features
(Barea, too. I'm always running into something that I'd like to have.
(BToday it was being able to defer a UNIQUE constraint.
(B
(Bcjs
(B-- 
(BCurt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
(BDon't you know, in this new Dark Age, we're all light.  --XTC
(B
(B---(end of broadcast)---
(BTIP 5: Have you checked our extensive FAQ?
(B
(Bhttp://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Oracle rant

[Curt Sampson]

On Thu, 16 Jan 2003, Fred Zellinger wrote:

> With Oracle, you can screw around with files and tablespaces and
> extents and segments and partition striping and local and global
> indexing and block sizes and 400+ other tuning parameters to your
> heart's content. ... I am a control freak and I think a lot of
> other people are too. Oracle is tremendously complex and has a
> steep learning curve, but it gives me control. With PG, a lot
> of effort has been made to simplify. ... If PostgreSQL were to
> open up all the internals of storage and become as complex as
> Oracle, there probably would be a lot of high profile crashes and PG
> would get a bad reputation. However, I think that having a mode of
> operation(controlled at compile time) where all the dirty details of
> storage was made accessible in the data dictionary, would be something
> good to pacify the control freaks.

The reason you don't have all of these storage parameters available to
you, and they can't be "opened up" is that they simply aren't there.
Oracle uses raw devices and does all of its own management of disk space
at the block level. Postgres relies on the filesystem and the operating
system to deal with block allocation and a lot of the I/O scheduling.

Adding an effecient layer to replace this is a major project that would
probably provide few gains, since operating systems have gotten a lot
smarter about block allocation and I/O scheduling over the years. There
has been some discussion about this before, if you look back in the
archives.

There are various other places in postgres that the coders could work
on that are likely to provide more performance gain for less effort.
The optimizer comes to mind. At the low level, dropping shared memory
and moving to mmap might (but it's not certain) provide some noticable
improvement for not too much implementation effort.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

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

Re: [HACKERS] Terrible performance on wide selects

[Daniel Kalchev]

>>>Hannu Krosing said:
 > Tom Lane kirjutas N, 23.01.2003 kell 02:04:
 > > We already do cache column offsets when they are fixed.  The code that's
 > > the problem executes when there's a variable-width column in the table
 > > --- which means that all columns to its right are not at fixed offsets,
 > > and have to be scanned for separately in each tuple, AFAICS.
 > 
 > Not only varlen columns, but also NULL columns forbid knowing the
 > offsets beforehand.

Does this mean, that constructing tables where fixed length fields are 
'before' variable lenght fields and 'possibly null' fields might increase 
performance?

Daniel


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

Re: [HACKERS] Terrible performance on wide selects

[Hannu Krosing]

Hannu Krosing kirjutas N, 23.01.2003 kell 12:11:

> make the pointer array incrementally for O(N) performance:
> 
> i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus
> keep count of how many are actually valid,

Additionally, this should also make repeted determining of NULL fields
faster - just put a NULL-pointer in and voila - no more bit-shifting and
AND-ing to find out if the field is null.

One has to watch the NULL bitmap on fist pass anyway.

> so the first call to get col[5] will fill first 5 positions in the array
> save said nr 5 and then access tuple[ptrarray[5]]
> 
> next call to get col[75] will start form col[5] and fill up to col[75]
> 
> next call to col[76] will start form col[75] and fill up to col[76]
> 
> next call to col[60] will just get tuple[ptrarray[60]]
> 
> the above description assumes 1-based non-C arrays ;)
-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

http://archives.postgresql.org

Re: [HACKERS] Terrible performance on wide selects

[Hannu Krosing]

Tom Lane kirjutas N, 23.01.2003 kell 02:04:
> "Dann Corbit" <[EMAIL PROTECTED]> writes:
> > Maybe I don't really understand the problem, but it seems simple enough
> > to do it once for the whole query.
> 
> We already do cache column offsets when they are fixed.  The code that's
> the problem executes when there's a variable-width column in the table
> --- which means that all columns to its right are not at fixed offsets,
> and have to be scanned for separately in each tuple, AFAICS.

Not only varlen columns, but also NULL columns forbid knowing the
offsets beforehand.

-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

Re: [PERFORM] [HACKERS] Terrible performance on wide selects

[Hannu Krosing]

Dann Corbit kirjutas N, 23.01.2003 kell 02:39:
> [snip]
> > For the disk case, why not have the start of the record 
> > contain an array of offsets to the start of the data for each 
> > column?  It would only be necessary to have a list for 
> > variable fields.
> > 
> > So (for instance) if you have 12 variable fields, you would 
> > store 12 integers at the start of the record.
> 
> You have to store this information anyway (for variable length objects).
> By storing it at the front of the record you would lose nothing (except
> the logical coupling of an object with its length).  But I would think
> that it would not consume any additional storage.

I don't think it will win much either (except for possible cache
locality with really huge page sizes), as the problem is _not_ scanning
over big strings finding their end marker, but instead is chasing long
chains of pointers.

There could be some merit in the idea of storing in the beginning of
tuple all pointers starting with first varlen field (16 bit int should
be enough) 
so people can minimize the overhead by moving fixlen fields to the
beginning. once we have this setup, we no longer need the varlen fields
/stored/ together with field data. 

this adds complexity of converting form (len,data) to ptr,...,data) when
constructing the tuple

as  tuple (int,int,int,varchar,varchar)

which is currently stored as

(intdata1, intdata2, intdata3, (len4, vardata4), (len5,vardata5))

should be rewritten on storage to

(ptr4,ptr5),(intdata1, intdata2, intdata3, vardata4,vardata5)

but it seems to solve the O(N) problem quite nicely (and forces no
storage growth for tuples with fixlen fields in the beginning of tuple)

and we must also account for NULL fields in calculations .

-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Terrible performance on wide selects

[Hannu Krosing]

Tom Lane kirjutas N, 23.01.2003 kell 02:18:
> "Dann Corbit" <[EMAIL PROTECTED]> writes:
> > Why not waste a bit of memory and make the row buffer the maximum
> > possible length?
> > E.g. for varchar(2000) allocate 2000 characters + size element and point
> > to the start of that thing.
> 
> Surely you're not proposing that we store data on disk that way.
> 
> The real issue here is avoiding overhead while extracting columns out of
> a stored tuple.  We could perhaps use a different, less space-efficient
> format for temporary tuples in memory than we do on disk, but I don't
> think that will help a lot.  The nature of O(N^2) bottlenecks is you
> have to kill them all --- for example, if we fix printtup and don't do
> anything with ExecEvalVar, we can't do more than double the speed of
> Steve's example, so it'll still be slow.  So we must have a solution for
> the case where we are disassembling a stored tuple, anyway.
> 
> I have been sitting here toying with a related idea, which is to use the
> heap_deformtuple code I suggested before to form an array of pointers to
> Datums in a specific tuple (we could probably use the TupleTableSlot
> mechanisms to manage the memory for these).  Then subsequent accesses to
> individual columns would just need an array-index operation, not a
> nocachegetattr call.  The trick with that would be that if only a few
> columns are needed out of a row, it might be a net loss to compute the
> Datum values for all columns.  How could we avoid slowing that case down
> while making the wide-tuple case faster?

make the pointer array incrementally for O(N) performance:

i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus
keep count of how many are actually valid,

so the first call to get col[5] will fill first 5 positions in the array
save said nr 5 and then access tuple[ptrarray[5]]

next call to get col[75] will start form col[5] and fill up to col[75]

next call to col[76] will start form col[75] and fill up to col[76]

next call to col[60] will just get tuple[ptrarray[60]]

the above description assumes 1-based non-C arrays ;)

-- 
Hannu Krosing <[EMAIL PROTECTED]>

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

Re: Windows Build System was: [HACKERS] Win32 port patches

[Darko Prenosil]

On Wednesday 22 January 2003 20:47, [EMAIL PROTECTED] wrote:
> Firebird uses a set of Borland command line tools and Borland's make,
> which they give away as a free download.  Even if you're compiling for
> Windows, the build process uses Borland's command line "make".  A batch
> build script copies makefiles from a single source directory and spreads
> them around the tree, then kicks off Borland's make.  For things to work
> successfully, you must download Borland's tools and install them
> together with setting a few environment variables by hand.  Borland
> command line tools are just a set of Unix utilities like grep, sed,
> make, (sh?) etc.  Once upon a time they required cygwin utilities, but
> managed to purge themselves of cygwin with the Borland utilities.  When
> they required cygwin, they also required some Borland utilities anyway.
> So they had a real reason for purging cygwin.  If someone thinks the
> cygwin package is too big, we could require the Borland utilities instead
> :)
>
I can't agree more ! Even if You do not want to hear my opinion here it is:

The reason why I started to use PosgreSQL was:
1. Price 
2. Most features that I used on MSSQL was there (except replication).

If You require MS Visual C for building, it may increase the price.
Borland C++ free version 5.5 you can download from :
http://www.borland.com/products/downloads/download_cbuilder.html
(I newer downloaded it from there, because I got it on some CD from our
local computer news).
What is included in the package you can see at :
http://community.borland.com/article/0,1410,21205,00.html

I can point to one more example of using bcc32 in multiplatform projects:
http://www.trolltech.com/products/qt/windows.html
(They support also Microsoft Visual C++, but Windows version is not free !)

And in the end, the developers for windows are not stupid, they can survive 
without MS projects, workspaces, fancy GUI e.t.c. (at least developers that 
You want to contribute to Postgres.)


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

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] ECPG, threading and pooling

[Shridhar Daithankar<[EMAIL PROTECTED]>]

Hi all,

I would like to use ECPG as it is relatively easy to code. However my 
application is multithreaded and also uses connecion pools.

I would like to know if ECPG is thread safe and I can use an arbitrary PGconn* 
object pulled from a connection pool.

Do I need to use connection name? I mean how do I store connection name in an 
array or so?

Otherwise I would have to fiddle with libpq which is bit more difficult than 
ECPG.

TIA 

 Shridhar

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