Re: [GENERAL] table size/record limit

[Gaetano Mendola]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Dennis Gearon wrote:
| Gaetano Mendola wrote:
|
|> Dennis Gearon wrote:
|>
|>> I am designing something that may be the size of yahoo, google, ebay,
|>> etc.
|>>
|>> Just ONE many to many table could possibly have the following
|>> characteristics:
|>>
|>>3,600,000,000 records
|>
|> This is a really huge monster one, and if you don't partition that
|> table in some way I think you'll have nightmares with it...
|>
|> Regards
|> Gaetano Mendola
|>
| thanks for the input, Gaetano.
For partion in some way I don't mean only split it in more tables. You
can use some available tools in postgres and continue to see this table
as one but implemented behind the scenes with more tables.
One usefull and impressive way is to use the inheritance in order to obtain
a vertical partition
0) Decide a partition policy ( based on time stamp for example )
1) Create an empty base table with the name that you want see as "public"
2) Create the partition using the empty table as base table
3) Create a rule on the base table so an insert or the update on it is
~   performed as a insert or an update on the right table ( using the partition
~   policy at step 0 )
in this way you are able to vacuum each partition, reindex each partition and
so on in a more "feseable way" I do not immagine vacuum full or reindex a
3,600,000,000 records table...

Regards
Gaetano Mendola










-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBeLiK7UpzwH2SGd4RAh+TAJ4w89SvkFWgt9DGhQx/aUR6j2wDtwCgtut5
FN0OuoycbI37a8Wouvo3icw=
=Wb6h
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Is it possible to remove the public schema?

[Henry Combrinck]

> No problem at all.
> It's easy to automate the table linking process.
>
> I have a table in access that holds - among other things - the internal
> and external name of my linked tables, in which database, schema and
> server they locate.
[snip]

Thank you very much for the information.

Regards
Henry



This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus, 
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now!  086 11 11 440

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

Re: [GENERAL] Information about storge engine in PostgreSQL

[nd02tsk]

I really appreciate these type of high-quality anwsers, thank you.

Tim

> On 10/21/2004 10:27 AM, [EMAIL PROTECTED] wrote:
>
>> Hello
>>
>> MySQL has information about several storage engines. MEMORY to handle
>> temporary tables, InnoDB to handle transactions and which also can split
>> its table data over several files/partitions. Splitting of storage is
>> something which according to the following article, PostgreSQL does not
>> support:
>
> For a long time the MySQL documentation was stating that foreign keys
> are mainly for documentation purposes and explained why you really
> didn't want them and why it was so much better that MySQL swallowed
> their syntax silently without any effect. Similarly dangerous opinions
> where documented about transactions and ACID features.
>
> Then the InnoDB table handler was added to MySQL and with the new
> features, namely transactions and referential integrity, the documented
> opinion about these features was changed. But since every other database
> had these features for long already, all that was left was now the
> capability of having different storage engines, and it became the new
> advantage feature to point out.
>
> Right now on their boiler plate is another buzzword compliant table
> handler, the NDB cluster storage engine. And while a lot of people are
> getting all excited about it, all I really see so far is yet another
> table handler that does not provide foreign keys, that does not
> integrate with the existing transaction systems ACID properties, and
> that has outrageous network and memory requirements. Especially worried
> am I about the fact that the responsibility for referential integrity,
> that was lifted from the developers shoulders with the InnoDB tables, is
> now dropped twice as heavy back into his laps. I don't think that Web
> developers who had problems getting integrity constraints implemented in
> the application before InnoDB will do this much better in a concurrent
> multimaster cluster environment. But I am sure enough PHB's who, free
> from every knowledge obstacles, fully believe in marketing speech will
> force their developers into that nightmare.
>
> None of all these advanced storage engines was developed by MySQL. They
> all got purchased and turned into table handlers. The multiple storage
> engine capability of MySQL is the technical base for stapling together
> those features, MySQL isn't able to build into the existing system and
> has to buy somewhere else.
>
> The PostgreSQL philosophy is a little different. That is why we have
> only one, tightly integrated and not very easy to replace storage engine.
>
>
> Jan
>
> --
> #==#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.  #
> #== [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

[GENERAL] FKs and deadlocks

[Philippe Lang]

Hello,

As the amount of simultaneous users of my database grows (25 users
sometimes, PGSQL 7.4.5), deadlocks are unfortunately more and more
frequent. I guess this is due to the FKs problem with Postgresql.

I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
in every trigger and function, hoping it would solve my problem. Maybe
it helped, but it did not solve anything.

I don't know if anyone has a better idea, but I would like to try taking
away some FKs in my schema. My problem is that I really don't know which
one to delete. There are over 40 tables. Are there rules to do that? Or
maybe can I simply wait on the next deadlock, and try understanding who
got locked by who? OK, but how can I do that?

Thanks for your help!

Philippe

Note: I have read about a patch for FK's, is that something that can
really be used in production?

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

[GENERAL] PostgreSQL-related job posting

[John Wells]

Guys,

I have an opening currently for the following position.  If interested,
email me at john.wells__replace_this_with_at__timco.aero.  Although relo
is stated as not covered, I may be able to fight for that given a very
qualified candidate.

Company:TIMCO Aviation Services
Location:   US-NC-Greensboro
Base Pay:   N/A
Employee Type:  Full-Time Employee
Industry:   Airline - Aviation
Computer Software
Manages Others: No

Job Type:   Information Technology
Req'd Education:4 Year Degree
Req'd Experience:   At Least 1 Year
Req'd Travel:   None
Relocation Covered: No


 DESCRIPTION
Administers all areas of Linux and Unix systems, with specific emphasis on
Linux. Some PostgreSQL database administration also required.

 REQUIREMENTS
1. Bachelor Degree in Computer Science, Information Systems or related
fields.
2. Minimum of 2 years experience administering Linux/UNIX systems.
3. Minimum 1 year experience administering PostgreSQL databases, including
indexing, DBMS tuning, and query optimization.
4. Minimum 1 year experience with database design, including good
foundations in data normalization and referential integrity.
5. 1-3 years experience with Red Hat Linux and one of the following:
Slackware Linux, Debian Linux, SUSE Linux, Gentoo Linux.
6. 1-2 years experience with one of the following: Perl, Python, PHP.
7. Experience with UNIX, TCP/IP, firewalls, SAN, WAN, LAN, and network
monitoring.
8. Strong shell scripting experience required.
9. Must be willing to work in a 24 x 7 environment
10. Good written and verbal communications skills.
11. Must be a team player with advanced interpersonal/customer service
skills and a very positive attitude.


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

Re: [GENERAL] Row versioning

[Ruediger Herrmann]

