[GENERAL] Tsearch2 install on postgres 8.2 NOTICE messages

[Henrik Zagerholm]

Hello list,

I'm trying to install tsearch2 on a freshly created database in 8.2  
but I get these NOTICES when doing so.

Is this something to be concerned about?

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"pg_ts_dict_pkey" for table "pg_ts_dict"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"pg_ts_parser_pkey" for table "pg_ts_parser"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"pg_ts_cfg_pkey" for table "pg_ts_cfg"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"pg_ts_cfgmap_pkey" for table "pg_ts_cfgmap"

NOTICE:  type "tsvector" is not yet defined
DETAIL:  Creating a shell type definition.
NOTICE:  argument type tsvector is only a shell
NOTICE:  type "tsquery" is not yet defined
DETAIL:  Creating a shell type definition.
NOTICE:  argument type tsquery is only a shell
NOTICE:  type "gtsvector" is not yet defined
DETAIL:  Creating a shell type definition.
NOTICE:  argument type gtsvector is only a shell
NOTICE:  type "gtsq" is not yet defined
DETAIL:  Creating a shell type definition.
NOTICE:  argument type gtsq is only a shell

psql (PostgreSQL) 8.2.0
Linux santa 2.6.8-2-386 #1 Fri Jul 29 18:01:33 CEST 2005 i686 GNU/Linux

Regards,
Henrik

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

  http://archives.postgresql.org/

Re: [GENERAL] fedora core 6 startup script for pg 8.2

[Gene]

Thanks for the suggestion, this is my first experience with SELinux. I
disabled selinux postgresql daemon protection, and then had to
re"initdb" and then it worked! Greatly appreciated!

On 12/18/06, Richard Huxton  wrote:

Gene wrote:
> I'm having some trouble installing postgresql 8.2 on fedora core 6.

Check your logs - something *must* be recorded in /var/log. The first
thing I'd guess is it's something to do with selinux though.

--
   Richard Huxton
   Archonet Ltd

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




--
Gene Hart
cell: 443-604-2679

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

Re: [GENERAL] determining which table to lookup depending on data

[Richard Huxton]

Steve Castellotti wrote:

SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);


You can try something like:

SELECT name FROM audio JOIN media ON id
WHERE table_name='audio' AND media_id=1
UNION ALL
SELECT name FROM video JOIN media ON id
WHERE table_name='video' AND media_id=1
;

Assuming you've got indexes on (id) on both tables that should be 
reasonably quick.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] How non-superuser can restore database containing procedures

[Richard Huxton]

Andrus wrote:

How to allow non-superusres to create database with language ?


You can't. If they can install a language-handler, they can install code 
that can do anything, including take control of the server and the unix 
account it runs as.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] fedora core 6 startup script for pg 8.2

[Richard Huxton]

Gene wrote:

I'm having some trouble installing postgresql 8.2 on fedora core 6.


Check your logs - something *must* be recorded in /var/log. The first 
thing I'd guess is it's something to do with selinux though.


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] fedora core 6 startup script for pg 8.2

[Gene]

I'm having some trouble installing postgresql 8.2 on fedora core 6.
This is my first time running fedora, I used to not have any problems
getting postgresql 8.1 started under gentoo. The RPMs are all
installed, it installed the init.d script and can su - postgres and do
a pg_ctl start successfully. I can't figure out why it won't start on
bootup. What am I missing?? Any help would be appreciated. I tried
starting it in "Service Configuration" to no success also. Thanks

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

Re: [GENERAL] out of memory woes

[Tom Lane]

"Angva" <[EMAIL PROTECTED]> writes:
> Here is the sole plpgsql function that was called when the error
> occurred. This function is intended to be called from a shell script in
> order to cluster tables in parallel processes.

OK, I played around with this for a bit, and what I find is that in 8.1,
that SPIExec context is where the sort operation run by CLUSTER's
reindexing step allocates memory.  So the short answer is "you've got
maintenance_work_mem set too high".  I don't know why it sometimes fails
and sometimes not --- maybe you are reaching the swap-space limits of
the machine when you do several of these in parallel?  But there doesn't
seem to be any actual leak here.

BTW, it's also the case that the 8.1 sort code isn't great about
measuring its space usage.  I had maintenance_work_mem set to 100MB
and saw actual process size exceeding 200MB ... IIRC, that's been
improved in 8.2.

