Re: [GENERAL] Text Search Configuration Problem

[Oleg Bartunov]

Kevin,

it looks like you use UTF-8, so the problem in .aff file, which contains
cyrillic comments :) I converted files into UTF-8 encoding using iconv.


Oleg

On Thu, 3 Apr 2008, Kevin Reynolds wrote:


I'm using Postgresql version 8.3.1 on CentOS 5 and am following the steps in 
section 12.7 of the documentation for creating a custom text search 
configuration.

 When I get to the step that says:

 CREATE TEXT SEARCH DICTIONARY english_ispell (
   TEMPLATE = ispell,
   DictFile = english,
   AffFile = english,
   StopWords = english
);

 I get the following error:

 ERROR:  invalid byte sequence for encoding "UTF8": 0xe0c020
HINT:  This error can also happen if the byte sequence does not match the encoding 
expected by the server, which is controlled by "client_encoding".

 I'm using the english ispell files from 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/

 Does anyone know how to solve this?


-
You rock. That's why Blockbuster's offering you one month of Blockbuster Total 
Access, No Cost.


Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [GENERAL] Limiting postgresql resources

[Tom Lane]

"Joey K." <[EMAIL PROTECTED]> writes:
> So, is it possible to kill a query thats running for x minutes with 50%+ CPU
> load and using y MB of memory?

There's statement_timeout ...

regards, tom lane

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

Re: [GENERAL] Exception messages -> application?

[Frank Miles]

On Fri, Apr 04, 2008 at 12:02:20PM -0700, Frank Miles wrote:


I have a moderately DB-ignorant question: is there a "built-in" way for an
application to receive the message emitted by a RAISE  in a PgSQL function?

Context: I have a moderately complex application (in python, using psycopg2)


psycopg2 does a pretty good job of converting any PostgreSQL
side exceptions into Python exceptions which are then
getting raised in your Python code. Unless you explicitely
silence/catch PG exceptions inside your plpgsql code you
should be seeing them turn up in Python.

There's a bunch of arguments on the Python exception raised.

try:
...
except TheExceptionType, e:
print dir(e)

might help with that.

Also, you may need to fiddle with the PG client logging
settings (in postgresql.conf) which control what level of
detail of error information is getting sent to the client in
the first place.

BTW, are you talking about RAISE EXCEPTION or RAISE NOTICE ?

Karsten


Thanks, Karsten.  At some point in the forgotten past, I had set up
a DB-handling python class which (among many other things) catches
psycopg exceptions - your guess there was right on the mark.  I should
be able to alter that code to fix this problem.

Unfortunately this does not help for lesser events (i.e. NOTICE and WARNING).
My preliminary effort suggests that psycopg isn't passing these.  I'll
take a further look at the client logging settings - will these be
sufficient?

Thanks again!

-f

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

Re: [GENERAL] Cannot Install PostgreSQL on Windows 2000 Server

[Dee]

Not with 8.3.  

I finally did get a few earlier versions to install. I am not very familiar 
with windows 2000 administration, so it took some doing ;) Here is what I did
http://archives.postgresql.org/pgsql-general/2008-03/msg00904.php

At that point I stopped looking into version 8.3. Since I was not having any 
luck with it and received no responses.  I would still like to know how to 
force it install though ... 

Dee

Ron Tyndall <[EMAIL PROTECTED]> wrote:   MessageNo  luck here and can and 
no one responded to my post. It is a mystery. did you have  any luck?
  
 thx
  
  
 Ron Tyndall
IT Tools Sr. Systems  Analyst
Nortel
[EMAIL PROTECTED]
Office 919-905-2793  ESN 355 2793 


   -Original Message-
From: Dee[mailto:[EMAIL PROTECTED] 
Sent: Monday, March 17, 200810:00 AM
To: pgsql-general@postgresql.org
Subject: RE:Cannot Install PostgreSQL on Windows 2000 Server


Were youever able to install PostgreSQL on windows 2000? I am having 
similar problemson 2000 Pro and have absolutely zero luck with it.

Dee
  

-
   Looking for last minute shopping deals? Findthem fast with Yahoo! Search.

   
-
You rock. That's why Blockbuster's offering you one month of Blockbuster Total 
Access, No Cost.

Re: [GENERAL] Numbering rows by date

[brian]

Andrus wrote:

I have table

create Document ( docdate date, docorder integer )

I need update docorder column with numbers 1,2 in docdate date order
Something like

i = 1;
UPDATE Document SET docorder = i++
  ORDER BY docdate;


How to do this is PostgreSQL 8.2 ?



ALTER TABLE DROP COLUMN docorder;
SELECT docdate FROM document ORDER BY docdate ASC;

Or, if you really need the numbering and can't do it in application code 
... my first thought was to do this:


CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') FROM document
ORDER BY docdate ASC;

But the ordering will occur afterwards so the sequence will be out of 
order. I think you'd need a subquery, then:


CREATE TEMP SEQUENCE seq_doc_number;
SELECT docdate, nextval('seq_doc_number') AS docorder
FROM (SELECT docdate FROM document ORDER BY docdate ASC) dummy_alias;

b


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

[GENERAL] Limiting postgresql resources

[Joey K.]

Hello,
Is it possible to limit cpu/memory resources to queries on postgres
something akin to limits.conf on Linux?

We have a couple of databases (pg 8.2.7)  serving 500K - 1M records. For
various reasons, users are allowed to run queries against the database via a
web based query interface. These queries are mostly used to generate
reports.

Some times users write inefficient queries that brings the entire database
server (runs webserver as well) to it's knees affecting others users on the
system.

So, is it possible to kill a query thats running for x minutes with 50%+ CPU
load and using y MB of memory?

In a perfect world, we would address such issues in the application
design/database design and/or hardware. But our application is in an
imperfect world where such resources are unavailable.

Thanks in advance,
Joey

Re: [GENERAL] slow pgsql tables - need to vacuum?

[Douglas McNaught]

On Thu, Apr 3, 2008 at 2:34 PM, Dan99 <[EMAIL PROTECTED]> wrote:
> Hi,
>
>  I am having some troubles with a select group of tables in a database
>  which are acting unacceptably slow.  For example a table with
>  approximately < 10,000 rows took about 3,500ms to extract a single row
>  using the following select statement:
>
>  SELECT * FROM table WHERE column = 'value'
>
>  I have preformed this same test on a number of different tables, only
>  a few of which have this same problem.  The only common thing that I
>  can see between these affected tables is the fact that they are dumped
>  and re-populated every day from an outside source.

You need to ANALYZE the tables after you load them, and make sure you
have indexes on the column you're querying (which it sounds like you
do, but they're not being used because the statistics for the table
are inaccurate).  There may also be a lot of dead tuples which will
further slow down a sequential scan.