thanks for your replay. This approach sounds very comfy. As I read the
documentation this is kind of a "transaction sequence" or better a
"unique transaction id". Am I right with this? So every row inserted or
updated within the same transaction is tagged with the same xmin.

Is there any information wether this approach is future proof? I heard
the OID is depecated now, maybe XMIN is next, no idea...

TIA
Ruediger


> > has anyone implemented row versions/timestamps in
> PostgreSQL or any
> > thoughts on this?
> > Did I hit the right term? What I want to achieve
> is optimistic 
> > concurrency beyound transaction boundaries. When
> retrieving data
> > I would also retrieve the row version and later
> on, in a different
> > transaction, before updating the data, I could
> check if was unchanged.
> 
> You could use the xmin system column for this.
> 
>   regards, tom lane
> 
> 
> 

-- 
Geschenkt: 3 Monate GMX ProMail + 3 Ausgaben der TV Movie mit DVD
 Jetzt anmelden und testen http://www.gmx.net/de/go/mail 


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

Re: [GENERAL] FKs and deadlocks

[Stephan Szabo]

On Fri, 22 Oct 2004, Philippe Lang wrote:

> I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
> in every trigger and function, hoping it would solve my problem. Maybe
> it helped, but it did not solve anything.

Note that set constraints all deferred does nothing unless you made the
constraint deferrable which is not the default.  If your constraints
aren't then you won't see any effect, and you'll probably want to change
them and see if that does help (and possibly making them initially
deferred at the same time).

> I don't know if anyone has a better idea, but I would like to try taking
> away some FKs in my schema. My problem is that I really don't know which
> one to delete. There are over 40 tables. Are there rules to do that? Or
> maybe can I simply wait on the next deadlock, and try understanding who
> got locked by who? OK, but how can I do that?

I think you may be able to do this if you turn on statement locking and
try to resurrect the state from the logs.  If you want to send a possibly
slightly sanitized typical sequence of events, we might be able to help
with that part.

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

Re: [GENERAL] table size/record limit

[Dennis Gearon]

Great Idea! When I get that far, I will try it.
Gaetano Mendola wrote:

For partion in some way I don't mean only split it in more tables. You
can use some available tools in postgres and continue to see this table
as one but implemented behind the scenes with more tables.
One usefull and impressive way is to use the inheritance in order to obtain
a vertical partition
0) Decide a partition policy ( based on time stamp for example )
1) Create an empty base table with the name that you want see as "public"
2) Create the partition using the empty table as base table
3) Create a rule on the base table so an insert or the update on it is
~   performed as a insert or an update on the right table ( using the 
partition
~   policy at step 0 )

in this way you are able to vacuum each partition, reindex each 
partition and
so on in a more "feseable way" I do not immagine vacuum full or reindex a
3,600,000,000 records table...

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

Re: [GENERAL] FKs and deadlocks

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 22 Oct 2004, Philippe Lang wrote:
>> I don't know if anyone has a better idea, but I would like to try taking
>> away some FKs in my schema. My problem is that I really don't know which
>> one to delete. There are over 40 tables. Are there rules to do that? Or
>> maybe can I simply wait on the next deadlock, and try understanding who
>> got locked by who? OK, but how can I do that?

> I think you may be able to do this if you turn on statement locking and
> try to resurrect the state from the logs.

Also look in pg_locks and pg_activity.

regards, tom lane

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

Re: [GENERAL] Row versioning

[Tom Lane]

"Ruediger Herrmann" <[EMAIL PROTECTED]> writes:
> Is there any information wether this approach is future proof?

[ shrug... ]  As much as anything that's not specified by the SQL
standard is around here.  We have no plans to replace MVCC, and
xmin/xmax are a pretty fundamental part of that.

regards, tom lane

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

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

Re: [GENERAL] Two questions from the boss (SQL:2003 && scalability)

[Richard_D_Levine]

I've worked with Oracle, Interbase, and Informix.  PostgreSQL is the most
SQL spec compliant of any of them, whether the spec is 89, 92, or 03.  I
have not worked with MySQL.

Rick


   
  
  Robert Treat 
  
  <[EMAIL PROTECTED]To:   John Wells <[EMAIL 
PROTECTED]>  
  ge.net>cc:   "[EMAIL PROTECTED]" 
<[EMAIL PROTECTED]> 
  Sent by:   Subject:  Re: [GENERAL] Two 
questions from the boss (SQL:2003 && scalability)   
  [EMAIL PROTECTED]

  tgresql.org  
  
   
  
   
  
  10/21/2004 03:56 PM  
  
   
  
   
  