regards, tom lane

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

[GENERAL] Creating an Application

[Bob Pawley]

Hi

I want to combine a PostgreSQL project with my host interface software to make 
an installable, c/w the PostgreSQL server.

Can someone please point to the document that explains how this is 
accomplished??

I am using Delphi's version of InstallShield on Win XP.

Bob Pawley

Re: [GENERAL] out of memory woes

[Angva]

Tom,

Here is the sole plpgsql function that was called when the error
occurred. This function is intended to be called from a shell script in
order to cluster tables in parallel processes. One calls it with
from_perc and to_perc - the % of statements that are run (e.g. 0% to
14%). (This concept may seem a bit silly with only 7 statements, but
this a convention I use for other functions too, such as creating
indexes - there are many indexes.) I call this function from my shell
script such that only one cluster statement is run at a time, for each
of 7 different processes.

Interesting that the leak is actually in the raise. Could this possibly
be related to the exception handling memory leak I read about?  When
searching this newsgroup I found a post of yours about this leak, but
decided it probably is not the issue - I believe I read that the memory
leak is local to a transaction.

Thanks,
Mark

create or replace function cluster_load_tables(from_perc integer,
to_perc integer) returns void as
  $$
declare
  cmdArr text[7];
  max_val integer;
  enabled boolean;
begin
  raise notice 'cluster_load_tables called %', timeofday();


  select cluster_load_tables into enabled from
secmaster_stage.data_load_config;

  if enabled = false then
raise notice 'cluster_load_tables disabled - exiting out %',
timeofday();
return;
  end if;

  cmdArr[0] := 'CLUSTER sm_issue';
  cmdArr[1] := 'CLUSTER sm_mbs_pool_detail';
  cmdArr[2] := 'CLUSTER sm_mbs_quartile_distribution';
  cmdArr[3] := 'CLUSTER sm_mbs_loan_distribution';
  cmdArr[4] := 'CLUSTER sm_mbs_geo_pool_distribution';
  cmdArr[5] := 'CLUSTER sm_issue_id';
  cmdArr[6] := 'CLUSTER sm_pool_prefix';

  max_val := 6;

  for i in ceiling(from_perc*(max_val/100::numeric(20,1))) ..
floor(to_perc*(max_val/100::numeric(20,1))) loop
  --for i in 0 .. 6 loop
begin
  execute cmdArr[i];
exception
  when others then
raise notice 'failed to execute %; error is: %', cmdArr[i],
sqlerrm;
end;
  end loop;

  /*
[snip - old commented-out code]
  */

  raise notice 'cluster_load_tables done %', timeofday();
end;
  $$
language plpgsql;


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

   http://archives.postgresql.org/

[GENERAL] How non-superuser can restore database containing procedures

[Andrus]

I need to allow non-superusers to create and restore databases containing
plpgsql language procedures.
template0 and template1 do not have any languages installed.

During restore they receive error:

pg_restore: [archiver (db)] could not execute query: ERROR:  must be
superuser to create procedural language
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

How to allow non-superusres to create database with language ?

Andrus. 



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

[GENERAL] determining which table to lookup depending on data values

[Steve Castellotti]

Hello all-

I'm working with a poorly-designed schema and need to do a lookup in
one table who's name I have to pull from a second table. I'm wondering
if its possible to do something like this in PostgreSQL:


Say I have three tables:

CREATE TABLE audio (id int4, name varchar(32));
CREATE TABLE video (id int4, name varchar(32));
CREATE TABLE media (id int4, table_name varchar(32), table_id int4);

with data:

INSERT INTO audio VALUES (0, 'file.wav');
INSERT INTO video VALUES (0, 'file.avi');
INSERT INTO media VALUES (0, 'audio', 0);
INSERT INTO media VALUES (1, 'video', 0);


Is there any way, especially in a single statement, where I can get
the name of a few if I only have the media table's id?

SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);

Of course I get back an error about needing to give my subquery an
alias, but even if I do so I only get the output from the subquery,
instead of being able to tell PostgreSQL to use that output as the name
of the table it should use to look up "name"

The original design was meant for a multimedia play which could use
a mixture of audio and video in a playlist, and that which housed all of
the entries for the playlist could just refer to an id for the media
table. When the software plays it has to pull this information out and
do a single lookup for each item. This looses referential integrity and
means you can't get from a play down to a specific file with one query.