Do read up on VACUUM and MVCC in the docs--it's a very important
thing. You will suffer horribly unless you have a working periodic
VACUUM.

Also, are you using TRUNCATE TABLE to clear out before the reload, or
a mass DELETE?  The latter will leave a lot of dead rows, bloating the
table and slowing down scans.  TRUNCATE just deletes the table file
and recreates it empty.

-Doug

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

Re: [GENERAL] ERROR: XX000: cache lookup failed for relation

[Jan Wieck]

On 4/5/2008 11:02 AM, Glyn Astill wrote:

Hi Jan,

Is that still true for 1.2.12? As that's the version I'm using.. Also any ideas 
on where I start to sort it out? I just want to drop the old table now I've 
removed it from replication, but the error mentioned previously is stopping me.


Yes, this is and will be true for ALL 1.2 versions. And this is also the 
reason why Slony 2.0 will NOT be supporting any Postgres version prior 
to 8.3.


If you removed the table from the replication set, then this error 
should not appear any more. All I can think of is that the table might 
have foreign key relationships with other tables, which are still 
involved in replication. If that is the case, try dropping it by using 
the EXECUTE SCRIPT feature of the slonik command language to perform the 
DROP TABLE (which I think is highly recommended for any sort of DDL 
throughout the Slony documentation anyway).



Jan



Thanks


- Original Message 

From: Jan Wieck <[EMAIL PROTECTED]>
To: Glyn Astill <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
Sent: Saturday, 5 April, 2008 3:00:04 PM
Subject: Re: [Slony1-general] ERROR:  XX000: cache lookup failed for relation

On 4/5/2008 7:47 AM, Glyn Astill wrote:
> Hi chaps,
> 
> I know there's been a bit of "activity" on this listrecently - but does anyone 
know where I should start looking to resolvethe below?


Yes, a "SET DROP TABLE" is mandatory prior to dropping the table itself. 
This is because up to version 1.2.x, Slony is deliberately corrupting 
the system catalog on subscriber nodes in order to suppress triggers and 
rules to fire (this can only be controlled by other means since Postgres 
8.3 and will be done so in Slony 2.0).



Jan

> 
> - Original Message 
>> From: Glyn Astill 
>> To: [EMAIL PROTECTED]

>> Cc: pgsql-general@postgresql.org
>> Sent: Friday, 4 April, 2008 3:05:18 PM
>> Subject: [Slony1-general] ERROR:  XX000: cache lookup failed for relation
>> 
>> Hi chaps,
>> 
>> I've got a problem trying to drop a table, I get the error "cache lookup 
failed 
>> for relation"
>> 
>> SEE=# drop table replicated_users;

>> ERROR:  XX000: cache lookup failed for relation 30554884
>> LOCATION:  getRelationDescription, dependency.c:2021
>> Now this table is on a slony-I slave and was in replication when I tried to 
drop 
>> it - I presume this is a big mistake and I should never try to drop a table 
>> without first droping it from replication?
>> 
>> In addition I'd set up a trigger on the table "replicate_users".
>> 
>> If I do:
>> 
>>  select relname,oid from pg_class where relname = 'replicated_users';
>> 
>> -[ RECORD 1 ]-

>> relname | replicated_users
>> oid | 30554879
>> 
>> Thats not the same oid as the one it's complaining about.
>> 
>> Does anyone have any idea why this has happened or how I can fix it?
>> 
>> Cheers

>> Glyn
>> 
>> 
>> 
>> 
>> 
>> 
>>   ___ 
>> Yahoo! For Good helps you make a difference  
>> 
>> http://uk.promotions.yahoo.com/forgood/
>> 
>> ___

>> Slony1-general mailing list
>> [EMAIL PROTECTED]
>> http://lists.slony.info/mailman/listinfo/slony1-general
>> 
> 
> 
> 
> 
>   ___ 
> Yahoo! For Good helps you make a difference  
> 
> http://uk.promotions.yahoo.com/forgood/
> 
> ___

> Slony1-general mailing list
> [EMAIL PROTECTED]
> http://lists.slony.info/mailman/listinfo/slony1-general


--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin







  ___ 
Yahoo! For Good helps you make a difference  


http://uk.promotions.yahoo.com/forgood/




--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


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

Re: [GENERAL] Text Search Configuration Problem

[Tom Lane]

Kevin Reynolds <[EMAIL PROTECTED]> writes:
>   I get the following error:
   
>   ERROR:  invalid byte sequence for encoding "UTF8": 0xe0c020
> HINT:  This error can also happen if the byte sequence does not match the 
> encoding expected by the server, which is controlled by "client_encoding".
   
>   I'm using the english ispell files from 
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/

Are you sure those are in UTF8 encoding?

regards, tom lane

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

Re: [GENERAL] pg_dump / load seems really slow..

[Tom Lane]

Chad <[EMAIL PROTECTED]> writes:
> export:

> pg_dump forums --data-only --no-owner -t foo > 

> The load step seems really slow.