On Thu, 2004-10-21 at 15:40, John Wells wrote:
> Guys,
>
> My boss has been keeping himself busy reading MySQL marketing pubs,
> and came at me with a few questions this morning regarding PostgreSQL
> features (we're currently moving to PostgreSQL).
>

I'd be interested to see what my$ql has to say about SQL:2003
compliance...

> While I don't think either are really that important for our
> situation, he wanted to know specifically whether PostgreSQL supported
> SQL:2003,

Well, certainly it is not in full compliance, but then who is? I would
say that most of the new features in SQL:2003 are not supported yet,
however if you run into a specific one that you could actually use post
to the list and you'll likely get a good work-around.

and what sort of capabilities PostgreSQL has to scale across
> multiple CPUs and hosts (multithreading, load balancing, etc).
>

Well, PostgreSQL can certainly take advantage of multiple CPU's,
although there are some cases where we could do more (use multiple CPU
on one query). You can also use a combination of tools like pgpool and
slony to set up load balancing depending on your needs... though I
should say that PostgreSQL has tremendous ability to scale up even
without getting into all the buzzword friendly schemes.


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


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






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

Re: [GENERAL] correct example of a functional index usage?

[Dennis Gearon]

But it is possible to use multiple indexes on dates, and that is why the one at the 
bottom works, right?
Would a single index get used for
SELECT appointment
FROM the_table
WHERE 0 <> (date_mask &&  date_range);
Tom Lane wrote:

No, an index can be used for one or the other.  Since we don't yet have
bitmap combining of indexes, you don't get to apply two indexes in a
single query.  Even if you did, this would be relatively inefficient
since each index would return a whole lotta rows.
Why don't you just do the straightforward thing and look for
WHERE appointment_date >= 'some_date'
  AND appointment_date <= 'some-other-date'
AFAICS that solves the stated problem.  Maybe you were not being clear
about what you want?
regards, tom lane

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

Re: [GENERAL] Slony-I 1.0.4 Released

[Jan Wieck]

Sorry folks,
the Slony-I team has produced a great product, but the project 
management (that's mostly me here) sucks big time!

Shortly after giving Chris Browne green light for the 1.0.4 announcement 
we found a way to guard against bug #896. That being a really bad one I 
decided to stop the 1.0.4 release and go for 1.0.5 including that fix. 
But I failed to make sure Chris, Justin and others involved in the 
announcement process get the message.

I have just committed those changes and 1.0.5 should be available later 
today.

I apologize for the confusion and look forward to retire from the 
Slony-I project leadership position in order to fully focus on the 
multimaster replication project Afilias has decided to put forward.

Jan
On 10/21/2004 5:55 PM, Chris Browne wrote:
The Slony-I team is proud to present the 1.0.4 release of the most
advanced replication solution for the most advanced Open Source
Database in the world.
The release tarball is available for download 
   http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar.gz

There are a limited number of "new features" this release largely in
terms of adding in the ability to move or drop tables and sequences
from replication:
 - SET DROP TABLE - drops a table from replication
 - SET DROP SEQUENCE - does the same for sequences
 - SET MOVE TABLE - moves a table from one replication set to another
 - SET MOVE SEQUENCE - moves a sequence from one replication set to another
Other changes involve smoothing out the 'sharp edges' found by early
adopters, notably including:
 - Frequently vacuuming pg_listener; growth of dead tuples could hurt
   performance
 
 - A cleanup process for pg_listener resolves cases where old slon
   processes may have terminated due to network problems, leaving
   backends around holding onto event notifications

 - Lowered lock level on sl_event, resolving issues where pg_dump
   would block Slony-I
 - Purges CONFIRM entries for nodes that don't exist anymore
 - Substantially increased documentation
 - More sophisticated administration scripts 

 - Now uses string comparison for user defined types that do not have
   a suitable comparison operation
 - Safer log purging
 - Various other bug fixes and "improved cleanliness."
 
 - As of 1.0.4, the slon replication engine refuses to work against
   any database that does not have the stored procedures for the same
   version loaded or where the shared object containing the C language
   support functions and the log trigger does not match the version
   number.  in a cluster must be upgraded at once.

See the HISTORY-1.0 file for a detailed list of changes.


--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [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: [GENERAL] correct example of a functional index usage?

[Tom Lane]

Dennis Gearon <[EMAIL PROTECTED]> writes:
> But it is possible to use multiple indexes on dates, and that is why the one at the 
> bottom works, right?

No, it is possible to use multiple conditions that are relevant to a
single index.  A range query like "where x >= lobound and x <= hibound"
works very nicely with a btree index on x.  But "where x >= lobound and
y <= hibound" isn't a range query.

> Would a single index get used for

> SELECT appointment
> FROM the_table
> WHERE 0 <> (date_mask &&  date_range);

I don't see any indexable operator there at all.  You might care to read
http://developer.postgresql.org/docs/postgres/xindex.html
which describes the behaviors Postgres indexes have.

regards, tom lane

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

[GENERAL] PlPERL and shared libraries on Suse

[Eric E]

Hi,
   I have an installation of Postgres 7.4.2 on SuSE 9.1.  This version 
of SuSE comes with a binary for plperl and several other postgres 
procedural languages.  All the others, including plpgsql install without 
a problem, but executing:
   createlang -u postgres plperl template1
produces the result:
createlang: language installation failed: ERROR:  could not load library 
"/usr/lib/postgresql/plperl.so": libperl.so: cannot open shared object 
file: No such file or directory

The file, however, is there:
# ls -l /usr/lib/postgresql/plperl.so
-rwxr-xr-x  1 root root 37097 Apr  5  2004 /usr/lib/postgresql/plperl.so
Some googling gave me the idea that it may be a problem with the way 
perl was compiled - i.e, perl is not compiled for shared libraries.  Can 
anyone confirm this?  If so, do I need to rebuild perl, or pl/perl, or 
both?  Are there any binaries that can resolve this?  (yes not wanting 
to compile plperl is plain old laziness, but I do have reservations 
about recompiling the perl interpreter)

Any ideas would be helpful.
Thanks,
Eric
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [Slony1-general] Re: [GENERAL] Slony-I 1.0.4 Released

[Jan Wieck]

On 10/22/2004 11:29 AM, Ed L. wrote:
Wow.  First, thanks again for all your efforts, Jan.  Second, I'm 
disappointed to hear the slony author and lead developer is leaving the 
slony leadership.  When is that going to happen?  And what does that mean 
with respect to your future involvement in slony?
It means that I will try to do with Slony-I what I have done 
successfully with several other PostgreSQL related projects - to make 
sure the project doesn't depend on me in person any more. None of the 
big items I've done (rewrite rule system, PL/pgSQL, PL/Tcl, TOAST, 
NUMERIC, foreign keys) really requires my attention if there's a bug. I 
was able to do the conceptual work and important parts of the 
implementation, but I didn't get stuck in/with the maintenance. I 
constider this a very important aspect of being a CORE developer.

What I am trying to do is to spend less and less time on Slony-I and 
more and more time on Slony-II - the synchronous multi-master system. 
Slony-I was for sure one of the better things that I've done so far, but 
it's not going to allow early retirement with financial independance and 
wealth. And as long as Afilias is using Slony-I in production, Andrew 
Sullivan will not let me do whatever I want if there's a severe problem 
nobody else can fix.

So don't worry, I'll be around.
Jan
Ed
On Friday October 22 2004 7:26, Jan Wieck wrote:
Sorry folks,
the Slony-I team has produced a great product, but the project
management (that's mostly me here) sucks big time!
Shortly after giving Chris Browne green light for the 1.0.4 announcement
we found a way to guard against bug #896. That being a really bad one I
decided to stop the 1.0.4 release and go for 1.0.5 including that fix.
But I failed to make sure Chris, Justin and others involved in the
announcement process get the message.
I have just committed those changes and 1.0.5 should be available later
today.
I apologize for the confusion and look forward to retire from the
Slony-I project leadership position in order to fully focus on the
multimaster replication project Afilias has decided to put forward.
Jan
On 10/21/2004 5:55 PM, Chris Browne wrote:
> The Slony-I team is proud to present the 1.0.4 release of the most
> advanced replication solution for the most advanced Open Source
> Database in the world.
>
> The release tarball is available for download
>   
> http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar
>.gz
>
> There are a limited number of "new features" this release largely in
> terms of adding in the ability to move or drop tables and sequences
> from replication:
>
>  - SET DROP TABLE - drops a table from replication
>  - SET DROP SEQUENCE - does the same for sequences
>  - SET MOVE TABLE - moves a table from one replication set to another
>  - SET MOVE SEQUENCE - moves a sequence from one replication set to
> another
>
> Other changes involve smoothing out the 'sharp edges' found by early
> adopters, notably including:
>
>  - Frequently vacuuming pg_listener; growth of dead tuples could hurt
>performance
>
>  - A cleanup process for pg_listener resolves cases where old slon
>processes may have terminated due to network problems, leaving
>backends around holding onto event notifications
>
>  - Lowered lock level on sl_event, resolving issues where pg_dump
>would block Slony-I
>
>  - Purges CONFIRM entries for nodes that don't exist anymore
>
>  - Substantially increased documentation
>
>  - More sophisticated administration scripts
>
>  - Now uses string comparison for user defined types that do not have
>a suitable comparison operation
>
>  - Safer log purging
>
>  - Various other bug fixes and "improved cleanliness."
>
>  - As of 1.0.4, the slon replication engine refuses to work against
>any database that does not have the stored procedures for the same
>version loaded or where the shared object containing the C language
>support functions and the log trigger does not match the version
>number.  in a cluster must be upgraded at once.
>
> See the HISTORY-1.0 file for a detailed list of changes.
>
> 

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] PlPERL and shared libraries on Suse

[Steve Atkins]

On Fri, Oct 22, 2004 at 12:13:12PM -0400, Eric E wrote:

> Hi,
>I have an installation of Postgres 7.4.2 on SuSE 9.1.  This version 
> of SuSE comes with a binary for plperl and several other postgres 
> procedural languages.  All the others, including plpgsql install without 
> a problem, but executing:
>createlang -u postgres plperl template1
> produces the result:
> createlang: language installation failed: ERROR:  could not load library 
> "/usr/lib/postgresql/plperl.so": libperl.so: cannot open shared object 
> file: No such file or directory
> 
> The file, however, is there:
> 
> # ls -l /usr/lib/postgresql/plperl.so
> -rwxr-xr-x  1 root root 37097 Apr  5  2004 /usr/lib/postgresql/plperl.so
> 
> Some googling gave me the idea that it may be a problem with the way 
> perl was compiled - i.e, perl is not compiled for shared libraries.  Can 
> anyone confirm this?  If so, do I need to rebuild perl, or pl/perl, or 
> both?  Are there any binaries that can resolve this?  (yes not wanting 
> to compile plperl is plain old laziness, but I do have reservations 
> about recompiling the perl interpreter)

The error message gives you the hint you need. The missing file isn't
plperl.so - it's libperl.so.

libperl.so is the dynamic library version of perl. It's not usually
installed by most people building perl. (It's also not neccesarily
needed by plperl.so, as libperl.a can be statically linked into
plperl.so, I believe).

I don't know SuSEs package system (I always install from source) but
there may well be another perl package that would provide libperl.so.
Failing that you'll either need to build a perl installation and tell
Configure to build libperl.so, or rebuild plperl.so. It's also
possible that you do have libperl.so somewhere, but it's not on
the standard library search path and not where plperl expects
to find it.

'ldd' is a useful command for tracking down this sort of problem.

Cheers,
  Steve

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

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

Re: [GENERAL] PlPERL and shared libraries on Suse

[Tom Lane]

Eric E <[EMAIL PROTECTED]> writes:
> createlang: language installation failed: ERROR:  could not load library 
> "/usr/lib/postgresql/plperl.so": libperl.so: cannot open shared object 
> file: No such file or directory

> The file, however, is there:

> # ls -l /usr/lib/postgresql/plperl.so
> -rwxr-xr-x  1 root root 37097 Apr  5  2004 /usr/lib/postgresql/plperl.so

It's complaining that it can't find libperl.so (ie, the Perl interpreter)
not plperl.so.  Do you have a libperl.so?  Is it in your ldconfig search
path?

regards, tom lane

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

[GENERAL] A Simple Question

[Terry Lee Tucker]

Greetings:

While working with plpgsql and triggers, I've been using TG_NAME to display 
the trigger name at various points where I need to raise an exception and 
return a message. I was thinking how nice it would be if I could display the 
line number as well. Is there a "special" variable that does this? If not, is 
this something that would be a nice feature to add?

Thanks...

-- 

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

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

Re: [GENERAL] Two questions from the boss (SQL:2003 && scalability)

[Andrew Sullivan]

On Thu, Oct 21, 2004 at 03:40:23PM -0400, John Wells wrote:
> Guys,
> 
> My boss has been keeping himself busy reading MySQL marketing pubs,
> and came at me with a few questions this morning regarding PostgreSQL
> features (we're currently moving to PostgreSQL).

I should point out that there's a whoile bunch of folks on the
-advocacy list who'd love to do any debunking necessary, if you want.

> situation, he wanted to know specifically whether PostgreSQL supported
> SQL:2003, 

I know of no system which completely meets SQL 2003.  Many of the
features in the 2003 standard are available in PostgreSQL, however,
and compliance with the standard is very much the goal of the
project.  Given that MySQL has possibly the worst SQL standards
conformance of all systems claiming to conform, I can't imagine this
would be a real point of contention.  (Note that MySQL is getting
much better on this front, but they still have a very long way to
go.)

> and what sort of capabilities PostgreSQL has to scale across
> multiple CPUs and hosts (multithreading, load balancing, etc).

PostgreSQL can scale across multiple CPUs, but it is not
multithreaded.  Of the "across hosts" question refers to the new
table handler which sort-of mostly provides multi-master capability
to MySQL, I suggest _very strongly_ you get all the details on that
system before plonking down your money.  There are some remarkably
serious problems with it, in my (not very humble) opinion.  I did an
analysis of it this year for my employers.  I'm not at liberty to
publish the resulting memo, but I would say that, at the very least,
it's important to understand how the table types, concurrency, and
transaction control all work together.  (I believe one value for
"work together" there is "don't".)

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

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

Re: [GENERAL] Two questions from the boss (SQL:2003 && scalability)

[Jan Wieck]

On 10/21/2004 3:40 PM, John Wells wrote:
Guys,
My boss has been keeping himself busy reading MySQL marketing pubs,
and came at me with a few questions this morning regarding PostgreSQL
features (we're currently moving to PostgreSQL).
While I don't think either are really that important for our
situation, he wanted to know specifically whether PostgreSQL supported
SQL:2003, and what sort of capabilities PostgreSQL has to scale across
multiple CPUs and hosts (multithreading, load balancing, etc).
That "multiple hosts" sounds that he came across the NDB cluster stuff 
that will become available in MySQL someday. Be aware that this new 
table handler will to my knowledge NOT support foreign keys. So the 
enforcement of referential integrity is back to the application, now in 
a multimaster cluster. I don't think that's a good idea, nor do I think 
it will be easier to add this later instead of doing it right in the 
initial design phase, but my way of solving problems is not the way 
MySQL plans their features.

Jan
I appreciate any input you can provide.
Thanks,
JB
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] correct example of a functional index usage?

[Dennis Gearon]

The site seems to be down Tom.
Tom Lane wrote:

I don't see any indexable operator there at all.  You might care to read
http://developer.postgresql.org/docs/postgres/xindex.html
which describes the behaviors Postgres indexes have.
regards, tom lane
 


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

Re: [Slony1-general] Re: [GENERAL] Slony-I 1.0.4 Released

[Ed L.]

Wow.  First, thanks again for all your efforts, Jan.  Second, I'm 
disappointed to hear the slony author and lead developer is leaving the 
slony leadership.  When is that going to happen?  And what does that mean 
with respect to your future involvement in slony?

Ed


On Friday October 22 2004 7:26, Jan Wieck wrote:
> Sorry folks,
>
> the Slony-I team has produced a great product, but the project
> management (that's mostly me here) sucks big time!
>
> Shortly after giving Chris Browne green light for the 1.0.4 announcement
> we found a way to guard against bug #896. That being a really bad one I
> decided to stop the 1.0.4 release and go for 1.0.5 including that fix.
> But I failed to make sure Chris, Justin and others involved in the
> announcement process get the message.
>
> I have just committed those changes and 1.0.5 should be available later
> today.
>
> I apologize for the confusion and look forward to retire from the
> Slony-I project leadership position in order to fully focus on the
> multimaster replication project Afilias has decided to put forward.
>
>
> Jan
>
> On 10/21/2004 5:55 PM, Chris Browne wrote:
> > The Slony-I team is proud to present the 1.0.4 release of the most
> > advanced replication solution for the most advanced Open Source
> > Database in the world.
> >
> > The release tarball is available for download
> >   
> > http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar
> >.gz
> >
> > There are a limited number of "new features" this release largely in
> > terms of adding in the ability to move or drop tables and sequences
> > from replication:
> >
> >  - SET DROP TABLE - drops a table from replication
> >  - SET DROP SEQUENCE - does the same for sequences
> >  - SET MOVE TABLE - moves a table from one replication set to another
> >  - SET MOVE SEQUENCE - moves a sequence from one replication set to
> > another
> >
> > Other changes involve smoothing out the 'sharp edges' found by early
> > adopters, notably including:
> >
> >  - Frequently vacuuming pg_listener; growth of dead tuples could hurt
> >performance
> >
> >  - A cleanup process for pg_listener resolves cases where old slon
> >processes may have terminated due to network problems, leaving
> >backends around holding onto event notifications
> >
> >  - Lowered lock level on sl_event, resolving issues where pg_dump
> >would block Slony-I
> >
> >  - Purges CONFIRM entries for nodes that don't exist anymore
> >
> >  - Substantially increased documentation
> >
> >  - More sophisticated administration scripts
> >
> >  - Now uses string comparison for user defined types that do not have
> >a suitable comparison operation
> >
> >  - Safer log purging
> >
> >  - Various other bug fixes and "improved cleanliness."
> >
> >  - As of 1.0.4, the slon replication engine refuses to work against
> >any database that does not have the stored procedures for the same
> >version loaded or where the shared object containing the C language
> >support functions and the log trigger does not match the version
> >number.  in a cluster must be upgraded at once.
> >
> > See the HISTORY-1.0 file for a detailed list of changes.
> >
> > 


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

[GENERAL] Problem with query plan

[Cott Lang]

I have come up with a simple query that runs horribly depending on the
number of columns selected.

select order_lines.*
from orders, order_lines
where orders.merchant_order_id = '11343445' and
  order_lines.order_id=orders.order_id;

merchant_order_id is indexed.
order_id is indexed.
Tables are analyzed.

I get the following plan:

-
 Merge Join  (cost=nan..nan rows=3 width=1257)
   Merge Cond: ("outer".order_id = "inner".order_id)
   ->  Sort  (cost=5.33..5.33 rows=2 width=4)
 Sort Key: orders.order_id
 ->  Index Scan using ak_po_number on orders  (cost=0.00..5.32
rows=2 width=4)
   Index Cond: ((merchant_order_id)::text =
'11343445'::text)
   ->  Sort  (cost=nan..nan rows=2023865 width=1257)
 Sort Key: order_lines.order_id
 ->  Seq Scan on order_lines  (cost=0.00..83822.65 rows=2023865
width=1257)

If I restrict the columns (i.e., select 1 from ...), it works great.

I can add columns and it seems that once I get a width of more than
~610, it executes a Merge Join of cost nan that takes forever to return.

If I reduce the columns returned to slightly below this, I get a much
nicer plan:

--
 Nested Loop  (cost=0.00..16.60 rows=4 width=606)
   ->  Index Scan using ak_po_number on orders  (cost=0.00..5.69 rows=3
width=4)
 Index Cond: ((merchant_order_id)::text = '11343445'::text)
   ->  Index Scan using ak_order_line_doid on order_lines 
(cost=0.00..3.61 rows=2 width=610)
 Index Cond: (order_lines.order_id = "outer".order_id)

Is this possibly just an overflow that causes a NaN that isn't properly
handled by the optimizer?

This is on Redhat 3.0 AS U3 x86 with the RPMs from postgresql.org.

Thanks!




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

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

Re: [GENERAL] PlPERL and shared libraries on Suse

[Eric E]

Hi Steve and Tom,
Thanks for the tip, I was clearly not reading the error message closely
enough.
I copied libperl.so into /lib, and now everything works.
Many thanks,
Eric
Steve Atkins wrote:
On Fri, Oct 22, 2004 at 12:13:12PM -0400, Eric E wrote:

Hi,
  I have an installation of Postgres 7.4.2 on SuSE 9.1.  This version 
of SuSE comes with a binary for plperl and several other postgres 
procedural languages.  All the others, including plpgsql install without 
a problem, but executing:
  createlang -u postgres plperl template1
produces the result:
createlang: language installation failed: ERROR:  could not load library 
"/usr/lib/postgresql/plperl.so": libperl.so: cannot open shared object 
file: No such file or directory

The file, however, is there:
# ls -l /usr/lib/postgresql/plperl.so
-rwxr-xr-x  1 root root 37097 Apr  5  2004 /usr/lib/postgresql/plperl.so
Some googling gave me the idea that it may be a problem with the way 
perl was compiled - i.e, perl is not compiled for shared libraries.  Can 
anyone confirm this?  If so, do I need to rebuild perl, or pl/perl, or 
both?  Are there any binaries that can resolve this?  (yes not wanting 
to compile plperl is plain old laziness, but I do have reservations 
about recompiling the perl interpreter)

The error message gives you the hint you need. The missing file isn't
plperl.so - it's libperl.so.
libperl.so is the dynamic library version of perl. It's not usually
installed by most people building perl. (It's also not neccesarily
needed by plperl.so, as libperl.a can be statically linked into
plperl.so, I believe).
I don't know SuSEs package system (I always install from source) but
there may well be another perl package that would provide libperl.so.
Failing that you'll either need to build a perl installation and tell
Configure to build libperl.so, or rebuild plperl.so. It's also
possible that you do have libperl.so somewhere, but it's not on
the standard library search path and not where plperl expects
to find it.
'ldd' is a useful command for tracking down this sort of problem.
Cheers,
  Steve
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faqs/FAQ.html

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

Re: [GENERAL] Problem with query plan

[Tom Lane]

Cott Lang <[EMAIL PROTECTED]> writes:
>->  Sort  (cost=nan..nan rows=2023865 width=1257)

What PG version is this?  My recollection is we fixed such a thing quite
some time ago ...

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Problem with query plan

[Cott Lang]

Oops, sorry - guess I left that out - 7.4.5. :)


On Fri, 2004-10-22 at 12:28, Tom Lane wrote:
> Cott Lang <[EMAIL PROTECTED]> writes:
> >->  Sort  (cost=nan..nan rows=2023865 width=1257)
> 
> What PG version is this?  My recollection is we fixed such a thing quite
> some time ago ...
> 
>   regards, tom lane


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

   http://archives.postgresql.org

Re: [GENERAL] Problem with query plan

[Tom Lane]

Cott Lang <[EMAIL PROTECTED]> writes:
> Oops, sorry - guess I left that out - 7.4.5. :)

Hmm ... I can't duplicate any misbehavior here.  Are you using
nondefault values for any planner parameters?  (particularly sort_mem,
random_page_cost, effective_cache_size)

regards, tom lane

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

[GENERAL] unexpected field count in D message

[Hicham G. Elmongui]

what does this message mean?

thanks,
--h



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

   http://archives.postgresql.org

Re: [GENERAL] delayed input

[Hicham G. Elmongui]

Well, it seems that i have to build the scan operator myself. Even the
FunctionScan will make all the function calls and stores the result in a
tuuplestore. So, all the delay will be occured only at the first function
invocation.
--h




"Alvaro Herrera" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, Oct 19, 2004 at 01:44:34PM -0500, Hicham G. Elmongui wrote:
> > I need this for a side project. Is there a way to do something like
this:
> >
> > SELECT *
> > FROM DelayedTable('tablename', 5);
>
> You can probably build a sleep() function in C, and then use that to
> cause delaying in a PL/pgSQL set-returning function.  Something like
>
> #include 
> #include 
>
> PG_FUNCTION_INFO_V1(sleep);
>
> Datum
> sleep(PG_FUNCTION_ARGS)
> {
> int32   delay = PG_GETARG_INT32(0);
> sleep(delay);
> PG_RETURN_VOID();
> }
>
> -- 
> Alvaro Herrera ()
> La web junta la gente porque no importa que clase de mutante sexual seas,
> tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
> ciervos incendiándose", y el computador dirá "especifique el tipo de
ciervo"
> (Jason Alexander)
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>



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

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

Re: [Slony1-general] Re: [GENERAL] Slony-I 1.0.4 Released

[Andrew Sullivan]

On Fri, Oct 22, 2004 at 09:36:10AM -0400, Jan Wieck wrote:
> wealth. And as long as Afilias is using Slony-I in production, Andrew 
> Sullivan will not let me do whatever I want if there's a severe problem 
> nobody else can fix.

Or someone around here will, anyway.   I might get hit by a wayward
streetcar, but Afilias is very strongly committed to maintaining
and improving Slony-I even as we also sponsor work on multimaster
systems.  Afilias uses PostgreSQL for almost everything we do;
for us, there is no question that we'll need to support Slony.

It's worth pointing out here that the really big database vendors
usually have more than one thing that they sell as "replication". 
That's because you can't really use (for instance) RAC across a WAN. 
But if you're running a bank, or network infrastructure, or a phone
company, or anything else that requires you to have plans for the
disaster recovery of your disaster recovery site, you'll certainly
need to have replicas which are geographically dispersed.  That's why
we've assigned staff, aside from Jan, to keep working on Slony-I. 
That's _also_ why we wanted, from the very beginning, to build a
community around Slony: we knew that we couldn't do it ourselves, if
only because our perspective is bound to be limited by our
experiences, and we wanted to make sure that we wouldn't build in
such limitations.  I think we've been pretty successful in that
attempt, and I'm delighted that we've managed to attract many
really smart people to the project.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

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

Re: [GENERAL] Problem with query plan

[Cott Lang]

shared_buffers = 16384
sort_mem = 8192
random_page_cost = 2
effective_cache_size = 3932160


On Fri, 2004-10-22 at 13:32, Tom Lane wrote:
> Cott Lang <[EMAIL PROTECTED]> writes:
> > Oops, sorry - guess I left that out - 7.4.5. :)
> 
> Hmm ... I can't duplicate any misbehavior here.  Are you using
> nondefault values for any planner parameters?  (particularly sort_mem,
> random_page_cost, effective_cache_size)
> 
>   regards, tom lane


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

Re: [GENERAL] Problem with query plan

[Tom Lane]

Cott Lang <[EMAIL PROTECTED]> writes:
> sort_mem = 8192
> random_page_cost = 2
> effective_cache_size = 3932160

effective_cache_size 30Gb ?  Seems a tad high ;-)