Thanks in advance!

Steve Castellotti

Re: [GENERAL] Performance of outer joins?

[macgillivary]

ben would something like this work in your situation?

SELECT customer.id, customer.name, deliveries.calendar_day,
deliveries.delivered
FROM ben_customers as customer, ben_deliveries as deliveries

WHERE customer.id = deliveries.customers_id
and deliveries.calendar_day in (Select day
from ben_calendar
where day < 20061201 and day >= 20060101)

I think that would cut down the deliveries table fairly quickly, thus
making the customer_id join nice and quick.  You would also only be
returning data from the tables in your from clause.  I suppose it
depends on what you are trying to display.  If you want a list of all
the possible days, and deliveries on those days then I'd approach it a
bit differently.


On Dec 15, 7:59 pm, [EMAIL PROTECTED] (Benjamin Smith) wrote:

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


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

Re: [GENERAL] PDOStatement:closeCursor

[Hannes Dorbath]

It's always good to close your cursors once you don't need them anymore, 
but PostgreSQL doesn't force you to or blocks if you don't.


I really wonder why people use senseless things like PDO. Ah yes.. it's 
all about design patterns, right. Let's write a wrapper for the sole 
purpose of having written a wrapper. Sounds like a great pattern.


Yonatan Ben-Nes wrote:
I know that it's also related to PHP but sadly no one knew anything 
there so

I try here... :)

At the PHP manual of
PDOStatement::closeCursorit's 


written that "This method is useful for database drivers that do not
support executing a PDOStatement object when a previously executed
PDOStatement object still has unfetched rows. If your database driver
suffers from this limitation, the problem may manifest itself in an
out-of-sequence error.".
Anyone know if the PostgreSQL driver suffer from this problem or not?



--
Best regards,
Hannes Dorbath

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

Re: [GENERAL] Functions on tables

[Brendan Jurd]

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

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


I've been looking into SQL2003, and there are indeed some features
there that correspond to what I want.

ISO/IEC 9075-2:2003 - Foundation (SQL/Foundation) talks about
"structured types" (like a user-defined composite type in Postgres,
and somewhat like a "struct" in C).  It applies many OO concepts to
these structured types: inheritance, encapsulation, overloading.  It
goes so far as to say that every structured type has an implied
constructor method, and for every attribute within the type, one
implied "observer" method and one implied "mutator" method.

The standard provides for adding user-defined methods to these types,
which have an implied first parameter "SELF", which is exactly the
sort of thing I am looking for.  The standard doesn't specifically
mention doing this with tables, or row types.  However, the conceptual
distinction between a user-defined composite type and a table is not
vast, and AIUI Postgres already implies a composite type for every
table, with each tuple in the table being an object of that composite
type.

Frankly I don't have much experience reading SQL standards, and the
language they use is a bit abstruse.  But as far as I can tell, my
suggestion is quite nicely compliant with the behaviour the standard
recommends ... indeed the standard takes the OO idea much further than
I initially hoped to.

Alternatively, the standard also specifies "generated columns" within
a table, which would allow you to achieve a similar effect to my
person.name() method like so:

CREATE TABLE person (
id serial PRIMARY KEY,
firstname text NOT NULL,
lastname text NOT NULL,
name GENERATED ALWAYS AS (firstname || ' ' || lastname)
);

I think the generated column idea has some usefulness, but isn't quite
what I'm after.  You can't use it with additional parameters (c.f. my
earlier birthday(int) example), and I like the idea of keeping
attributes and methods totally separate.  A generated column is made
to appear like an attribute, and it is referenced like an attribute,
but in actual fact it is not an attribute, it is a derived value.

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

[GENERAL] PDOStatement:closeCursor

[Yonatan Ben-Nes]

Hi all,

I know that it's also related to PHP but sadly no one knew anything there so
I try here... :)

At the PHP manual of
PDOStatement::closeCursorit's
written that "This method is useful for database drivers that do not
support executing a PDOStatement object when a previously executed
PDOStatement object still has unfetched rows. If your database driver
suffers from this limitation, the problem may manifest itself in an
out-of-sequence error.".
Anyone know if the PostgreSQL driver suffer from this problem or not?

Thanks a lot in advance,
Ben-Nes Yonatan