Don't use --data-only.  Or if you must, look into dropping and
recreating indexes and foreign keys.  See

http://www.postgresql.org/docs/8.3/static/populate.html

(or the corresponding page for whatever PG version you are using).

regards, tom lane

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

[GENERAL] drop database regardless of connections

[Kev]

Hi everyone,

I'd like to drop a database to which users have idle connections.  I'm
running pgsql 8.2 on win32, soon 8.3, using mod_perl2 and
connect_cached.

I know this issue has been brought up several times over the past few
years, but I don't quite understand the reason for it not being
supported as an sql command or command-line utility.

Over on pgsql.hackers Stuart Bishop posted this code:

 > CREATE OR REPLACE FUNCTION _killall_backends(text)
 > RETURNS Boolean AS $$
 > import os
 > from signal import SIGTERM
 >
 > plan = plpy.prepare(
 > "SELECT procpid FROM pg_stat_activity WHERE datname=$1",
['text']
 > )
 > success = True
 > for row in plpy.execute(plan, args):
 > try:
 > plpy.info("Killing %d" % row['procpid'])
 > os.kill(row['procpid'], SIGTERM)
 > except OSError:
 > success = False
 >
 > return success
 > $$ LANGUAGE plpythonu;

However, I'd like to avoid installing Python just for this task, if
possible.  So I tried to do this in Perl, but for some reason neither
kill() nor Win32::Process::KillProcess() actually terminate the
threads.  I also tried clearing CachedKids, but that doesn't seem to
have any effect.  So even with no client apps running, I am unable to
drop the database without restarting the whole database service.

Does this mean I have to run a separate database server for each
database just to be able to boot people off a particular database
before dropping it?  It seems like a basic thing to be able to drop a
database unconditionally without restarting the service for people who
are using other databases on the same service.  Maybe I'm missing
something that everyone else who asked about this eventually realized?

Thanks,
Kev

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

[GENERAL] Silent install 8.3 diiffers from 8.2

[Syra . Didelez]

Hello,

With the postgresql-8.2-int.msi, i used to do a succesful install with the 
following options


BASEDIR=C:\Postgres
DATADIR=D:\PostgresData
INTERNALLAUNCH=1 
DOSERVICE=1 
CREATESERVICEUSER=1 
SERVICEACCOUNT=postgres 
SERVICEDOMAIN=%COMPUTERNAME% 
SERVICEPASSWORD=123qwe-.
DOINITDB=1 ENCODING=UTF-8
 PERMITREMOTE=1 
SUPERUSER=DBA
SUPERPASSWORD=supersecret

With postgresql-8.3-int.msi I always run into the following security 
error:

"Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start.
Verify that you have sufficient privileges to start system services."

When pressing cancel, I should expect a cleanup, but the bin and lib 
directories in the basedir still remain.
This together with 6 postgres.exe processes 

I am using exactly the smae options as above, and I have removed the 
postgres user before starting the silent install.
With 8.2 I could do this over and over again, with 8.3 it seems not to 
work.


- Syra Didelez -
Apogee Portal Deployment Manager
Apogee Portal Hotfix Manager
Agfa-Gevaert N.V.

Tel.: +32 3 444 4237
http://www.agfa.com

[GENERAL] pg_dump / load seems really slow..

[Chad]

Howdy!

I'm trying to build some fixture (test) data for a webapp I'm building
for my company. To do this I've taken a dump of data from production,
exported it using pg_dump, and load it via psql  < 

The data that is exported from production is about 25K rows and 12MB.
The dump takes less than a second but the load is taking 2 minutes.
The load step seems really slow. I'm wondering if there are ways to
tweak the system to load faster. Or, if the process cannot be spead up
I guess I don't need all 25 rows.. Is there a way to export a query to
file via command line?

export:

pg_dump forums --data-only --no-owner -t foo > 

import:

psql forums < 

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

[GENERAL] Text Search Configuration Problem

[Kevin Reynolds]