However, I set up a dummy test case on 7.4.5 and don't see any overflow.

regression=# create table z1(f1 char(1253));
CREATE TABLE
regression=# update pg_class set reltuples=2023865, relpages=65000 where relname = 
'z1';
UPDATE 1
regression=# set sort_mem = 8192;
SET
regression=# set random_page_cost = 2;
SET
regression=# set effective_cache_size = 3932160;
SET
regression=# explain select * from z1 order by f1;
 QUERY PLAN
-
 Sort  (cost=2200533.17..2205592.83 rows=2023865 width=1257)
   Sort Key: f1
   ->  Seq Scan on z1  (cost=0.00..85238.65 rows=2023865 width=1257)
(3 rows)

Can you try this exact test case and see if you get a NAN?

regards, tom lane

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

[GENERAL] Slony-I 1.0.5 Released

[Chris Browne]

The Slony-I team  is happy to present the 1.0.5
release.

The release tarball is available for download 
http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.5.tar.gz

See the HISTORY-1.0 file for a detailed list of changes.
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.

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

Re: [GENERAL] Slony-I 1.0.4 Released

[Gaetano Mendola]

Christopher Browne wrote:
> Oops! Gaetano Mendola <[EMAIL PROTECTED]> was seen spray-painting on a wall:
>
>>Chris Browne wrote:
>> > The Slony-I team is proud to present the 1.0.4 release of the most
>> > advanced replication solution for the most advanced Open Source
>> > Database in the world.
>> >
>> > The release tarball is available for download
>> >http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar.gz
>>
>>May I use it on a machine with a postgres 7.4.5 installed with RPM ?
>
>
> Probably only with some degree of trickery.
I suspected it, I currently can not use it because of this. Any chance to
have a slony rpm compatible with the 7.4.5 rpm ?
Regards
Gaetano Mendola

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