I'm using Postgresql version 8.3.1 on CentOS 5 and am following the steps in 
section 12.7 of the documentation for creating a custom text search 
configuration.
   
  When I get to the step that says:
   
  CREATE TEXT SEARCH DICTIONARY english_ispell (
TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
   
  I get the following error:
   
  ERROR:  invalid byte sequence for encoding "UTF8": 0xe0c020
HINT:  This error can also happen if the byte sequence does not match the 
encoding expected by the server, which is controlled by "client_encoding".
   
  I'm using the english ispell files from 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
   
  Does anyone know how to solve this?

   
-
You rock. That's why Blockbuster's offering you one month of Blockbuster Total 
Access, No Cost.

[GENERAL] slow pgsql tables - need to vacuum?

[Dan99]

Hi,

I am having some troubles with a select group of tables in a database
which are acting unacceptably slow.  For example a table with
approximately < 10,000 rows took about 3,500ms to extract a single row
using the following select statement:

SELECT * FROM table WHERE column = 'value'

I have preformed this same test on a number of different tables, only
a few of which have this same problem.  The only common thing that I
can see between these affected tables is the fact that they are dumped
and re-populated every day from an outside source.  The temporary
solution that I have found is to copy all the data into another (brand
new) table using:

SELECT * INTO table2 FROM table

After dropping the old table and renaming the new table to reflect the
old table, I run the same tests as before and find that the table
responds much faster (approx. < 10ms).  My solution is only addressing
the symptoms of the problem and not the actual root cause.  So my
question to everyone is what is really happening?  Why are these
tables becoming so slow.  The idea that I am leaning towards is the
need to regularly vacuum these tables, which has never been done
before.  After switching from other database systems, I am
unaccustomed to the vacuuming concept.  Thus, if you believe I am
correct in my believe that this is causing all my problems, can you
please explain vacuuming to me.  I have attempted to run a simple
"VACUUM VERBOSE" command on the entire database, with little success
(it comes back saying something along the lines of the need to
increase the max_fsm_pages, which I am unfamiliar with).

Sorry for the long post, but any help with the above would be greatly
appreciated.

Thank you,
Daniel

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

Re: [GENERAL] To many records returned

[Dan99]

On Apr 3, 9:02 pm, [EMAIL PROTECTED] (Justin) wrote:
> Have a select statement with a where clause using datestamp with
> timezone column compared to a date
>
> Select * from sometable where DateStampColumn > '2008-03-31'
>
> this returns records that are equal 2008-03-31
>
> but when the query includes casting to date
> Select * from sometable where DateStampColumn::date > '2008-03-31'
> The result is correct
>
> What is causing the difference in the results
>
> My guess is '2008-03-31' is being cast into a datestamp value of
> 2008-03-31 00:00:00.00-00  which would explain why one query returns
> more records.
>
> --
> Sent via pgsql-general mailing list ([EMAIL PROTECTED])
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

I believe you are correct in your explanation of the problem.  The
solution is to use your second query or one such as this :

Select * from sometable where DateStampColumn >= '2008-04-01'

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

[GENERAL] Newby Help needed

[Andrea]

Hi all, i'm newby to postgresql: really great product.

I'm trying to understand exactly how to set up WAL  archiving.
I read the doc ( by the way i'm running postgresql-8.1.11 ): i need some 
explanation on how are created and recycled theWAL bacause i cannot 
understand, for example, how is filled-up the dir pg_xlog/archive-status.


I noticed that sometimes the same file is present in pg_xlog dir and 
also in archive-status dir with the extension .ready. Then, after some 
time ( i cannot understand how long, and wehre this parameter, if any, 
is written ) it disappear from only archive-status directory...


Some one can help in understanding these ??

Many thanks in advance to any one will post an answer..

--Andrea

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

Re: [GENERAL] Cannot Install PostgreSQL on Windows 2000 Server

[Ron Tyndall]

No luck here and can and no one responded to my post. It is a mystery.
did you have any luck?
 
thx
 
 

Ron Tyndall
IT Tools Sr. Systems Analyst
Nortel
[EMAIL PROTECTED]
Office 919-905-2793 ESN 355 2793 


-Original Message-
From: Dee [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 17, 2008 10:00 AM
To: pgsql-general@postgresql.org
Subject: RE: Cannot Install PostgreSQL on Windows 2000 Server


Were you ever able to install PostgreSQL on windows 2000? I am having
similar problems on 2000 Pro and have absolutely zero luck with it.

Dee




  _  

Looking for last minute shopping deals? Find
 them fast with Yahoo! Search.

[GENERAL] Numbering rows by date

[Andrus]

I have table

create Document ( docdate date, docorder integer )

I need update docorder column with numbers 1,2 in docdate date order
Something like

i = 1;
UPDATE Document SET docorder = i++
  ORDER BY docdate;


How to do this is PostgreSQL 8.2 ?

Andrus.



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

[GENERAL] Benetl 1.7, free ETL tool using postgreSQL is out !!!

[Benoît Carpentier]

Dear all,

Benetl, a free ETL tool for files using postgreSQL is out, in version 1.7 !!!

It is correcting a trouble with part formula, and brings six new
functionalities.
Benetl is now much more compliant with Linux (should be tested).

It is available at : www.benetl.net

Thanks for your interest.

Regards,

-- 
Benoît Carpentier
www.benetl.net
Founder of Benetl & Java Developer




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

Re: [GENERAL] Conversion to 8.3

[Terry Lee Tucker]

On Friday 04 April 2008 16:36, Tom Lane wrote:
> Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> > I am converting our application from 7.4.19 to 8.3.1. In the old scheme
> > of things, I was generating an interval between two timestamps and
> > evaluating the interval string in another set of trigger code. I was
> > doing the following:
> > IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
> > With the new casting rules, this doesn't work.
>
> Well, you could force it to work by casting new.ontime to text
> explicitly, but this is a pretty horrid way of testing for a negative
> interval anyhow.  I'd be inclined to do something like
>  new.ontime < '0 seconds'
>
> BTW, the IS NOT NULL test is redundant too, since the comparison
> can't succeed for a null.
>
>regards, tom lane

Thanks Tom. That's what I'm looking for.

-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

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

Re: [GENERAL] pg_dump and schemas

[Brent Wood]

Hi Rusty,

In which case can you not include the text around the schema & table creation 
commands to ensure other instances of "public" do not match the string?

I'm not sure exactly what the pg_dump output contains, but you could use 
something like:

cat pg_dump.sql | sed 's/CREATE SCHEMA "public"/CREATE SCHEMA "new_schema"/' | \
sed 's/CREATE TABLE "public"./CREATE TABLE "new_schema"./' > new_schema.sql

This should avoid any ambiguity as to which instances of "public" you want 
changed in the pg_dump sql file.

I think adding support for changing schema names in pg_dump would make it 
unnecessarily complex, as why just schemas? Also rename databases, tables, 
columns, index names, change comments... 

I've yet to find something like this I couldn't do with sed, & if there was 
there is always awk for the truly desparate :-)
pg_dump generates the dump, reliably, simply & safely. Any change you want from 
the original is not, IMHO, the role of a backup program. That should ONLY back 
up a replica of your data. Make changes afterwards if you like, but a backup 
program shouldn't modify your data. 

Just my 02c, & I ain't no Postgres developer, so I'm not speaking for them in 
this

Cheers,

  Brent Wood



Hi Brent,

It's not he best solution, because we could have fields containing  
"public" in their names and sed would happily change those to test1 as  
well.

I'm looking for a safer solution, thats why it should be a part of  
pg_dump.

Rusty

On Apr 5, 2008, at 12:41 AM, Brent Wood wrote:

> Hi Rusty,
>
> Try passing the output through a utility like sed, already there  
> under Linux , but versions that work under Windows are available  
> (eg, cygwin)
>
> eg, using a pipe:   pg_dump -d | sed 's/public/test1/g' > dump.sql

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

Re: [GENERAL] mac ports question

[Shane Ambler]

Stephan Szabo wrote:

On Sat, 5 Apr 2008, Tom Allison wrote:


If it doesn't remove the 8.2 then I guess I can migrate it.
But that requires that I still need to get 8.2 running.


Right now it complains that it can't find a listening socket at /tmp/...
(localhost mode).  And I can't find the configuration files in this set-up.

I do have this running:
/opt/local/bin/daemondo --label=postgresql82-server --start-cmd
/opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper
start ; --stop-c

But that doesn't actually mean anything to me other that I guess it's
trying to start.  And it's stuck somewhere.
And no logs.


/opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper 
should be your startup script (as in Apple's replacement for rc scripts) 
and should contain your settings (as in datadir) if not a config file in 
the dir with it or in /etc/rc.conf (I'm don't use macports so I'm not 
sure how it config's)


If not search for a file called postgresql.conf or PG_VERSION you should 
find several copies of PG_VERSION, one at the top of your data dir where 
you will also find your postgresql.conf and the log files or log dir 
with the log files depending on your conig. The other PG_VERSION files 
will be inside each folder that has your table data.


Permission settings will mean that root (or the pg user) will be the 
only ones able to see these files so you may need to find as root from 
the terminal.




I believe if it's getting far enough to actually try running PostgreSQL
the logs would be being put somewhere like /opt/local/var/log/postgresql82
(or at least the 83 port seems to put it in a postgresql83 from there)




--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] mac ports question

[Stephan Szabo]

On Sat, 5 Apr 2008, Tom Allison wrote:

> If it doesn't remove the 8.2 then I guess I can migrate it.
> But that requires that I still need to get 8.2 running.
>
>
> Right now it complains that it can't find a listening socket at /tmp/...
> (localhost mode).  And I can't find the configuration files in this set-up.
>
> I do have this running:
> /opt/local/bin/daemondo --label=postgresql82-server --start-cmd
> /opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper
> start ; --stop-c
>
> But that doesn't actually mean anything to me other that I guess it's
> trying to start.  And it's stuck somewhere.
> And no logs.

I believe if it's getting far enough to actually try running PostgreSQL
the logs would be being put somewhere like /opt/local/var/log/postgresql82
(or at least the 83 port seems to put it in a postgresql83 from there)

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

Re: [GENERAL] mac ports question

[Tom Allison]

If it doesn't remove the 8.2 then I guess I can migrate it.
But that requires that I still need to get 8.2 running.


Right now it complains that it can't find a listening socket at /tmp/... 
(localhost mode).  And I can't find the configuration files in this set-up.


I do have this running:
/opt/local/bin/daemondo --label=postgresql82-server --start-cmd 
/opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper 
start ; --stop-c


But that doesn't actually mean anything to me other that I guess it's 
trying to start.  And it's stuck somewhere.


And no logs.
And no config file to make the logs hyper verbose.

This stinks.  I can find all this stuff on my Debian boxes, but on this 
mac -- have no clue where things live.


Stephan Szabo wrote:

On Sat, 5 Apr 2008, Tom Allison wrote:


I ran into a problem today where somewhere my port of postgresql82 just
stopped working.  I'm largely an idiot on Mac because I use is as a
workstation/development box and do most of the real system related work
on my debian boxes.

But I don't know how to get the port working again.

Then I saw there is a new version 8.3 in port.

So, if I upgrade does anyone know if this cleanly removes version 8.2
from the box so I don't have to start carrying multiple versions?


It won't remove 8.2 automatically. You'd have to ask port to deactivate
and uninstall it. Unfortunately, you may find it complains about
dependencies when you do that.

Are you using the server as well, or just the client components? If the
server, AFAIK it also won't try to do any database migration, which
probably isn't a huge problem on a dev system, but could be.



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

Re: [GENERAL] mac ports question

[Stephan Szabo]

On Sat, 5 Apr 2008, Tom Allison wrote:

> I ran into a problem today where somewhere my port of postgresql82 just
> stopped working.  I'm largely an idiot on Mac because I use is as a
> workstation/development box and do most of the real system related work
> on my debian boxes.
>
> But I don't know how to get the port working again.
>
> Then I saw there is a new version 8.3 in port.
>
> So, if I upgrade does anyone know if this cleanly removes version 8.2
> from the box so I don't have to start carrying multiple versions?

It won't remove 8.2 automatically. You'd have to ask port to deactivate
and uninstall it. Unfortunately, you may find it complains about
dependencies when you do that.

Are you using the server as well, or just the client components? If the
server, AFAIK it also won't try to do any database migration, which
probably isn't a huge problem on a dev system, but could be.

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

Re: [GENERAL] database 1.2G, pg_dump 73M?!

[Anton Melser]

>  I am still trying to figure out if the database was getting any
>  automatic vacuuming at all.  The Postgres documentation (the database is
>  8.2, though I'm moving to 8.3 soon) sounds as if it's on automatically,
>  but the Debian-specific documentation suggests I may need to do some
>  additional things to enable it.

This is probably far too late but anyway. You do indeed have to enable
autovacuum with 8.2, as it isn't enabled by default, at least with
most distributions. 8.3 it's by default with most distributions.
# show autovacuum;
will tell you if it's on.
Cheers
Anton

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

Re: [GENERAL] Conversion to 8.3

[Tom Lane]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> what type is new.ontime ??   timestamp or interval.  I would expect it
> to be an interval.  But intervals are either negative or positive, not
> "ago" unless that's something peculiar to 7.4 that I've long since
> forgotten.

No, it's still around:

regression=# select '-1 day'::interval;
 interval 
--
 -1 days
(1 row)

regression=# set datestyle = postgres;
SET
regression=# select '-1 day'::interval;
  interval   
-
 @ 1 day ago
(1 row)

The most bletcherous aspect of Terry's original coding is that it fails
entirely, and silently, if the DateStyle setting isn't what it's
assuming...

regards, tom lane

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

Re: [GENERAL] ERROR: XX000: cache lookup failed for relation

[Glyn Astill]

Hi Jan,

Is that still true for 1.2.12? As that's the version I'm using.. Also any ideas 
on where I start to sort it out? I just want to drop the old table now I've 
removed it from replication, but the error mentioned previously is stopping me.

Thanks


- Original Message 
> From: Jan Wieck <[EMAIL PROTECTED]>
> To: Glyn Astill <[EMAIL PROTECTED]>
> Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
> Sent: Saturday, 5 April, 2008 3:00:04 PM
> Subject: Re: [Slony1-general] ERROR:  XX000: cache lookup failed for relation
> 
> On 4/5/2008 7:47 AM, Glyn Astill wrote:
> > Hi chaps,
> > 
> > I know there's been a bit of "activity" on this listrecently - but does 
> > anyone 
> know where I should start looking to resolvethe below?
> 
> Yes, a "SET DROP TABLE" is mandatory prior to dropping the table itself. 
> This is because up to version 1.2.x, Slony is deliberately corrupting 
> the system catalog on subscriber nodes in order to suppress triggers and 
> rules to fire (this can only be controlled by other means since Postgres 
> 8.3 and will be done so in Slony 2.0).
> 
> 
> Jan
> 
> > 
> > - Original Message 
> >> From: Glyn Astill 
> >> To: [EMAIL PROTECTED]
> >> Cc: pgsql-general@postgresql.org
> >> Sent: Friday, 4 April, 2008 3:05:18 PM
> >> Subject: [Slony1-general] ERROR:  XX000: cache lookup failed for relation
> >> 
> >> Hi chaps,
> >> 
> >> I've got a problem trying to drop a table, I get the error "cache lookup 
> failed 
> >> for relation"
> >> 
> >> SEE=# drop table replicated_users;
> >> ERROR:  XX000: cache lookup failed for relation 30554884
> >> LOCATION:  getRelationDescription, dependency.c:2021
> >> Now this table is on a slony-I slave and was in replication when I tried 
> >> to 
> drop 
> >> it - I presume this is a big mistake and I should never try to drop a 
> >> table 
> >> without first droping it from replication?
> >> 
> >> In addition I'd set up a trigger on the table "replicate_users".
> >> 
> >> If I do:
> >> 
> >>  select relname,oid from pg_class where relname = 'replicated_users';
> >> 
> >> -[ RECORD 1 ]-
> >> relname | replicated_users
> >> oid | 30554879
> >> 
> >> Thats not the same oid as the one it's complaining about.
> >> 
> >> Does anyone have any idea why this has happened or how I can fix it?
> >> 
> >> Cheers
> >> Glyn
> >> 
> >> 
> >> 
> >> 
> >> 
> >> 
> >>   ___ 
> >> Yahoo! For Good helps you make a difference  
> >> 
> >> http://uk.promotions.yahoo.com/forgood/
> >> 
> >> ___
> >> Slony1-general mailing list
> >> [EMAIL PROTECTED]
> >> http://lists.slony.info/mailman/listinfo/slony1-general
> >> 
> > 
> > 
> > 
> > 
> >   ___ 
> > Yahoo! For Good helps you make a difference  
> > 
> > http://uk.promotions.yahoo.com/forgood/
> > 
> > ___
> > Slony1-general mailing list
> > [EMAIL PROTECTED]
> > http://lists.slony.info/mailman/listinfo/slony1-general
> 
> 
> -- 
> Anyone who trades liberty for security deserves neither
> liberty nor security. -- Benjamin Franklin
> 
> 




  ___ 
Yahoo! For Good helps you make a difference  

http://uk.promotions.yahoo.com/forgood/


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

Re: [GENERAL] [Slony1-general] ERROR: XX000: cache lookup failed for relation

[Jan Wieck]

On 4/5/2008 7:47 AM, Glyn Astill wrote:

Hi chaps,

I know there's been a bit of "activity" on this listrecently - but does anyone 
know where I should start looking to resolvethe below?


Yes, a "SET DROP TABLE" is mandatory prior to dropping the table itself. 
This is because up to version 1.2.x, Slony is deliberately corrupting 
the system catalog on subscriber nodes in order to suppress triggers and 
rules to fire (this can only be controlled by other means since Postgres 
8.3 and will be done so in Slony 2.0).



Jan



- Original Message 

From: Glyn Astill <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Friday, 4 April, 2008 3:05:18 PM
Subject: [Slony1-general] ERROR:  XX000: cache lookup failed for relation

Hi chaps,

I've got a problem trying to drop a table, I get the error "cache lookup failed 
for relation"


SEE=# drop table replicated_users;
ERROR:  XX000: cache lookup failed for relation 30554884
LOCATION:  getRelationDescription, dependency.c:2021
Now this table is on a slony-I slave and was in replication when I tried to drop 
it - I presume this is a big mistake and I should never try to drop a table 
without first droping it from replication?


In addition I'd set up a trigger on the table "replicate_users".

If I do:

 select relname,oid from pg_class where relname = 'replicated_users';

-[ RECORD 1 ]-
relname | replicated_users
oid | 30554879

Thats not the same oid as the one it's complaining about.

Does anyone have any idea why this has happened or how I can fix it?

Cheers
Glyn






  ___ 
Yahoo! For Good helps you make a difference  


http://uk.promotions.yahoo.com/forgood/

___
Slony1-general mailing list
[EMAIL PROTECTED]
http://lists.slony.info/mailman/listinfo/slony1-general






  ___ 
Yahoo! For Good helps you make a difference  


http://uk.promotions.yahoo.com/forgood/

___
Slony1-general mailing list
[EMAIL PROTECTED]
http://lists.slony.info/mailman/listinfo/slony1-general



--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


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

Re: [GENERAL] pl/pgsql RECORD data type, how to access to the values

[Guillaume Bog]

On Sat, Apr 5, 2008 at 4:05 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote:

> Hello
>
>
>
>
> On 05/04/2008, Guillaume Bog <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as
> > argument to the trigger function.
> >
> > Provided my table has only one column named 'id', I can do easilly
> >
> > CREATE FUNCTION ft() RETURNS trigger AS $$
> >BEGIN
> >   RAISE NOTICE 'It works:%', OLD.id;
> >   END
> > $$ LANGUAGE plpgsql;
> >
> > But I'd like to do
> >
> > CREATE FUNCTION ft() RETURNS trigger AS $$
> >   DECLARE
> > col VARCHAR;
> >   BEGIN
> > col = TG_ARGV[0]
> > RAISE NOTICE 'This does not works:%', OLD.col
> > RAISE NOTICE 'This also does not works:%', OLD[col]
> >   END
> >  $$ LANGUAGE plpgsql;
> >
> > I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I
> > checked the docs.
>
> It's not possible in plpgsql. You have to use plperl, pltcl or plpython.

Ok, thanks. I may keep my code in the previous state instead because I
only have a little bit of duplication that currently still fits on one
screen, and it seems preferable to use pl/pgsql in my case.

Another question that is puzzling me:

I want a table to be "read-only", so I raise exceptions with a before
trigger on update, insert and delete. It works well.

This read-only table is modified (delete + insert) by a trigger
function set on another table, but this second trigger calls the first
and I can modify my read-only table. I'd like my "read-only" trigger
to be aware that the modification call on the read-only table comes
from the second trigger. Is it possible?

I have read in some places that I should use a rule instead, but I
never used them and it seems complex. I would prefer not to set up
complex access rules with GRANT and REVOKE because my access rules in
simple and works now. The best solution I can think of so far is to
have the client application work with a view, but having this behavior
fully managed through triggers would be more natural and I fear I
missed something in the docs.

> Regards
> Pavel Stehule
>
> >
> >
>

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

Re: [GENERAL] pg_dump and schemas

[Rusty Conover]

Hi Brent,

It's not he best solution, because we could have fields containing  
"public" in their names and sed would happily change those to test1 as  
well.


I'm looking for a safer solution, thats why it should be a part of  
pg_dump.


Rusty

On Apr 5, 2008, at 12:41 AM, Brent Wood wrote:


Hi Rusty,

Try passing the output through a utility like sed, already there  
under Linux , but versions that work under Windows are available  
(eg, cygwin)


eg, using a pipe:   pg_dump -d | sed 's/public/test1/g' > dump.sql

or converting a pg_dump output file:

pg_dump
cat dump.sql | sed 's/public/test1/g' > dump2.sql

With tools like these freely available, you don't really need to  
spend time reinventing them in your database applications. Of  
course. if you have the "public" schema name used elsewhere in your  
database, you may need to get a bit creative in your use of sed, but  
it can pretty well always be made to do this sort of operation.


Cheers,

  Brent Wood




Rusty Conover <[EMAIL PROTECTED]> 05/04/08 8:42 AM >>>

Hi All,

Is there a way to pass a parameter to pg_dump that would make the
produced dump be loaded into a different schema rather then the one it
is being dumped from?  Basically be able to say dump out of public,
but write the dump so its restored to say "test1".

Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com







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



--
Rusty Conover
InfoGears Inc.
http://www.infogears.com







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

[GENERAL] mac ports question

[Tom Allison]

I ran into a problem today where somewhere my port of postgresql82 just 
stopped working.  I'm largely an idiot on Mac because I use is as a 
workstation/development box and do most of the real system related work 
on my debian boxes.


But I don't know how to get the port working again.

Then I saw there is a new version 8.3 in port.

So, if I upgrade does anyone know if this cleanly removes version 8.2 
from the box so I don't have to start carrying multiple versions?


I don't really need to upgrade, but I figured it would be an easy way to 
get my database running again and get me back up to current toys.  Of 
course, upgrades don't often work out that well...


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

Re: [GENERAL] [Slony1-general] ERROR: XX000: cache lookup failed for relation

[Glyn Astill]

Hi chaps,

I know there's been a bit of "activity" on this listrecently - but does anyone 
know where I should start looking to resolvethe below?

- Original Message 
> From: Glyn Astill <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Cc: pgsql-general@postgresql.org
> Sent: Friday, 4 April, 2008 3:05:18 PM
> Subject: [Slony1-general] ERROR:  XX000: cache lookup failed for relation
> 
> Hi chaps,
> 
> I've got a problem trying to drop a table, I get the error "cache lookup 
> failed 
> for relation"
> 
> SEE=# drop table replicated_users;
> ERROR:  XX000: cache lookup failed for relation 30554884
> LOCATION:  getRelationDescription, dependency.c:2021
> Now this table is on a slony-I slave and was in replication when I tried to 
> drop 
> it - I presume this is a big mistake and I should never try to drop a table 
> without first droping it from replication?
> 
> In addition I'd set up a trigger on the table "replicate_users".
> 
> If I do:
> 
>  select relname,oid from pg_class where relname = 'replicated_users';
> 
> -[ RECORD 1 ]-
> relname | replicated_users
> oid | 30554879
> 
> Thats not the same oid as the one it's complaining about.
> 
> Does anyone have any idea why this has happened or how I can fix it?
> 
> Cheers
> Glyn
> 
> 
> 
> 
> 
> 
>   ___ 
> Yahoo! For Good helps you make a difference  
> 
> http://uk.promotions.yahoo.com/forgood/
> 
> ___
> Slony1-general mailing list
> [EMAIL PROTECTED]
> http://lists.slony.info/mailman/listinfo/slony1-general
> 




  ___ 
Yahoo! For Good helps you make a difference  

http://uk.promotions.yahoo.com/forgood/


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

Re: [GENERAL] default value based on select

[Volkan YAZICI]

On Sat, 5 Apr 2008, Pedro Doria Meunier <[EMAIL PROTECTED]> writes:
> The record being created has a column with an Id that has to be passed as an 
> argument to the function that, in turn, fills the other column in the same 
> record.

Doesn't BEFORE INSERT/UPDATE trigger solve this problem? (If not, I
wonder what is missing.)


Regards.

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

Re: [GENERAL] default value based on select

[Pedro Doria Meunier]

Pavel and Volkan

Thank you for your kind (and prompt) replies.

Let me try to better explain my needs:
The record being created has a column with an Id that has to be passed as an 
argument to the function that, in turn, fills the other column in the same 
record.

Does this even make sense? :)

Kind regards,
-- 
Pedro Doria Meunier
Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
9125-162 Caniço
Madeira - Portugal
--
Skype : pdoriam
Mobile: +351961720188


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

Re: [GENERAL] default value based on select

[Volkan YAZICI]

On Sat, 5 Apr 2008, Pedro Doria Meunier <[EMAIL PROTECTED]> writes:
> Is it possible to have a table's column default value set to some form of 
> select?

AFAIK, you cannot provide sub-selects in the default values of a
field. E.g.

  CREATE TABLE foo (bar int DEFAULTS (SELECT ...), ...);

For this, I know two solutions:

1. You can create an SQL function that issues the related sub-select and
   call this function as the default value of the column.

2. Create a BEFORE INSERT/UPDATE trigger that issues the related
   sub-select if related column appears to be un-specified.


Regards.

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

Re: [GENERAL] default value based on select

[Pavel Stehule]

Hello

On 05/04/2008, Pedro Doria Meunier <[EMAIL PROTECTED]> wrote:
> Hi,
>
>  Is it possible to have a table's column default value set to some form of
>  select?
>

directly no, but you can use function

postgres=# create table a(a integer);
CREATE TABLE

postgres=# create function mx() returns int as $$select max(a) from
a$$ language sql volatile;
CREATE FUNCTION
postgres=# create table b(x integer default mx());
CREATE TABLE

Regards
Pavel Stehule
>  e.g. picking another table's value for a given SELECT ... WHERE ...
>
>  Already thankful for any insight,
>
> --
>  Pedro Doria Meunier
>  Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
>  9125-162 Caniço
>  Madeira - Portugal
>  --
>  Skype : pdoriam
>  Mobile: +351961720188
>
>

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

[GENERAL] default value based on select

[Pedro Doria Meunier]

Hi,

Is it possible to have a table's column default value set to some form of 
select?

e.g. picking another table's value for a given SELECT ... WHERE ...

Already thankful for any insight,
-- 
Pedro Doria Meunier
Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
9125-162 Caniço
Madeira - Portugal
--
Skype : pdoriam
Mobile: +351961720188


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

Re: [GENERAL] simple update queries take a long time - postgres 8.3.1

[Tomasz Ostrowski]

On 04/04/2008 05:43 PM, mark wrote:


stats with new settings are below..


These stats look good for me.

but even with this sometimes update queries take more than coupla 
seconds sometimes...



checkpoint_completion_target = 0.8


It looks like that this is set too high for your workload. If you have
spikes of activity then it would be possible that checkpoint will not
end it's work before the next one should be started - I think it would
then halt everything until finished. Try to set it back to 0.5 and observe.

Did you turn on log_checkpoints as recommended? Could you quote your log
lines about checkpoints? Do they correlate with long running queries?


sometimes INSERT statements are also slow. is there any settings
can tweak to make INSERT statements fast?


For postgresql update in table data is the same as delete+insert, so the 
same optimizations apply.


Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

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

Re: [GENERAL] pl/pgsql RECORD data type, how to access to the values

[Pavel Stehule]

Hello

On 05/04/2008, Guillaume Bog <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as
> argument to the trigger function.
>
> Provided my table has only one column named 'id', I can do easilly
>
> CREATE FUNCTION ft() RETURNS trigger AS $$
>BEGIN
>   RAISE NOTICE 'It works:%', OLD.id;
>   END
> $$ LANGUAGE plpgsql;
>
> But I'd like to do
>
> CREATE FUNCTION ft() RETURNS trigger AS $$
>   DECLARE
> col VARCHAR;
>   BEGIN
> col = TG_ARGV[0]
> RAISE NOTICE 'This does not works:%', OLD.col
> RAISE NOTICE 'This also does not works:%', OLD[col]
>   END
>  $$ LANGUAGE plpgsql;
>
> I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I
> checked the docs.

It's not possible in plpgsql. You have to use plperl, pltcl or plpython.

Regards
Pavel Stehule

>
>

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

Re: [GENERAL] Word boundaries in regular expressions

[Thomas Pundt]

Troy Rasiah schrieb:


Apologies if this has been posted somewhere else but what is the 
equivalent of perl's \b in postgres regular expressions ?


I think you are looking for \m and \M.

See
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Ciao,
Thomas

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

[GENERAL] pl/pgsql RECORD data type, how to access to the values

[Guillaume Bog]

Hello,

I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as
argument to the trigger function.

Provided my table has only one column named 'id', I can do easilly

CREATE FUNCTION ft() RETURNS trigger AS $$
  BEGIN
  RAISE NOTICE 'It works:%', OLD.id;
  END
$$ LANGUAGE plpgsql;

But I'd like to do

CREATE FUNCTION ft() RETURNS trigger AS $$
  DECLARE
col VARCHAR;
  BEGIN
col = TG_ARGV[0]
RAISE NOTICE 'This does not works:%', OLD.col
RAISE NOTICE 'This also does not works:%', OLD[col]
  END
$$ LANGUAGE plpgsql;

I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I
checked the docs.