Re: [GENERAL] Two questions from the boss (SQL:2003 && scalability)

[Kevin Barnard]

On Fri, 22 Oct 2004 14:18:52 -0400, Jan Wieck <[EMAIL PROTECTED]> wrote:
> 
> 
> That "multiple hosts" sounds that he came across the NDB cluster stuff
> that will become available in MySQL someday. Be aware that this new
> table handler will to my knowledge NOT support foreign keys. So the
> enforcement of referential integrity is back to the application, now in
> a multimaster cluster. I don't think that's a good idea, nor do I think
> it will be easier to add this later instead of doing it right in the
> initial design phase, but my way of solving problems is not the way
> MySQL plans their features.
> 

This is the major difference in philosphies between open source
projects that are controlled by a company whose profit depends on
sales of the product (MySQL AB) and a project that is feature funded
by companines that actually need the features they are
funding(PostgreSQL)

So of course the are "selling" a feature regardless of it's need to be
functional.

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

Re: [GENERAL] unexpected field count in D message

[Tom Lane]

"Hicham G. Elmongui" <[EMAIL PROTECTED]> writes:
> what does this message mean?

libpq is unhappy because the DataRow ('D') message had a field count
different from the preceding RowDescription ('T') message.  How did you
manage that?

regards, tom lane

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

[GENERAL] ia64 hostname lookup problem

[Ed L.]

I've installed both 32-bit and 64-bit executables of 7.4.5 on ia64 running 
HP-UX B.11.23.  The 32-bit version works fine and accepts both local and 
remote connections.  The 64-bit version, however, rejects any/all attempts 
to connect with the following message:

FATAL:  no pg_hba.conf entry for host "???", user "dba", database 
"template1"

These are using identical pg_hba.conf files.  Any clues?

Ed


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

   http://archives.postgresql.org

Re: [GENERAL] ia64 hostname lookup problem

[Tom Lane]

"Ed L." <[EMAIL PROTECTED]> writes:
> I've installed both 32-bit and 64-bit executables of 7.4.5 on ia64 running 
> HP-UX B.11.23.  The 32-bit version works fine and accepts both local and 
> remote connections.  The 64-bit version, however, rejects any/all attempts 
> to connect with the following message:
> FATAL:  no pg_hba.conf entry for host "???", user "dba", database 
> "template1"

It worked for me last time I tested on HP's testdrive machines.  How did
you build the 64-bit version *exactly* --- what configure options, what
compiler version, etc?

regards, tom lane

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

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

Re: [GENERAL] correct example of a functional index usage?

[Gaetano Mendola]

Tom Lane wrote:
Since we don't yet have bitmap combining of indexes...
 ^^^
Are you trying to tell us something ? :-)
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] index not used?

[Gaetano Mendola]

Scott Marlowe wrote:
On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote:
I'm trying to access a table with about 120M rows. It's a vertical version
of a table with 360 or so columns. The new columns are: original item col,
original item row, and the value.
I created an index:
CREATE INDEX idx on table (col, row)
however, selects are still very slow. It seems it still needs a sequential
scan:
EXPLAIN SELECT * FROM table WHERE col=1 AND row=10;
 QUERY PLAN  
--
Seq Scan on table  (cost=1.00..102612533.00 rows=1 width=14)
  Filter: ((col = 1) AND ("row" = 10))

What am I doing wrong?

What type are row and col?  If they're bigint (i.e. not int / int4) then
you might need to quote the value to get the query to use an index:
SELECT * FROM table WHERE col='1' AND row='10';
also, have you vacuumed / analyzed the table?  I'm assuming yes.
I assume not, seen that cost...
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Problem with query plan

[Cott Lang]

On Fri, 2004-10-22 at 14:19, Tom Lane wrote:
> Cott Lang <[EMAIL PROTECTED]> writes:
> > sort_mem = 8192
> > random_page_cost = 2
> > effective_cache_size = 3932160
> 
> effective_cache_size 30Gb ?  Seems a tad high ;-)

It's a 32GB machine with nothing else running on it except PG, buffers
hover around 31GB :)

> However, I set up a dummy test case on 7.4.5 and don't see any overflow.
> Can you try this exact test case and see if you get a NAN?

I don't. After a bounce, I also can't repeat my original case; it now
returns 16.60. 

Fiddling with the above values, only setting sort_mem absurdly large
easily causes NAN.  

My guess is there was a wonky setting for sort_mem that disappeared
after I bounced. 


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

[GENERAL] combining two queries?

[Mark Harrison]

How can I combine these two queries?
# select viewerid,count(*) from viewer_movies group by viewerid order by viewerid;
 viewerid  | count
--+
 22964835 |   3055
 22964836 |   1291
 22964837 |   3105
 22964838 |199
planb=# select name from xenons where id = 23500637;
  name
-
 x.moray
I would like to end up with a query result like this:
 viewer   | count
--+
 x.surf   |   3055
 x.dream  |   1291
 x.moray  |   3105
 x.sleepy |199
Many TIA!
Mark
--
Mark Harrison
Pixar Animation Studios
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Problem with query plan

[Tom Lane]

Cott Lang <[EMAIL PROTECTED]> writes:
> Fiddling with the above values, only setting sort_mem absurdly large
> easily causes NAN.  

Ah.  I see an overflow case for sort_mem exceeding 1Gb; that's probably
what you tickled.

I've fixed this in HEAD, but it doesn't seem worth back-patching.
If you care, the change in HEAD is

*** src/backend/optimizer/path/costsize.c.orig  Sun Aug 29 01:06:43 2004
--- src/backend/optimizer/path/costsize.c   Fri Oct 22 20:02:39 2004
***
*** 566,572 
if (nbytes > work_mem_bytes)
{
double  npages = ceil(nbytes / BLCKSZ);
!   double  nruns = nbytes / (work_mem_bytes * 2);
double  log_runs = ceil(LOG6(nruns));
double  npageaccesses;
  
--- 566,572 
if (nbytes > work_mem_bytes)
{
double  npages = ceil(nbytes / BLCKSZ);
!   double  nruns = (nbytes / work_mem_bytes) * 0.5;
double  log_runs = ceil(LOG6(nruns));
double  npageaccesses;
  

but the variable names have changed since 7.4 so this won't apply
cleanly.

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: [GENERAL] combining two queries?

[Duane Lee - EGOVX]

Title: RE: [GENERAL] combining two queries?





Try


select a.name,count(*) from
xenons as a,
viewer_movies as b
where a.id = b.viewerid 
group by a.name order by a.name;


-Original Message-
From: Mark Harrison [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 22, 2004 4:55 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] combining two queries?


How can I combine these two queries?


# select viewerid,count(*) from viewer_movies group by viewerid order by viewerid;
  viewerid  | count
--+
  22964835 |   3055
  22964836 |   1291
  22964837 |   3105
  22964838 |    199



planb=# select name from xenons where id = 23500637;
   name
-
  x.moray


I would like to end up with a query result like this:


  viewer   | count
--+
  x.surf   |   3055
  x.dream  |   1291
  x.moray  |   3105
  x.sleepy |    199


Many TIA!
Mark


--
Mark Harrison
Pixar Animation Studios


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

Re: [GENERAL] ia64 hostname lookup problem

[Ed L.]

On Friday October 22 2004 5:11, Tom Lane wrote:
> "Ed L." <[EMAIL PROTECTED]> writes:
> > I've installed both 32-bit and 64-bit executables of 7.4.5 on ia64
> > running HP-UX B.11.23.  The 32-bit version works fine and accepts both
> > local and remote connections.  The 64-bit version, however, rejects
> > any/all attempts to connect with the following message:
> > FATAL:  no pg_hba.conf entry for host "???", user "dba", database
> > "template1"
>
> It worked for me last time I tested on HP's testdrive machines.  How did
> you build the 64-bit version *exactly* --- what configure options, what
> compiler version, etc?

I'd misapplied a patch.  It works now.  Here's exactly what I did:

1)  export CC=gcc CFLAGS="-O2 -mlp64" LDFLAGS=-mlp64

2)  Applied attached patch.

3)  ./configure --without-readline --without-zlib \
--prefix=/opt/pgsql/postgresql-7.4.5-64bit

*** postgresql-7.4.5/src/Makefile.shlib	Sun Oct 19 21:34:33 2003
--- postgresql-7.4.5-64bit/src/Makefile.shlib	Fri Oct 22 16:01:49 2004
***
*** 128,133 
  ifeq ($(PORTNAME), hpux)
!   shlib			:= lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION)
!   LINK.shared		= $(LD) +h $(soname) -b +b $(libdir)
!   ifeq ($(GCC), yes)
! SHLIB_LINK		+= `$(CC) -print-libgcc-file-name`
endif
--- 128,135 
  ifeq ($(PORTNAME), hpux)
! shlib			:= lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION)
! ifeq ($(GCC), yes)
!   LINK.shared		= $(CC) $(LDFLAGS) -shared -Wl,+h -Wl,$(soname) -Wl,+b -Wl,$(libdir)
!   SHLIB_LINK		+= `$(CC) $(LDFLAGS) -print-libgcc-file-name`
! else
!   LINK.shared		= $(LD) +h $(soname) -b +b $(libdir)
endif
*** postgresql-7.4.5/src/include/storage/s_lock.h	Tue Nov  4 04:43:56 2003
--- postgresql-7.4.5-64bit/src/include/storage/s_lock.h	Fri Oct 22 20:03:51 2004
***
*** 464,465 
--- 464,478 
  
+ #if defined(__hpux) && defined(__ia64)
+ #if !defined(__GNUC__)
+ 
+ #define HAS_TEST_AND_SET
+ 
+ typedef unsigned int slock_t;
+ 
+ #include 
+ #define TAS(lock) _Asm_xchg(_SZ_W, lock, 1, _LDHINT_NONE)
+ 
+ #endif/* !defined(__GNUC__) */
+ #endif
+ 
  #if defined(__QNX__) && defined(__WATCOMC__)
*** postgresql-7.4.5/src/makefiles/Makefile.hpux	Thu May 29 14:08:42 2003
--- postgresql-7.4.5-64bit/src/makefiles/Makefile.hpux	Fri Oct 22 20:06:06 2004
***
*** 13,14 
--- 13,20 
  
+ # Using X/Open Networking Interfaces requires to link with libxnet.
+ # Without specifying this, bind(), getpeername() and so on don't work
+ # correctly in the LP64 data model.
+ LIBS := -lxnet $(LIBS)
+ 
+ 
  # Embed 'libdir' as the shared library search path so that the executables
***
*** 30,32 
--- 36,42 
  
+ ifeq ($(host_cpu), ia64)
+ DLSUFFIX = .so
+ else
  DLSUFFIX = .sl
+ endif
  ifeq ($(GCC), yes)
***
*** 39,42 
  # shlib ... should go away, since this is not really enough knowledge)
! %.sl: %.o
  	$(LD) -b -o $@ $<
  
--- 49,56 
  # shlib ... should go away, since this is not really enough knowledge)
! %$(DLSUFFIX): %.o
! ifeq ($(GCC), yes)
! 	$(CC) $(LDFLAGS) -shared -o $@ $<
! else
  	$(LD) -b -o $@ $<
+ endif
  

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

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

Re: [GENERAL] Slony-I 1.0.4 Released

[Christopher Browne]

After takin a swig o' Arrakan spice grog, Gaetano Mendola <[EMAIL PROTECTED]> belched 
out:
> Christopher Browne wrote:
>  > Oops! Gaetano Mendola <[EMAIL PROTECTED]> was seen spray-painting on a wall:
>  >
>  >>Chris Browne wrote:
>  >> > The Slony-I team is proud to present the 1.0.4 release of the most
>  >> > advanced replication solution for the most advanced Open Source
>  >> > Database in the world.
>  >> >
>  >> > The release tarball is available for download
>  >> >http://developer.postgresql.org/~wieck/slony1/download/slony1-1.0.4.tar.gz
>  >>
>  >>May I use it on a machine with a postgres 7.4.5 installed with RPM ?

>  > Probably only with some degree of trickery.

> I suspected it, I currently can not use it because of this. Any
> chance to have a slony rpm compatible with the 7.4.5 rpm ?

If someone contributes RPM packages, using the same GCC and the same
RPM source RPM, then presumably it's possible.

There's a BSD port, so it has proven of enough interest to attract
contributions there.  There's not yet a Debian package or an RPM for
one or another of the RPM-based distributions; hopefully interested
parties will find opportunity to build such.

I can appreciate the desire to have some pre-packaged components; I
generally despise the idea of compiling Perl stuff myself as that
tends to turn into a system management nightmare.  

It may be that packages will emerge in six months; we'll see.  If you
need RHAT RPM packages badly enough, that may be reason to pay someone
to build them.
-- 
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
Y2K conversion simplified: Januark, Februark, March, April, Mak, June,
Julk, August, September, October, November, December.

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