[GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions

[Peter Eisentraut]

It has come to the attention of the core team of the PostgreSQL project 
that insecure programming practice is widespread in SECURITY DEFINER 
functions.  Many of these functions are exploitable in that they allow 
users that have the privilege to execute such a function to execute 
arbitrary code with the privileges of the owner of the function.

The SECURITY DEFINER property of functions is a special non-default 
property that causes such functions to be executed with the privileges 
of their owner rather than with the privileges of the user invoking the 
function (the default mode, SECURITY INVOKER).  Thus, this mechanism is 
very similar to the "setuid" mechanism in Unix operating systems.

Because SQL object references in function code are resolved at run time, 
any references to SQL objects that are not schema qualified are 
resolved using the schema search path of the session at run time, which 
is under the control of the calling user.  By installing functions or 
operators with appropriate signatures in other schemas, users can then 
redirect any function or operator call in the function code to 
implementations of their choice, which, in case of SECURITY DEFINER 
functions, will still be executed with the function owner privileges.  
Note that even seemingly innocent invocations of arithmetic operators 
are affected by this issue, so it is likely that a large fraction of 
all existing functions are exploitable.

The proper fix for this problem is to insert explicit SET search_path 
commands into each affected function to produce a known safe schema 
search path.  Note that using the default search path, which includes a 
reference to the "$user" schema, is not safe when unqualified 
references are intended to be found in the "public" schema and "$user" 
schemas exist or can be created by other users.  It is also not 
recommended to rely on rigorously schema-qualifying all function and 
operator invocations in function source texts, as such measures are 
likely to induce mistakes and will furthermore make the source code 
harder to read and maintain.

This problem affects all existing PostgreSQL releases since version 7.3.  
Because this situation is a case of poor programming practice in 
combination with a design mistake and inadequate documentation, no 
security releases of PostgreSQL will be made to address this problem at 
this time.  Instead, all users are urged to hastily correct their code 
as described above.  Appropriate technological fixes for this problem 
are being investigated for inclusion with PostgreSQL 8.3.

---(end of broadcast)---
-To unsubscribe from this list, send an email to:

   [EMAIL PROTECTED]




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




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

Re: [GENERAL] Timestamp/Timezone - does this make sense?

[Tom Lane]

Mike Harding <[EMAIL PROTECTED]> writes:
> Where does that extra 8 hours come from?

Ellay is 8 hours west of UTC (at least on 1-Jan, at least till our
congresscritters see fit to monkey with the DST laws again).  What
problem have you got with these answers?  They look right to me.

regards, tom lane

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

Re: [GENERAL] converting a specified year and week into a date

[A. Kretschmer]

am  Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:
> 
> hi guys,
> i was just wondering if it was at all possible to turn a year and a given
> week number into a real date just using postgresql commands?
> 
> 
> e.g. if i have year = 2004 and week = 1,
> can i turn that into say 2004-01-01 (so that the specified
> date is the one for the beginning of week 1 in the year 2004

You can extract the week from a given date with this:

SELECT EXTRACT(WEEK FROM '2006-01-01'::date);

Be careful, the 1.1. can be in the 52. week in the last year. If you
know the first day in the year in week 1, then you can add 7* the given
week-1 days to this date.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

   http://archives.postgresql.org/

Re: [GENERAL] Proper escaping for char(3) string, or PHP at fault, or me at fault?

[Chris]

semi-ambivalent wrote:

All,

I have a char(3) column that has occasional values of this:
(V)

In a PHP-called nested query I've a line something like:
select * from tableA where  = any (select date from tableA where void !
= '(V)') group by date order by record


Shouldn't that be

select * from table where FIELD = any (.)

?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Problem with 'tr' command

[Chris]

Kaushik, Sushil wrote:

I am having a problem running the 'tr' command:


I'd suggest finding a HP-UX mailing list, the postgres one isn't going 
to help you much here.


--
Postgresql & php tutorials
http://www.designmagick.com/

---(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] [ANNOUNCE] Advisory on possibly insecure security definer functions

[Peter Eisentraut]

It has come to the attention of the core team of the PostgreSQL project 
that insecure programming practice is widespread in SECURITY DEFINER 
functions.  Many of these functions are exploitable in that they allow 
users that have the privilege to execute such a function to execute 
arbitrary code with the privileges of the owner of the function.

The SECURITY DEFINER property of functions is a special non-default 
property that causes such functions to be executed with the privileges 
of their owner rather than with the privileges of the user invoking the 
function (the default mode, SECURITY INVOKER).  Thus, this mechanism is 
very similar to the "setuid" mechanism in Unix operating systems.

Because SQL object references in function code are resolved at run time, 
any references to SQL objects that are not schema qualified are 
resolved using the schema search path of the session at run time, which 
is under the control of the calling user.  By installing functions or 
operators with appropriate signatures in other schemas, users can then 
redirect any function or operator call in the function code to 
implementations of their choice, which, in case of SECURITY DEFINER 
functions, will still be executed with the function owner privileges.  
Note that even seemingly innocent invocations of arithmetic operators 
are affected by this issue, so it is likely that a large fraction of 
all existing functions are exploitable.

The proper fix for this problem is to insert explicit SET search_path 
commands into each affected function to produce a known safe schema 
search path.  Note that using the default search path, which includes a 
reference to the "$user" schema, is not safe when unqualified 
references are intended to be found in the "public" schema and "$user" 
schemas exist or can be created by other users.  It is also not 
recommended to rely on rigorously schema-qualifying all function and 
operator invocations in function source texts, as such measures are 
likely to induce mistakes and will furthermore make the source code 
harder to read and maintain.

This problem affects all existing PostgreSQL releases since version 7.3.  
Because this situation is a case of poor programming practice in 
combination with a design mistake and inadequate documentation, no 
security releases of PostgreSQL will be made to address this problem at 
this time.  Instead, all users are urged to hastily correct their code 
as described above.  Appropriate technological fixes for this problem 
are being investigated for inclusion with PostgreSQL 8.3.

---(end of broadcast)---
-To unsubscribe from this list, send an email to:

   [EMAIL PROTECTED]




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

Re: [GENERAL] backup database by cloning itself

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/13/07 07:54, filippo wrote:
> Hello,
> 
> my database is not very big so I want to adopt this backup strategy:
> 
> I want to clone my database every 1 hour  to another
> database 'currenttime_mydatabase' in order to have 24 backup a day,
> overwriting the yesterday backups by today-same-time backups.
> 
> This is good for me because I have all the backups readily available
> to be read by my program (opening the backup read only). This is a
> very important for my needs.

There's almost definitely a better way to do what you want to do.

What benefit are you trying to obtain by creating 720 almost
identical databases per month?

> I'm writing a script run by cron each hour to do accomplish the backup
> task.
> 
> My target is to have the backup operation not affecting the users, so
> I want to be able to copy a database even if the database is used by
> someone.
> 
> Can I use
> CREATE DATABASE my_backup_database TEMPLATE current_database?
> 
>  Is there a better way to get what I need?

Have you tried pg_dump?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0rtOS9HxQb37XmcRAtUxAKDWLK7x3uDGxwni47Y+o1yJsHXOzACg4XYu
ik9TtDFb6DJ+uZllXxahSMs=
=zcis
-END PGP SIGNATURE-

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

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

Re: [GENERAL] Slony Replication problem

[Chris]

Slawek wrote:

Hello,

  I tring to set up Slony replication and am having issues with
repeatability.


Probably best to ask on the slony list..

http://gborg.postgresql.org/pipermail/slony1-general/

Sign up here: http://gborg.postgresql.org/mailman/listinfo/slony1-general

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Union Query Improvement

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/13/07 07:46, Ray Bannon wrote:
> I have a query which is running a bit slowly, and I'm wondering if anyone
> has a design improvement. Basically it's a series of unions as follows:
> 
> Select ID, plan_name from table/view
> Where plan_name = 'A'
> And rownum = 1
> UNION
> Select ID, plan_name from table/view
> Where plan_name = 'B'
> And rownum = 1
> UNION
> Select ID, plan_name from table/view
> Where plan_name = 'C'
> And rownum = 1
> UNION
> 
> Ad infinitum for about 100 iterations.
> 
> Any way to write this more efficiently?

Just out of curiosity: why does your(?) design have 100 tables/views
with the same (or almost identical) structure?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0rpTS9HxQb37XmcRAtH5AJ4zz5NPM5rBsNWLrKC+/Md6GhxCNgCfZBHf
AeMbTRNKp4guK81pGwfU5wc=
=t9y+
-END PGP SIGNATURE-

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

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

Re: [GENERAL] Union Query Improvement

[Chris]

Ray Bannon wrote:

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.


I'm sure I'm missing something but wouldn't this give the same results:

select id, plan_name from table where rownum=1 and plan_name in ('A', 
'B', 'C');


?

Or do the rownum's change later on?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

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

Re: [GENERAL] Infinite loop in transformExpr()

[Tom Lane]

Fernando Schapachnik <[EMAIL PROTECTED]> writes:
> I've stumbled upon what seems to be a core-dumping infinite recursion 
> in transformExpr(), on 8.1.6.

A test case would help.

regards, tom lane

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

[GENERAL] Daniel Simmler is out off office

[daniel . simmler]

Ich werde ab  12.02.2007 nicht im Büro sein. Ich kehre zurück am
15.02.2007.

Ihre E-Mails werden nicht weitergeleitet.
Haben Sie Fragen zu Loomdata oder BaaN kontaktieren Sie bitte den Help Desk
oder Jürg Gambon.
Vielen Dank
__
I will be back on Feb 15 2007.
My e-mails won't be forwarded.
For questions regarding Loomdata or BaaN please contact Help Desk or Jürg
Gambon.

Thank you

Daniel Simmler
Process Manager
__
Sefar AG
Printing Division
P.O.Box
CH-9425 Thal
Switzerland

Phone +41 71 886 3468
[EMAIL PROTECTED]
http://www.sefar.com
__
Be Safe. Be Sefar.
**



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

[GENERAL] [ADM #INO-38939-552]: [ANNOUNCE] Advisory on possibly insecure security definer functions

[Administrator Az.pl]

[english version below]

Drodzy Państwo!

niniejsza wiadomość jest automatycznym potwierdzeniem otrzymania
zgłoszenia w Az.pl, na które prosimy nie odpowiadać.

Uprzejmie dziękujemy za wysłane zgłoszenie i pragniemy zapewnić,
że każde z Państwa zgłoszeń zostanie rozpatrzone z należyta starannością.
Państwa zgłoszeniu został nadany numer:
INO-38939-552
którego prosimy nie kasować z tematu wiadomości.

Powyższy numer pozwoli na zachowanie ciągłości korespondecji i zapobieżenie
pomyłkom komunikacyjnym pomiędzy Państwem a naszą firmą.
W trosce o Państwa cenny czas, odpowiedź na zgłoszenie zostanie wysłana
najpóźniej w ciągu 10 godzin roboczych.

W celu sprawdzenia statusu oraz historii zgłoszenia proszę wejść na stronę
http://support.az.pl. Aby sprawdzić status zgłoszenia konieczna jest prosta
rejestracja.
Odpowiedzi na najczęściej zadawane pytania moga Państwo znaleźć pod adresem
http://www.az.pl/faq.action
Cenne wskazówki oraz pomoc dotyczące panelu DirectAdmin znajdują się na
stronie http://pomoc.az.pl
Zachęcamy rownież do odwiedzania naszej strony http://www.az.pl


This e-mail is an automatic confirmation from our mailing system. Please do not 
response for it. 
Your e-mail has got number: INO-38939-552. During contacting with Az.pl please 
use this number. We will answer in 10 working hours.  
You can check Your e-mail application on: http://support.az.pl. 
Help You can find on: http://www.site-helper.com/ and FAQ on: 
http://www.az.pl/faq.action. 
Please visit also our website: www.az.pl.



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

[GENERAL] Problem with 'tr' command

[Kaushik, Sushil]

I am having a problem running the 'tr' command:
 
This is what I expected (tested in development environment)
Version: HP-UX sdmw728a B.11.00 U 9000/800 
 
sdmw728a:clmdev: /apdev/clmdev/cl_be/4300/bin> x=PMRR
sdmw728a:clmdev: /apdev/clmdev/cl_be/4300/bin> echo $x | tr [A-Z] [a-z]
pmrr
sdmw728a:clmdev: /apdev/clmdev/cl_be/4300/bin> y=BACARDI
sdmw728a:clmdev: /apdev/clmdev/cl_be/4300/bin> echo $y | tr [A-Z] [a-z]
bacardi
 
 
And this is what I am getting in production environment
Version: HP-UX spfe631a B.11.11 U 9000/800
 
[spfe631a]/home1/d/dev_tech $ x=PMRR
[spfe631a]/home1/d/dev_tech $ echo $x | tr [A-Z] [a-z]
PMRR
[spfe631a]/home1/d/dev_tech $ y=BACARDI
[spfe631a]/home1/d/dev_tech $ echo $y | tr [A-Z] [a-z]
BxCxRDI

Why the same command behave differently on the same version of HP-UX. 
 
 
Sush
Distributed Systems - CLMS Development
Phone: 302-457-5082

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

[GENERAL] Slony Replication problem

[Slawek]

Hello,

  I tring to set up Slony replication and am having issues with
repeatability.

Configuration: 2 nodes
Slony ver.: 1.2.6
OS: SuSE Linux 9.2

About 1/3 of the time, replication does not start properly.  I think
I've traced it down to the fact that sometimes the second node is not
created properly in the prime db.  I can see this by querying the
sl_node table where only 1 node (the prime) appears.  On the backup
db, both nodes appear properly.  To create the node, I'm using the
following slonik script

try {
echo 'Storing node 2';
store node (id = 2, comment = 'Node 2');
}
on error {
echo 'Could not create Node 2!';
exit -1;
}

No error is thrown when this code is ran, so as far as slonik script
is concerned everything is OK.

Questions:
1) What could be causing the second node to fail to be created?  If
this is a timing issue, is there an event, or some data, I could check
to make sure that the 2nd node is ready to be created?
2) I discover that this error has occured after Slony is started on
both servers, and the replication set is subscribed to.  If I have to
recreate this 2nd node on the prime server, what do I have to do?

Thank you,

Slawek


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

[GENERAL] Help, Can't figure out what is wrong with my stored procedure

[loulou2u]

To make a long story short, I am archiving data from an original table to a
table I created. This is a third party web application that I am doing this
with, so I can't revise the structure/code of this application. With this
said, if the original table goes through an insert or update action I want
to replicate the information to my archive table. I don't want to delete any
articles from my archive table so this is why I am not wanting to do
anything based on a delete action. 

The only problem that I am facing is how to tell the function that I want to
perform an update if an update occurred and an  insert if an insert action
occurred

Help.

Thanks in advance.


This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();

CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
 tmp_news_id CHARACTER varying(48);
 tmp_title CHARACTER varying(100);
 tmp_abstract CHARACTER varying(300);
 tmp_news_story TEXT;
 tmp_topic_id CHARACTER varying(10);
 tmp_create_date DATE;
 tmp_author CHARACTER varying(50);
 tmp_begin_date DATE;
 tmp_end_date DATE;
 tmp_priority CHARACTER(1);
 tmp_image_name CHARACTER varying(512);
 tmp_image_mime_type CHARACTER varying(50);
 tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where
last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where
last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where
last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where
last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where
last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where
last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where
last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where
last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where
last_inserted(news_id);

//This is to be done if an INSERT action was done on the table
INSERT INTO su_archives(news_id, title, abstract, news_story, topic_id,
create_date, author, begin_date, end_date, priority, image_name,
image_mime_type, l
ayout_type) VALUES
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS
DONE

RETURN NEW;
END
';

-- 
View this message in context: 
http://www.nabble.com/Help%2C-Can%27t-figure-out-what-is-wrong-with-my-stored-procedure-tf3221483.html#a8947077
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] converting a specified year and week into a date

[vanessa]

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?


e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004


thanks
vanessa  :)

-- 
View this message in context: 
http://www.nabble.com/converting-a-specified-year-and-week-into-a-date-tf3223753.html#a8954235
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   http://archives.postgresql.org/

[GENERAL] Union Query Improvement

[Ray Bannon]

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

Thanks!


---(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] help and Links using postgreSQL with ASP

[Pankaj]

Hi 

 

Need help if there is any script source or links to provide help how to you
PostgreSQL with ASP 

 

 

Thanks & Regards


Pankaj

 

"SELF REALIZATION IS THE FIRST ENCOUNTER WITH  REALITY -Sahajayoga.org." 

 

[GENERAL] Infinite loop in transformExpr()

[Fernando Schapachnik]

I've stumbled upon what seems to be a core-dumping infinite recursion 
in transformExpr(), on 8.1.6.

Backtrace:

Core was generated by `postgres'.
Program terminated with signal 10, Bus error.
Reading symbols from /usr/lib/libssl.so.3...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib/libssl.so.3
Reading symbols from /lib/libcrypto.so.3...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libcrypto.so.3
Reading symbols from /lib/libz.so.2...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libz.so.2
Reading symbols from /lib/libreadline.so.5...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libreadline.so.5
Reading symbols from /lib/libcrypt.so.2...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libcrypt.so.2
Reading symbols from /lib/libm.so.3...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libm.so.3
Reading symbols from /lib/libutil.so.4...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libutil.so.4
Reading symbols from /lib/libc.so.5...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libc.so.5
Reading symbols from /lib/libncurses.so.5...(no debugging symbols 
found)...done.
Loaded symbols for /lib/libncurses.so.5
Reading symbols from /usr/local/lib/postgresql/dblink.so...(no 
debugging symbols found)...done.
Loaded symbols for /usr/local/lib/postgresql/dblink.so
Reading symbols from /usr/local/lib/libpq.so.4...(no debugging symbols 
found)...done.
Loaded symbols for /usr/local/lib/libpq.so.4
Reading symbols from /usr/lib/libpthread.so.1...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib/libpthread.so.1
Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols 
found)...done.
Loaded symbols for /libexec/ld-elf.so.1

#0  0x080d5979 in transformExpr ()
#1  0x080d6700 in transformExpr ()
#2  0x080d5bbb in transformExpr ()
[...]
#21669 0x080d6700 in transformExpr ()
#21670 0x080d5bbb in transformExpr ()
#21671 0x080d669e in transformExpr ()
#21672 0x080d5ba5 in transformExpr ()
#21673 0x080d4f10 in transformWhereClause ()
#21674 0x080c13dd in parse_sub_analyze ()
#21675 0x080bf36f in parse_sub_analyze ()
#21676 0x080bf110 in parse_sub_analyze ()
#21677 0x080bf021 in parse_analyze ()
#21678 0x0818d949 in pg_analyze_and_rewrite ()
#21679 0x0818dd76 in pg_plan_queries ()
#21680 0x081908d5 in PostgresMain ()
#21681 0x0816e084 in ClosePostmasterPorts ()
#21682 0x0816d887 in ClosePostmasterPorts ()
#21683 0x0816bbcf in PostmasterMain ()
#21684 0x0816b5ed in PostmasterMain ()
#21685 0x0813376b in main ()

This is postgres 8.1.6 compiled from ports (with 
--enable-thread-safety) on FreeBSD/i386 5.3 (gcc version 3.4.2
[FreeBSD] 20040728).

Should I file a bug report?

Thanks!

Fernando.

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

   http://archives.postgresql.org/

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Shoaib Mir]

Make sure you have stats collector enabled, if auto vacuum is doing the
analyze and vacuum it should be recording that info in this view. For
details on this you can have a look at -->
http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html

Just for a test try doing a VACUUM or ANALYZE manually and see if that gets
updated in the last_vacuum of pg_stats_all_tables.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, Walter Vaughan <[EMAIL PROTECTED]> wrote:


Shoaib Mir wrote:

> pg_stat_all_table view should help you:
>
> select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
  last_autovacuum | last_autoanalyze
-+--
  |
...snip lots of identically blank lines...
  |
  |
(939 rows)

Does that mean it's working or not configured right?

Thanks,
Walter

---(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] Proper escaping for char(3) string, or PHP at fault, or me at fault?

[semi-ambivalent]

All,

I have a char(3) column that has occasional values of this:
(V)

In a PHP-called nested query I've a line something like:
select * from tableA where  = any (select date from tableA where void !
= '(V)') group by date order by record

This is throwing a query error in the log that blames the parens but
my efforts to properly escape them with backslashes or "E" doesn't
help. So perhaps this is an error in my PHP, and I'll deal with that,
but in the SQL portion shouldn't '\(V\)' do the trick? Or is it now
'E(VE)'?

thanks,

r


---(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] Timestamp/Timezone - does this make sense?

[Mike Harding]

mvh=> set time zone 'UTC';
SET
mvh=> select now();
  now  
---
 2007-02-13 03:37:35.660652+00
(1 row)

mvh=> select timestamp with time zone '2007-01-01' at time zone
'America/Los_Angeles';
  timezone   
-
 2006-12-31 16:00:00
(1 row)

mvh=> select timestamp '2007-01-01' at time zone 'America/Los_Angeles';
timezone

 2007-01-01 08:00:00+00
(1 row)

Where does that extra 8 hours come from?



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

[GENERAL] backup database by cloning itself

[filippo]

Hello,

my database is not very big so I want to adopt this backup strategy:

I want to clone my database every 1 hour  to another
database 'currenttime_mydatabase' in order to have 24 backup a day,
overwriting the yesterday backups by today-same-time backups.

This is good for me because I have all the backups readily available
to be read by my program (opening the backup read only). This is a
very important for my needs.

I'm writing a script run by cron each hour to do accomplish the backup
task.

My target is to have the backup operation not affecting the users, so
I want to be able to copy a database even if the database is used by
someone.

Can I use
CREATE DATABASE my_backup_database TEMPLATE current_database?

 Is there a better way to get what I need?

Thanks,
Filippo


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

Re: [GENERAL] pg_get_serial_sequence is inconsistent

[Bruce Momjian]

FYI, we have at least documented this behavior in 8.2.X:

   pg_get_serial_sequence returns the name of the
   sequence associated with a column, or NULL if no sequence is associated
   with the column.  The first input parameter is a table name with
   optional schema, and the second parameter is a column name.  Because
   the first parameter is potentially a schema and table, it is not treated
   as a double-quoted identifier, meaning it is lowercased by default,
   while the second parameter, being just a column name, is treated as
   double-quoted and has its case preserved.  The function returns a value

---

Frank Millman wrote:
> Hi all
> 
> I found the following post dated October 2004 - 
> 
> Tom Lane wrote:
> > Christopher Kings-Lynne  au> writes:
> > >> pg_get_serial_sequence() does dequoting/downcasing on its relation-name
> > >> argument, but not on its column-name argument.
> > 
> > > I presume the reason for that is that the first paramater can be
> qualified:
> > 
> > Right.  From a bare-functionality point of view there's nothing wrong
> > with it, it just seems inconsistent and therefore likely to trip someone
> > up in future.
> > 
> > But it seems no one else cares, so I'll shut up about it ...
> 
> This inconsistency has just bitten me. Did anyone decide to fix it, or does
> it still behave the same?
> 
> I am using 8.1.3. Apologies if this has been fixed in 8.2 - I could not find
> anything in the Release Notes.
> 
> Thanks
> 
> Frank Millman
> 
> 
> ---(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

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

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

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

   http://archives.postgresql.org/

Re: [GENERAL] suggestions on improving a query

[Tom Lane]

"Adam Rich" <[EMAIL PROTECTED]> writes:
> This line:
> Index Scan using plp_total_idx on dockscore_plp  
> (cost=0.00..16733229.92 rows=4669988 width=80) 
> (actual time=98.323..322537.605 rows=25197 loops=1)
> Means the planner did what it did, because it estimated there would be
> nearly 5 million rows.  However, there were only 25,000.

No, you have to be careful about interpreting the numbers when
underneath a Limit node.  The rows estimate is an estimate of the total
number of rows if the plan node were run to completion ... but if the
Limit stops execution early, that's not what will happen.  The actual
rows count shows how many rows really got pulled from the node before
the Limit stopped things.

The real problem here is that the planner is guessing that it won't take
very long to find 10 rows satisfying the target = '1YC1' condition while
scanning in dockscore_plp.total order.  So it chooses a plan that would
have a long total runtime (notice the large cost estimates below the
Limit) expecting that only a small fraction of that total will actually
be expended.  The expectation seems a bit off unfortunately :-(.
I can't tell from the given data whether the problem is just an
overestimate of the frequency of target = '1YC1', or if there's an
additional effect.  For example, if that target value tended to only be
associated with larger values of dockscore_plp.total, then a plan like
this could lose big-time because it will have to scan a long way to find
those rows.

regards, tom lane

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

Re: [GENERAL] suggestions on improving a query

[Tom Lane]

Rajarshi Guha <[EMAIL PROTECTED]> writes:
> However the clause: 
> dock.target = '1YC1' and  
> dock.dockid = dockscore_plp.id  
> reduces the number of rows from 4.6M to 96K.

The planner seems to be estimating about ten times that many.  Perhaps
increasing the statistics target for dock.target would help?

regards, tom lane

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

Re: [GENERAL] suggestions on improving a query

[Adam Rich]

This line:

Index Scan using plp_total_idx on dockscore_plp  
(cost=0.00..16733229.92 rows=4669988 width=80) 
(actual time=98.323..322537.605 rows=25197 loops=1)

Means the planner did what it did, because it estimated there would be
nearly 5 million rows.  However, there were only 25,000.

Have these tables been vacuumed & analyzed recently?  Your first step
should be to vacuum & analyze these, and send us the new "explain
analyze".



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Rajarshi Guha
Sent: Monday, February 12, 2007 2:24 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] suggestions on improving a query


Hi, I have a query that involves 3 tables. T

select pubchem_compound.openeye_can_smiles,
pubchem_compound.nist_inchi, dock.cid, dockscore_plp.*
from dock, dockscore_plp, pubchem_compound 
where
dock.target = '1YC1' and
dock.dockid = dockscore_plp.id  and
dock.cid = pubchem_compound.cid
order by dockscore_plp.total 
limit 10;

The output of explain analyze is

 Limit  (cost=0.00..387.36 rows=10 width=297) (actual
time=242977.644..462748.215 rows=10 loops=1)
   ->  Nested Loop  (cost=0.00..37325186.12 rows=963575 width=297)
(actual time=242977.638..462748.175 rows=10 loops=1)
 ->  Nested Loop  (cost=0.00..31523550.51 rows=963575 width=90)
(actual time=242900.629..461810.902 rows=10 loops=1)
   ->  Index Scan using plp_total_idx on dockscore_plp
(cost=0.00..16733229.92 rows=4669988 width=80) (actual
time=98.323..322537.605 rows=25197 loops=1)
   ->  Index Scan using dock_pkey on dock  (cost=0.00..3.15
rows=1 width=18) (actual time=5.521..5.521 rows=0 loops=25197)
 Index Cond: (dock.dockid = "outer".id)
 Filter: (target = '1YC1'::text)
 ->  Index Scan using pubchem_compound_pkey on pubchem_compound
(cost=0.00..6.01 rows=1 width=216) (actual time=93.699..93.704 rows=1
loops=10)
   Index Cond: (("outer".cid)::text =
(pubchem_compound.cid)::text)
 Total runtime: 462748.439 ms
(10 rows)

Now, the tables 'dock' and 'dockscore_plp' have 4.6M rows and
'pubchem_compound' has 10M rows.

However the clause: 

dock.target = '1YC1' and
dock.dockid = dockscore_plp.id  

reduces the number of rows from 4.6M to 96K. I had figured that after
this the query would be very fast. But the explain analyze seems to
indicate that the dockscore_plp table is being sorted (using the index
plp_total_idx) in its entirety. This would then be the bottleneck

Is this a correct interpretation?

What I expected was that the sort would be applied to the 96K subset of
dockscore_plp, rather than the whole table. 

Is it possible to restructure the query such that the sort is done on
96K rows rather than 4.6M rows?

Thanks,


---
Rajarshi Guha <[EMAIL PROTECTED]>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
---
"I'd love to go out with you, but my favorite commercial is on TV."



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


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

   http://archives.postgresql.org/

Re: [GENERAL] SEQUENCE primary key

[Tom Lane]

Chris <[EMAIL PROTECTED]> writes:
> Actually it's better to use currval.

Right.  Also, in 8.2 and up there's INSERT RETURNING, which is far
more flexible --- for instance it could pull back an insertion
timestamp.

regards, tom lane

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

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

[GENERAL] suggestions on improving a query

[Rajarshi Guha]

Hi, I have a query that involves 3 tables. T

select pubchem_compound.openeye_can_smiles, pubchem_compound.nist_inchi, 
dock.cid, dockscore_plp.*
from dock, dockscore_plp, pubchem_compound 
where
dock.target = '1YC1' and
dock.dockid = dockscore_plp.id  and
dock.cid = pubchem_compound.cid
order by dockscore_plp.total 
limit 10;

The output of explain analyze is

 Limit  (cost=0.00..387.36 rows=10 width=297) (actual 
time=242977.644..462748.215 rows=10 loops=1)
   ->  Nested Loop  (cost=0.00..37325186.12 rows=963575 width=297) (actual 
time=242977.638..462748.175 rows=10 loops=1)
 ->  Nested Loop  (cost=0.00..31523550.51 rows=963575 width=90) (actual 
time=242900.629..461810.902 rows=10 loops=1)
   ->  Index Scan using plp_total_idx on dockscore_plp  
(cost=0.00..16733229.92 rows=4669988 width=80) (actual time=98.323..322537.605 
rows=25197 loops=1)
   ->  Index Scan using dock_pkey on dock  (cost=0.00..3.15 rows=1 
width=18) (actual time=5.521..5.521 rows=0 loops=25197)
 Index Cond: (dock.dockid = "outer".id)
 Filter: (target = '1YC1'::text)
 ->  Index Scan using pubchem_compound_pkey on pubchem_compound  
(cost=0.00..6.01 rows=1 width=216) (actual time=93.699..93.704 rows=1 loops=10)
   Index Cond: (("outer".cid)::text = (pubchem_compound.cid)::text)
 Total runtime: 462748.439 ms
(10 rows)

Now, the tables 'dock' and 'dockscore_plp' have 4.6M rows and
'pubchem_compound' has 10M rows.

However the clause: 

dock.target = '1YC1' and
dock.dockid = dockscore_plp.id  

reduces the number of rows from 4.6M to 96K. I had figured that after
this the query would be very fast. But the explain analyze seems to
indicate that the dockscore_plp table is being sorted (using the index
plp_total_idx) in its entirety. This would then be the bottleneck

Is this a correct interpretation?

What I expected was that the sort would be applied to the 96K subset of
dockscore_plp, rather than the whole table. 

Is it possible to restructure the query such that the sort is done on
96K rows rather than 4.6M rows?

Thanks,


---
Rajarshi Guha <[EMAIL PROTECTED]>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
---
"I'd love to go out with you, but my favorite commercial is on TV."



---(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] SEQUENCE primary key

[Chris]

John McCawley wrote:

In PostgreSQL 8 and up:

SELECT lastval();


Actually it's better to use currval.

See 
http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-lastval-evil.html


--
Postgresql & php tutorials
http://www.designmagick.com/

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

  http://archives.postgresql.org/

Re: [GENERAL] SEQUENCE primary key

[John McCawley]

In PostgreSQL 8 and up:

SELECT lastval();

gustavo halperin wrote:


Hello

I have a question, if I have a table with a SEQUENCE primary key, that 
obviously, I doesn't give in each new row inserted. For example if the 
table locks:

 CREATE SEQUENCE id_seq;
 CREATE TABLE table ( idinteger DEFAULT 
nextval('id_seq')  CONSTRAINT table_id PRIMARY KEY,

   arg1 integer,
   arg2 integer
 )

How can I know which id receive each new row. I mean, 
 
suddenly  I insert one row (*) with the arg1 and arg2 . So ...,  there 
are something that I receive back ?? Some pointer, something? There 
are any way to know which number receive my row ?


Thank you,
  Gustavo

(*) This process can be multi-thread. In my case I use the libraries 
with the wxWidget project with the class wxDbTable.


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



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

[GENERAL] SEQUENCE primary key

[gustavo halperin]

Hello

I have a question, if I have a table with a SEQUENCE primary key, that 
obviously, I doesn't give in each new row inserted. For example if the 
table locks:

 CREATE SEQUENCE id_seq;
 CREATE TABLE table (  
   idinteger DEFAULT nextval('id_seq')  CONSTRAINT table_id 
PRIMARY KEY,

   arg1 integer,
   arg2 integer
 )

How can I know which id receive each new row. I mean, 
 
suddenly  I insert one row (*) with the arg1 and arg2 . So ...,  there 
are something that I receive back ?? Some pointer, something? There are 
any way to know which number receive my row ?


Thank you,
  Gustavo

(*) This process can be multi-thread. In my case I use the libraries 
with the wxWidget project with the class wxDbTable.


---(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] Advisory on possibly insecure security definer functions

[Peter Eisentraut]

It has come to the attention of the core team of the PostgreSQL project 
that insecure programming practice is widespread in SECURITY DEFINER 
functions.  Many of these functions are exploitable in that they allow 
users that have the privilege to execute such a function to execute 
arbitrary code with the privileges of the owner of the function.

The SECURITY DEFINER property of functions is a special non-default 
property that causes such functions to be executed with the privileges 
of their owner rather than with the privileges of the user invoking the 
function (the default mode, SECURITY INVOKER).  Thus, this mechanism is 
very similar to the "setuid" mechanism in Unix operating systems.

Because SQL object references in function code are resolved at run time, 
any references to SQL objects that are not schema qualified are 
resolved using the schema search path of the session at run time, which 
is under the control of the calling user.  By installing functions or 
operators with appropriate signatures in other schemas, users can then 
redirect any function or operator call in the function code to 
implementations of their choice, which, in case of SECURITY DEFINER 
functions, will still be executed with the function owner privileges.  
Note that even seemingly innocent invocations of arithmetic operators 
are affected by this issue, so it is likely that a large fraction of 
all existing functions are exploitable.

The proper fix for this problem is to insert explicit SET search_path 
commands into each affected function to produce a known safe schema 
search path.  Note that using the default search path, which includes a 
reference to the "$user" schema, is not safe when unqualified 
references are intended to be found in the "public" schema and "$user" 
schemas exist or can be created by other users.  It is also not 
recommended to rely on rigorously schema-qualifying all function and 
operator invocations in function source texts, as such measures are 
likely to induce mistakes and will furthermore make the source code 
harder to read and maintain.

This problem affects all existing PostgreSQL releases since version 7.3.  
Because this situation is a case of poor programming practice in 
combination with a design mistake and inadequate documentation, no 
security releases of PostgreSQL will be made to address this problem at 
this time.  Instead, all users are urged to hastily correct their code 
as described above.  Appropriate technological fixes for this problem 
are being investigated for inclusion with PostgreSQL 8.3.

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

Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype

[Anton Melser]

On 13/02/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Anton Melser" <[EMAIL PROTECTED]> writes:
> ERROR: operator is not unique: boolean = integer

> I get this whether castcontext is 'a' or 'i'.

If you make both cast directions the same priority then the system has
no basis for choosing bool = bool over int = int or vice versa.  Try
making one direction 'i' and the other not.  I'm not sure which one
ought to be 'i', really --- it depends a lot on the details of the
queries you are trying to make work.


That did the trick. Both seem to work, so for me that's great. Thanks
heaps. I may end up trying to get them to change it from 1 and 0 to
'1' and '0', as sql server accepts it, and if sql server accepts it, I
would be surprised if mysql doesn't...
Thanks again,
Anton

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

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

Re: [GENERAL] PGSQL 8.2.3 Installation problem

[Paul Lambert]

marcelo Cortez wrote:

hi there

same things occurs to me.
 Any body install win32 version with success???

 best regards
 MDC


--- RPK <[EMAIL PROTECTED]> escribió:


When I run the setup of PGSQL 8.2.3, it displays
error while initializing
database cluster. Error displayed is: "Failed to
execute initdb. Unable to
set file system permissions".

I am installing on Windows XP SP2 with
"administrator" log in.
--
View this message in context:


http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083

Sent from the PostgreSQL - general mailing list
archive at Nabble.com.


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









__ 
Preguntá. Respondé. Descubrí. 
Todo lo que querías saber, y lo que ni imaginabas, 
está en Yahoo! Respuestas (Beta). 
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas 



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

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




I've installed it on my WinXP Professional SP2 (32 bit) machine without 
error.


Action start 6:33:07: SetPermissions.
1: Setting filesystem permissions...
Action ended 6:33:07: SetPermissions. Return value 1.
MSI (s) (F4:44) [06:33:07:312]: Doing action: RunInitdb
Action 6:33:07: RunInitdb. Initializing database cluster (this may take 
a minute or two)...

Action start 6:33:07: RunInitdb.
1: Initializing database cluster (this may take a minute or two)...
Action ended 6:33:07: RunInitdb. Return value 1.

I wasn't installing under administrator, did this under my own account 
and had the install create the 'postgres' user account.


Perhaps something wrong with the default file permissions where you are 
installing Postgres. I assume Postgres creates directories that inherit 
the parent directory permissions. If you have given the parent 
restricted access, the 'postgres' user that PG runs under may not have 
access to those dirs.


Only thing I can think of anyway, if not then I am not sure why you 
would be having a problem.


Regards,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers

---(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] Using PITR for creating Hot Standby

[Merlin Moncure]

On 2/13/07, Dhaval Shah <[EMAIL PROTECTED]> wrote:

I am in a situation where we have to deploy a hot standby to a
postgres db server. There is no custom tablespace and all data files
are in $PGDATA

I was thinking of using PITR
[]http://www.postgresql.org/docs/8.1/static/backup-online.html] to
achieve that and here are my thoughts:

1. Continuously copy WAL files to the standby.
2. The standby is always in "recovery" mode, that is whenever it gets
a WAL file, it recovers to that WAL file.
3. Always copy the "incomplete WAL" file to a different location. This
is the "current" file.

When the standby is activated, it checks to see if it has any complete
WAL file to recover from? And if it has, it recovers to that file. At
the end it recovers to the incomplete current file and the standby is
now "recovered" up to a point in time.

My concerns are as follows?

1. Is the above feasible?
2. What are the gotchas if somebody has already done that?
3. Is there something I can do more efficiently?

I looked at SLONY and it can back up only tables with primary keys.
The current schema on the primary does not have all the tables with
primary keys and we cannot change the schema!


google pg_standby.  it is up and coming contrib module that does
exactly what you want.  transferring wal files from 'a' to 'b' can be
as simple as nfs mount, scp, you name it.

merlin

---(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] Using PITR for creating Hot Standby

[Kenneth Downs]

Dhaval Shah wrote:

I am in a situation where we have to deploy a hot standby to a
postgres db server. There is no custom tablespace and all data files
are in $PGDATA

I was thinking of using PITR
[]http://www.postgresql.org/docs/8.1/static/backup-online.html] to
achieve that and here are my thoughts:


Same here.



1. Continuously copy WAL files to the standby.


Right.


2. The standby is always in "recovery" mode, that is whenever it gets
a WAL file, it recovers to that WAL file.


This I decided against, only because traffic does not warrant it.  The 
idea at this point was to let the WAL files pile up in the offsite 
location and then work out a periodic schedule for running them, but it 
would be more like once/week than continuously.


My thinking was that maintaining a 60-second failover is not really what 
I'm after.  If the main server goes down, we would likely wait and hope 
for up to 10 minutes before starting to make DNS changes.  During that 
time we could always run a recovery of the WAL files and have the 
standby easily ready in time.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(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] Function in psql to Compare two numbers and return the bigger value

[Steve Wampler]

Emi Lu wrote:
> Does it mean it is not allowed to use "select greatest(12.6,3.8)" ?

It may mean you're not running 8.1?  Here's what I get:
---
->psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

swampler=# select GREATEST(6.3, 2.8);
 greatest
--
  6.3
(1 row)

swampler=# select greatest(6.3, 2.8);
 greatest
--
  6.3
(1 row)

swampler=#
---

-- 
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.

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

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

Re: [GENERAL] Having a problem with my stored procedure

[Ted]

On Feb 13, 1:45 pm, [EMAIL PROTECTED] (Laura McCord) wrote:
> About your last comment, I can't do any revisions of the third party
> application where the inserts and updates are occurring. Plus, this
> whole idea came from a workaround based on a glitch in the software
> where the expiration of articles is not occurring ,therefore I have to
> do a delete articles to prevent them from being displayed on the web
> interface. Also, I don't entirely want to get rid of them completely and
> that is why I am saving records in an archive table to be used in
> another application that I need to write in the future. So, this is the
> reason for the redundancy.it's a long story.
>
> -Laura
>

I don't think I was suggesting you do anything with the original
application.  Rather, I was suggesting you alter what you're were
already trying to do.  You can apparently create as many trigger
functions as you like (implied by what I have read in the PostgreSQL
documentation), presumably without name collisions though, and you
have absolute control of what you do within the functions you develop,
as long as it is syntactically correct.  So far, I see nothing
preventing you from writing two trigger functions or requiring you to
use so many temporaries.   How does not touching the third party
application affect the triggers you add to the database?

Cheers

Ted


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

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

Re: [GENERAL] Having a problem with my stored procedure

[Ted]

On Feb 13, 1:20 pm, [EMAIL PROTECTED] (Laura McCord) wrote:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:
>
> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists
>
> So, I thought perhaps it couldn't be done.
>
>
>
>
>
> Ted Byers wrote:
> > Would it not be simpler to just create two trigger functions, one that
> > acts on insert operations and a second that acts on update
> > operations?  A 30 second glance at the Postgresql documentation showed
> > me that it is possible to have more than one row level trigger for a
> > given table, which implies the simpler options is possible.  This
> > would make for a much simpler design and avoid a conditional block
> > that would then be unnecessary.  This extra cost is, of course,
> > trivial if only a handful of records are modified or created, but if
> > the number is large, it could become significant.  Or is there
> > something in how an RDBMS handles triggers that would make it
> > preferable to have a single trigger for all possible operations on a
> > record?  Something an old C++ programmer would miss if not informed
> > about the peculiarities of database development.  Did I miss something
> > critical?  My usual approach is to have functions remain as simple as
> > practicable and do only one thing, unless there is a very good reason
> > to have them more complex (in which a driver function that calls a
> > number of simple functions may be preferable to one that tries to do
> > everything).  Simple functions are easy to validate, and once
> > validated make validation of more complex driver functions easier.
>
> > Why bother with so many temporaries?  Isn't that a waste of both
> > development time (lots of extra typing and opportunity for errors such
> > as typos) and runtime CPU cycles?  Why not just insert or update
> > values directly from the NEW or OLD record into the target table
> > rather than copying the values first into the temporaries and then
> > from the temporaries into their final destination?
>
> > HTH
>
> > Ted
>
> > - Original Message -
> > *From:* William Leite Araújo 
> > *To:* Laura McCord 
> > *Cc:* [EMAIL PROTECTED]
> > 
> > *Sent:* Tuesday, February 13, 2007 12:19 PM
> > *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>
> > 2007/2/13, Laura McCord <[EMAIL PROTECTED]
> > >:
>
> > To make a long story short, I am archiving data from an
> > original table
> > to a table I created. This is a third party web application
> > that I am
> > doing this with, so I can't revise the structure/code of this
> > application. With this said, if the original table goes
> > through an
> > insert or update action I want to replicate the information to my
> > archive table. I don't want to delete any articles from my archive
> > table so this is why I am not wanting to do anything based on
> > a delete
> > action.
>
> > The only problem that I am facing is how to tell the function
> > that I want to perform an update if an update occurred and an
> > insert if an insert action occurred. I want to have different
> > actions occur depending on if the trigger was based on an
> > insert or update.
>
> > Help, I've been stumped for two days.
> > Thanks in advance.
>
> > This is what I have so far:
> > CREATE TRIGGER archive_articles
> > AFTER INSERT OR UPDATE ON
> > news_content
> > EXECUTE PROCEDURE su_archive_articles();
>
> > CREATE OR REPLACE FUNCTION su_archive_articles()
> > RETURNS TRIGGER
> > LANGUAGE plpgsql
> > AS '
> > DECLARE
> > tmp_news_id CHARACTER varying(48);
> > tmp_title CHARACTER varying(100);
> > tmp_abstract CHARACTER varying(300);
> > tmp_news_story TEXT;
> > tmp_topic_id CHARACTER varying(10);
> > tmp_create_date DATE;
> > tmp_author CHARACTER varying(50);
> > tmp_begin_date DATE;
> > tmp_end_date DATE;
> > tmp_priority CHARACTER(1);
> > tmp_image_name CHARACTER varying(512);
> > tmp_image_mime_type CHARACTER varying(50);
> > tmp_layout_type CHARACTER varying(10);
>
> > BEGIN
> > SELECT INTO  tmp_news_id news_id from news_content where
> > last_inserted(news_id);
> > SELECT INTO  tmp_title title from news_content where
> > last_inserted(news_id);
> > SELECT INTO  tmp_abstract abstract from news_content where
> > last_inserted(news_id);
> > SELECT INT

Re: [GENERAL] Having a problem with my stored procedure

[Laura McCord]

Ok, I think I am starting to put two-and-two together based on your
thread and Ted's thread, I just realized that OLD. and NEW. are keywords
in postgres.

Alan Hodgson wrote:
> On Tuesday 13 February 2007 11:35, Laura McCord 
> <[EMAIL PROTECTED]> wrote:
>   
>> Here is a question that I am stumped on:
>>
>> Does postgres even recognize last_inserted() as mysql does? I notice
>> that the function fails on that line.
>>
>> 
>
> Not, that's just a MySQL function.  You could, however, look for the 
> last value of the sequence generator for the table's primary key, which 
> should be (but may not always be) equivalent.
>
> However, you are doing far more work than necessary getting the 
> inserted/updated data; you can just access it through the OLD. and NEW. 
> records already provided to the trigger.
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html
>
>
>   

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

Re: [GENERAL] Having a problem with my stored procedure

[Alan Hodgson]

On Tuesday 13 February 2007 11:35, Laura McCord 
<[EMAIL PROTECTED]> wrote:
> Here is a question that I am stumped on:
>
> Does postgres even recognize last_inserted() as mysql does? I notice
> that the function fails on that line.
>

Not, that's just a MySQL function.  You could, however, look for the 
last value of the sequence generator for the table's primary key, which 
should be (but may not always be) equivalent.

However, you are doing far more work than necessary getting the 
inserted/updated data; you can just access it through the OLD. and NEW. 
records already provided to the trigger.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html


-- 
"If a nation expects to be ignorant and free, in a state of 
civilization, it expects what never was and never will be." -- Thomas 
Jefferson


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

[GENERAL] Using PITR for creating Hot Standby

[Dhaval Shah]

I am in a situation where we have to deploy a hot standby to a
postgres db server. There is no custom tablespace and all data files
are in $PGDATA

I was thinking of using PITR
[]http://www.postgresql.org/docs/8.1/static/backup-online.html] to
achieve that and here are my thoughts:

1. Continuously copy WAL files to the standby.
2. The standby is always in "recovery" mode, that is whenever it gets
a WAL file, it recovers to that WAL file.
3. Always copy the "incomplete WAL" file to a different location. This
is the "current" file.

When the standby is activated, it checks to see if it has any complete
WAL file to recover from? And if it has, it recovers to that file. At
the end it recovers to the incomplete current file and the standby is
now "recovered" up to a point in time.

My concerns are as follows?

1. Is the above feasible?
2. What are the gotchas if somebody has already done that?
3. Is there something I can do more efficiently?

I looked at SLONY and it can back up only tables with primary keys.
The current schema on the primary does not have all the tables with
primary keys and we cannot change the schema!


Thanks in advance
Dhaval Shah

---(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] Having a problem with my stored procedure

[Laura McCord]

Here is a question that I am stumped on:

Does postgres even recognize last_inserted() as mysql does? I notice
that the function fails on that line.

Thanks,
 Laura

Tom Lane wrote:
> Laura McCord <[EMAIL PROTECTED]> writes:
>   
>> I tried doing two different triggers as you suggested but I kept getting
>> an error stating:
>> 
>
>   
>> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
>> for relation "news_content" already exists
>> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
>> relation "news_content" already exists
>> 
>
>   
>> So, I thought perhaps it couldn't be done.
>> 
>
> You need to pick different names for the triggers ... or if you're
> trying to replace an existing trigger definition, you need to DROP it first.
>
>   regards, tom lane
>   

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

Re: [GENERAL] Having a problem with my stored procedure

[Tom Lane]

Laura McCord <[EMAIL PROTECTED]> writes:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:

> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists

> So, I thought perhaps it couldn't be done.

You need to pick different names for the triggers ... or if you're
trying to replace an existing trigger definition, you need to DROP it first.

regards, tom lane

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

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

Re: [GENERAL] PostgreSQL and OpenLdap

[Magnus Hagander]

Cristiano Panvel wrote:
> It does not appear nothing in log, only in /var/log/message the error
> of failed in login.
> 
> Feb 13 12:04:16 fns4 postgres[7055]: [4-1] FATAL:  LDAP authentication
> failed for user "scott"
> Feb 13 12:04:20 fns4 postgres[7056]: [4-1] FATAL:  LDAP authentication
> failed for user "scott"
> Feb 13 12:04:20 fns4 postgres[7057]: [4-1] FATAL:  LDAP authentication
> failed for user "scott"
> Feb 13 12:45:57 fns4 postgres[7216]: [4-1] FATAL:  LDAP authentication
> failed for user "sflo"
> Feb 13 12:46:10 fns4 postgres[7223]: [4-1] FATAL:  LDAP authentication
> failed for user "dbadm"

There definitely should be more than that. Note however that most of
these things are not classified as errors, so they are logged at LOG
level. It may be that you're filtering so you're not showing LOG level
information, or perhaps your syslogd is configured to write them to a
different file.

AFAICS, the only code-path that does not log *why* it rejected the
authentication is the case when the client refuses to send a password.

//Magnus

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

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

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

[Emi Lu]

Emi Lu wrote:



I am looking for a psql method to get the bigger value of two numbers.

For example,

methodName(12.6, 3.8)


select greatest(12.6,3.8);


It does not work for me,
select greatest(12.6,3.8);
ERROR:  function greatest(numeric, numeric) does not exist
HINT:   No function matches the given name and argument types. You may 
need to add explicit type casts.


In 8.1 docs, it says that:
GREATEST(value [, ...])
LEAST(value [, ...])

* Note that GREATEST and LEAST are not in the SQL standard, but are a 
common extension. *


Does it mean it is not allowed to use "select greatest(12.6,3.8)" ?


I see the reason, my version is 8.0.8. The doc is for 8.1.

Thank you for all your help!

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

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

[Emi Lu]

I am looking for a psql method to get the bigger value of two numbers.

For example,

methodName(12.6, 3.8)


select greatest(12.6,3.8);


It does not work for me,
select greatest(12.6,3.8);
ERROR:  function greatest(numeric, numeric) does not exist
HINT:   No function matches the given name and argument types. You may 
need to add explicit type casts.


In 8.1 docs, it says that:
GREATEST(value [, ...])
LEAST(value [, ...])

* Note that GREATEST and LEAST are not in the SQL standard, but are a 
common extension. *


Does it mean it is not allowed to use "select greatest(12.6,3.8)" ?

thanks


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

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

[Bricklen Anderson]

Emi Lu wrote:

HEllo,

I am looking for a psql method to get the bigger value of two numbers.

For example,

methodName(12.6, 3.8)


select greatest(12.6,3.8);

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

Re: [GENERAL] Having a problem with my stored procedure

[Laura McCord]

About your last comment, I can't do any revisions of the third party
application where the inserts and updates are occurring. Plus, this
whole idea came from a workaround based on a glitch in the software
where the expiration of articles is not occurring ,therefore I have to
do a delete articles to prevent them from being displayed on the web
interface. Also, I don't entirely want to get rid of them completely and
that is why I am saving records in an archive table to be used in
another application that I need to write in the future. So, this is the
reason for the redundancy.it's a long story.

-Laura


Laura McCord wrote:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:
>
> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists
>
> So, I thought perhaps it couldn't be done.
>
>
>
> Ted Byers wrote:
>   
>> Would it not be simpler to just create two trigger functions, one that
>> acts on insert operations and a second that acts on update
>> operations?  A 30 second glance at the Postgresql documentation showed
>> me that it is possible to have more than one row level trigger for a
>> given table, which implies the simpler options is possible.  This
>> would make for a much simpler design and avoid a conditional block
>> that would then be unnecessary.  This extra cost is, of course,
>> trivial if only a handful of records are modified or created, but if
>> the number is large, it could become significant.  Or is there
>> something in how an RDBMS handles triggers that would make it
>> preferable to have a single trigger for all possible operations on a
>> record?  Something an old C++ programmer would miss if not informed
>> about the peculiarities of database development.  Did I miss something
>> critical?  My usual approach is to have functions remain as simple as
>> practicable and do only one thing, unless there is a very good reason
>> to have them more complex (in which a driver function that calls a
>> number of simple functions may be preferable to one that tries to do
>> everything).  Simple functions are easy to validate, and once
>> validated make validation of more complex driver functions easier.
>>  
>> Why bother with so many temporaries?  Isn't that a waste of both
>> development time (lots of extra typing and opportunity for errors such
>> as typos) and runtime CPU cycles?  Why not just insert or update
>> values directly from the NEW or OLD record into the target table
>> rather than copying the values first into the temporaries and then
>> from the temporaries into their final destination?
>>  
>> HTH
>>  
>> Ted
>>
>> - Original Message -
>> *From:* William Leite Araújo 
>> *To:* Laura McCord 
>> *Cc:* pgsql-general@postgresql.org
>> 
>> *Sent:* Tuesday, February 13, 2007 12:19 PM
>> *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>>
>> 2007/2/13, Laura McCord <[EMAIL PROTECTED]
>> >:
>>
>> To make a long story short, I am archiving data from an
>> original table
>> to a table I created. This is a third party web application
>> that I am
>> doing this with, so I can't revise the structure/code of this
>> application. With this said, if the original table goes
>> through an
>> insert or update action I want to replicate the information to my
>> archive table. I don't want to delete any articles from my archive
>> table so this is why I am not wanting to do anything based on
>> a delete
>> action.
>>
>> The only problem that I am facing is how to tell the function
>> that I want to perform an update if an update occurred and an
>> insert if an insert action occurred. I want to have different
>> actions occur depending on if the trigger was based on an
>> insert or update.
>>
>> Help, I've been stumped for two days.
>> Thanks in advance.
>>
>> This is what I have so far:
>> CREATE TRIGGER archive_articles
>> AFTER INSERT OR UPDATE ON
>> news_content
>> EXECUTE PROCEDURE su_archive_articles();
>>
>>
>>
>> CREATE OR REPLACE FUNCTION su_archive_articles()
>> RETURNS TRIGGER
>> LANGUAGE plpgsql
>> AS '
>> DECLARE
>> tmp_news_id CHARACTER varying(48);
>> tmp_title CHARACTER varying(100);
>> tmp_abstract CHARACTER varying(300);
>> tmp_news_story TEXT;
>> tmp_topic_id CHARACTER varying(10);
>> tmp_create_date DATE;
>> tmp_author CHARACTER varying(50);
>> tmp_begin_date DATE;
>> tmp_end_date DATE;
>>   

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

[Adam Rich]

You want GREATEST()




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu
Sent: Tuesday, February 13, 2007 12:37 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Function in psql to Compare two numbers and return
the bigger value


HEllo,

I am looking for a psql method to get the bigger value of two numbers.

For example,

methodName(12.6, 3.8)

Will return 12.6

Thanks!

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


---(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] Function in psql to Compare two numbers and return the bigger value

[Emi Lu]

HEllo,

I am looking for a psql method to get the bigger value of two numbers.

For example,

methodName(12.6, 3.8)

Will return 12.6

Thanks!

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

Re: [GENERAL] Having a problem with my stored procedure

[Laura McCord]

I tried doing two different triggers as you suggested but I kept getting
an error stating:

psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
for relation "news_content" already exists
psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
relation "news_content" already exists

So, I thought perhaps it couldn't be done.



Ted Byers wrote:
> Would it not be simpler to just create two trigger functions, one that
> acts on insert operations and a second that acts on update
> operations?  A 30 second glance at the Postgresql documentation showed
> me that it is possible to have more than one row level trigger for a
> given table, which implies the simpler options is possible.  This
> would make for a much simpler design and avoid a conditional block
> that would then be unnecessary.  This extra cost is, of course,
> trivial if only a handful of records are modified or created, but if
> the number is large, it could become significant.  Or is there
> something in how an RDBMS handles triggers that would make it
> preferable to have a single trigger for all possible operations on a
> record?  Something an old C++ programmer would miss if not informed
> about the peculiarities of database development.  Did I miss something
> critical?  My usual approach is to have functions remain as simple as
> practicable and do only one thing, unless there is a very good reason
> to have them more complex (in which a driver function that calls a
> number of simple functions may be preferable to one that tries to do
> everything).  Simple functions are easy to validate, and once
> validated make validation of more complex driver functions easier.
>  
> Why bother with so many temporaries?  Isn't that a waste of both
> development time (lots of extra typing and opportunity for errors such
> as typos) and runtime CPU cycles?  Why not just insert or update
> values directly from the NEW or OLD record into the target table
> rather than copying the values first into the temporaries and then
> from the temporaries into their final destination?
>  
> HTH
>  
> Ted
>
> - Original Message -
> *From:* William Leite Araújo 
> *To:* Laura McCord 
> *Cc:* pgsql-general@postgresql.org
> 
> *Sent:* Tuesday, February 13, 2007 12:19 PM
> *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>
> 2007/2/13, Laura McCord <[EMAIL PROTECTED]
> >:
>
> To make a long story short, I am archiving data from an
> original table
> to a table I created. This is a third party web application
> that I am
> doing this with, so I can't revise the structure/code of this
> application. With this said, if the original table goes
> through an
> insert or update action I want to replicate the information to my
> archive table. I don't want to delete any articles from my archive
> table so this is why I am not wanting to do anything based on
> a delete
> action.
>
> The only problem that I am facing is how to tell the function
> that I want to perform an update if an update occurred and an
> insert if an insert action occurred. I want to have different
> actions occur depending on if the trigger was based on an
> insert or update.
>
> Help, I've been stumped for two days.
> Thanks in advance.
>
> This is what I have so far:
> CREATE TRIGGER archive_articles
> AFTER INSERT OR UPDATE ON
> news_content
> EXECUTE PROCEDURE su_archive_articles();
>
>
>
> CREATE OR REPLACE FUNCTION su_archive_articles()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS '
> DECLARE
> tmp_news_id CHARACTER varying(48);
> tmp_title CHARACTER varying(100);
> tmp_abstract CHARACTER varying(300);
> tmp_news_story TEXT;
> tmp_topic_id CHARACTER varying(10);
> tmp_create_date DATE;
> tmp_author CHARACTER varying(50);
> tmp_begin_date DATE;
> tmp_end_date DATE;
> tmp_priority CHARACTER(1);
> tmp_image_name CHARACTER varying(512);
> tmp_image_mime_type CHARACTER varying(50);
> tmp_layout_type CHARACTER varying(10);
>
> BEGIN
> SELECT INTO  tmp_news_id news_id from news_content where
> last_inserted(news_id);
> SELECT INTO  tmp_title title from news_content where
> last_inserted(news_id);
> SELECT INTO  tmp_abstract abstract from news_content where
> last_inserted(news_id);
> SELECT INTO  tmp_news_story news_story from news_content where
> last_inserted(news_id);
> SELECT INTO  tmp_topic_id topic_id from news_content where
> last_inserted(news_id);
> SELECT INTO  tmp_create_d

Re: [GENERAL] Having a problem with my stored procedure

[Ted Byers]

Would it not be simpler to just create two trigger functions, one that acts on 
insert operations and a second that acts on update operations?  A 30 second 
glance at the Postgresql documentation showed me that it is possible to have 
more than one row level trigger for a given table, which implies the simpler 
options is possible.  This would make for a much simpler design and avoid a 
conditional block that would then be unnecessary.  This extra cost is, of 
course, trivial if only a handful of records are modified or created, but if 
the number is large, it could become significant.  Or is there something in how 
an RDBMS handles triggers that would make it preferable to have a single 
trigger for all possible operations on a record?  Something an old C++ 
programmer would miss if not informed about the peculiarities of database 
development.  Did I miss something critical?  My usual approach is to have 
functions remain as simple as practicable and do only one thing, unless there 
is a very good reason to have them more complex (in which a driver function 
that calls a number of simple functions may be preferable to one that tries to 
do everything).  Simple functions are easy to validate, and once validated make 
validation of more complex driver functions easier.

Why bother with so many temporaries?  Isn't that a waste of both development 
time (lots of extra typing and opportunity for errors such as typos) and 
runtime CPU cycles?  Why not just insert or update values directly from the NEW 
or OLD record into the target table rather than copying the values first into 
the temporaries and then from the temporaries into their final destination?

HTH

Ted
  - Original Message - 
  From: William Leite Araújo 
  To: Laura McCord 
  Cc: pgsql-general@postgresql.org 
  Sent: Tuesday, February 13, 2007 12:19 PM
  Subject: Re: [GENERAL] Having a problem with my stored procedure


  2007/2/13, Laura McCord <[EMAIL PROTECTED]>:
To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an 
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action.

The only problem that I am facing is how to tell the function that I want 
to perform an update if an update occurred and an insert if an insert action 
occurred. I want to have different actions occur depending on if the trigger 
was based on an insert or update. 

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles(); 



CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
tmp_news_id CHARACTER varying(48);
tmp_title CHARACTER varying(100);
tmp_abstract CHARACTER varying(300); 
tmp_news_story TEXT;
tmp_topic_id CHARACTER varying(10);
tmp_create_date DATE;
tmp_author CHARACTER varying(50);
tmp_begin_date DATE;
tmp_end_date DATE;
tmp_priority CHARACTER(1);
tmp_image_name CHARACTER varying(512); 
tmp_image_mime_type CHARACTER varying(50);
tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where 
last_inserted(news_id); 
SELECT INTO  tmp_abstract abstract from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where 
last_inserted(news_id); 
SELECT INTO  tmp_create_date create_date from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where 
last_inserted(news_id); 
SELECT INTO  tmp_end_date end_date from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where 
last_inserted(news_id); 
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where 
last_inserted(news_id);

 IF TG_OP = 'INSERT' THEN 



//This is to be done if an INSERT action was done on the table

INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, im

Re: [GENERAL] Having a problem with my stored procedure

[William Leite Araújo]

2007/2/13, Laura McCord <[EMAIL PROTECTED]>:


To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action.

The only problem that I am facing is how to tell the function that I want
to perform an update if an update occurred and an insert if an insert action
occurred. I want to have different actions occur depending on if the trigger
was based on an insert or update.

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();



CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
tmp_news_id CHARACTER varying(48);
tmp_title CHARACTER varying(100);
tmp_abstract CHARACTER varying(300);
tmp_news_story TEXT;
tmp_topic_id CHARACTER varying(10);
tmp_create_date DATE;
tmp_author CHARACTER varying(50);
tmp_begin_date DATE;
tmp_end_date DATE;
tmp_priority CHARACTER(1);
tmp_image_name CHARACTER varying(512);
tmp_image_mime_type CHARACTER varying(50);
tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where
last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where
last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where
last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where
last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where
last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where
last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where
last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where
last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where
last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where
last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where
last_inserted(news_id);



  IF TG_OP = 'INSERT' THEN

//This is to be done if an INSERT action was done on the table


INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, image_mime_type, layout_type) VALUES
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);



  ELSEIF  TG_OP = 'UPDATE' THEN

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS

DONE



   END IF;

RETURN NEW;

END
';

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

   http://archives.postgresql.org/





--
William Leite Araújo
Analista de Banco de Dados - QualiConsult

[GENERAL] Having a problem with my stored procedure

[Laura McCord]

To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action. 

The only problem that I am facing is how to tell the function that I want to 
perform an update if an update occurred and an insert if an insert action 
occurred. I want to have different actions occur depending on if the trigger 
was based on an insert or update. 

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();



CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
 tmp_news_id CHARACTER varying(48);
 tmp_title CHARACTER varying(100);
 tmp_abstract CHARACTER varying(300);
 tmp_news_story TEXT;
 tmp_topic_id CHARACTER varying(10);
 tmp_create_date DATE;
 tmp_author CHARACTER varying(50);
 tmp_begin_date DATE;
 tmp_end_date DATE;
 tmp_priority CHARACTER(1);
 tmp_image_name CHARACTER varying(512);
 tmp_image_mime_type CHARACTER varying(50);
 tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where 
last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where 
last_inserted(news_id);

//This is to be done if an INSERT action was done on the table

INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, image_mime_type, layout_type) VALUES 
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE

RETURN NEW;
END
';

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

   http://archives.postgresql.org/

Re: [GENERAL] PGSQL 8.2.3 Installation problem

[marcelo Cortez]

hi there

same things occurs to me.
 Any body install win32 version with success???

 best regards
 MDC


--- RPK <[EMAIL PROTECTED]> escribió:

> 
> When I run the setup of PGSQL 8.2.3, it displays
> error while initializing
> database cluster. Error displayed is: "Failed to
> execute initdb. Unable to
> set file system permissions".
> 
> I am installing on Windows XP SP2 with
> "administrator" log in.
> -- 
> View this message in context:
>
http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083
> Sent from the PostgreSQL - general mailing list
> archive at Nabble.com.
> 
> 
> ---(end of
> broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 







__ 
Preguntá. Respondé. Descubrí. 
Todo lo que querías saber, y lo que ni imaginabas, 
está en Yahoo! Respuestas (Beta). 
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas 


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

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

[GENERAL] PGSQL 8.2.3 Installation problem

[RPK]

When I run the setup of PGSQL 8.2.3, it displays error while initializing
database cluster. Error displayed is: "Failed to execute initdb. Unable to
set file system permissions".

I am installing on Windows XP SP2 with "administrator" log in.
-- 
View this message in context: 
http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] A view needs at least one column whereas a table doesn't...

[Tom Lane]

"Gurjeet Singh" <[EMAIL PROTECTED]> writes:
> I know the ERROR seems obvious, but then if we allow zero-column tables,
> then why not zero-column views?
> Or should the question be, why do we allow zero-column tables?

Neither one is legal per the SQL spec.  We allow zero-column tables
because otherwise there are unpleasant corner cases for ALTER TABLE DROP
COLUMN, ie, you couldn't drop the last remaining column before adding
another.  But since we don't have ALTER VIEW DROP COLUMN, there's no
comparable argument for violating the spec for views.

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] A view needs at least one column whereas a table doesn't...

[Gurjeet Singh]

The first SQL command throws an error whereas the second one runs fine:

edb=# create schema s1 authorization u1
edb-# create table t1() create view v1 as select * from t1;
ERROR:  view must have at least one column
edb=#
edb=# create schema s1 authorization u1
edb-# create table t1( c1 int ) create view v1 as select * from t1;
CREATE SCHEMA
edb=#

I know the ERROR seems obvious, but then if we allow zero-column tables,
then why not zero-column views?

Or should the question be, why do we allow zero-column tables? (is
inheritance the only reason?)

Can someone provide some explanation for this behaviour?

Best regards,

--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | yahoo }.com
17°29'34.37"N  78°30'59.76"E

Re: [GENERAL] Possible outer join bug with coalesce in 8.2

[Tom Lane]

John McCawley <[EMAIL PROTECTED]> writes:
> What exactly is going on here?

EXPLAIN might shed some light.  However, if you think this is a bug then
you need to provide a self-contained test case.

regards, tom lane

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

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

Re: [GENERAL] PostgreSQL and OpenLdap

[Cristiano Panvel]

It does not appear nothing in log, only in /var/log/message the error
of failed in login.

Feb 13 12:04:16 fns4 postgres[7055]: [4-1] FATAL:  LDAP authentication
failed for user "scott"
Feb 13 12:04:20 fns4 postgres[7056]: [4-1] FATAL:  LDAP authentication
failed for user "scott"
Feb 13 12:04:20 fns4 postgres[7057]: [4-1] FATAL:  LDAP authentication
failed for user "scott"
Feb 13 12:45:57 fns4 postgres[7216]: [4-1] FATAL:  LDAP authentication
failed for user "sflo"
Feb 13 12:46:10 fns4 postgres[7223]: [4-1] FATAL:  LDAP authentication
failed for user "dbadm"

Cris.


On 2/13/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:

Fisrt, you still have not shown us the output in the server log.  That
is still where you will see more information about why it's failing.

Second, if that's what you want, you should probably try:
ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;uid=;ou=users,

Or something along that line.

//Magnus

On Tue, Feb 13, 2007 at 11:49:54AM -0200, Cristiano Panvel wrote:
> I am not trying thus passed the usuary, and this users does not
> function is active and functioned in the OpenLdap.
>
> ldap ldap://ldap.cb.sc.gov.br/uid=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br
>
> something is very strange
>
> Cris.
>
> On 2/13/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> >On Mon, Feb 12, 2007 at 09:41:44PM -0200, Cristiano Panvel wrote:
> >> Thanks Magnus,
> >>
> >> Now I am passing the line thus pg_hba.conf
> >>
> >> ##
> >> hostall all 10.193.4.0/24  ldap
> >> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br
> >> ##
> >>
> >> However error in login is occurring
> >>
> >> % psql postgresql -h server -U scott -W
> >> psql: FATAL:  LDAP authentication failed for user "scott"
> >
> >That's one step further. Now is the time you need to check the server
> >log.
> >
> >> I must pass plus some thing.
> >>
> >> My base Ldap:
> >> dc=cb,dc=sc,dc=gov,dc=br
> >>
> >> The Bind:
> >> ou=Users
> >
> >Uh, you want to bind as an OU? That's not really possible? Or do you
> >mean that your actual bind shuold be as something like
> >cn=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br? If so, try something like
> >ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;;ou=users,
> >
> >(note the double semicolons - the first one is for the prefix, the
> >second one for suffix)
> >
> >But to re-iterate, your server log shuold contain more information about
> >the problem.
> >
> >//Magnus
> >
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings



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

Re: [GENERAL] Function, that returns set of 2 tables columns

[Willy-Bas Loos]

Creating a view might work.

Yeah, if all you use is SELECT, you should probably use a view, then you
don't need to specify the output columns in the calling statement, only a
WHERE clause.
Othrewise, it's the same thing:
SELECT * FROM myview WHERE field1=10;

Views are transparant in postgresql, so you need not worry that at first
your backend will load the whole view and then filter your results
(performance-wise).

On 2/13/07, Ron Johnson <[EMAIL PROTECTED]> wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/13/07 07:46, Dmitriy Chumack wrote:
> Hi *
>
>   I need to write a function, that returns a set of all columns from 2
>   tables.
[snip]
> for i in select * from "Table1", "Table2"
> loop
>return next i;
> end loop;
[snip]
>
>   This two tables have about 20 columns together, so I don't want list
>   them each I call this function. Can I achieve this in some other
>   (right) way?
>
>   P.S. I don't want to create a specific type for this purpose, but
>   if there is no other way, I should.

What do these tables join on?

Creating a view might work.

CREATE VIEW V_SIDE_BY_SIDE AS
SELECT T1.*, T2.*
FROM TABLE1 T1,
 TABLE2 T2
WHERE T1.PK = T2.PK;

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0ck4S9HxQb37XmcRAjPEAJ9qaHwa5mbKiFVqcfEZbPXIX7GmIwCgzvvs
zCPM45xZ590kv2xXnIoSsqM=
=NkbJ
-END PGP SIGNATURE-

---(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] Keyword OWNED not recognized in pg v. 8.1

[Peter Eisentraut]

Raymond O'Donnell wrote:
> For what it's worth, I've always found it easier to compile from
> source than to try and grapple with packet managers, both under
> RedHat and Ubuntu, and I'm no Linux expert on Ubuntu in
> particular, apt-get seems to scatter files around the hard disk in
> all sorts of non-standard places.

FWIW, I'm pretty sure both Red Hat and Ubuntu, following the LSB and 
all, scatter their files in a decidedly standard way.  Of course you 
are not required to like that way, but then you will have similar 
issues with most Linux binary packages.  If you have particular and 
well-founded gripes with the package layout, a bug report would also 
work.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

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

Re: [GENERAL] PostgreSQL and OpenLdap

[Magnus Hagander]

Fisrt, you still have not shown us the output in the server log.  That
is still where you will see more information about why it's failing.

Second, if that's what you want, you should probably try:
ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;uid=;ou=users,

Or something along that line.

//Magnus

On Tue, Feb 13, 2007 at 11:49:54AM -0200, Cristiano Panvel wrote:
> I am not trying thus passed the usuary, and this users does not
> function is active and functioned in the OpenLdap.
> 
> ldap ldap://ldap.cb.sc.gov.br/uid=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br
>   
> something is very strange
> 
> Cris.
> 
> On 2/13/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> >On Mon, Feb 12, 2007 at 09:41:44PM -0200, Cristiano Panvel wrote:
> >> Thanks Magnus,
> >>
> >> Now I am passing the line thus pg_hba.conf
> >>
> >> ##
> >> hostall all 10.193.4.0/24  ldap
> >> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br
> >> ##
> >>
> >> However error in login is occurring
> >>
> >> % psql postgresql -h server -U scott -W
> >> psql: FATAL:  LDAP authentication failed for user "scott"
> >
> >That's one step further. Now is the time you need to check the server
> >log.
> >
> >> I must pass plus some thing.
> >>
> >> My base Ldap:
> >> dc=cb,dc=sc,dc=gov,dc=br
> >>
> >> The Bind:
> >> ou=Users
> >
> >Uh, you want to bind as an OU? That's not really possible? Or do you
> >mean that your actual bind shuold be as something like
> >cn=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br? If so, try something like
> >ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;;ou=users,
> >
> >(note the double semicolons - the first one is for the prefix, the
> >second one for suffix)
> >
> >But to re-iterate, your server log shuold contain more information about
> >the problem.
> >
> >//Magnus
> >
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

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

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

[GENERAL] Possible outer join bug with coalesce in 8.2

[John McCawley]

My development machine is PostgreSQL 8.1.5, and my production machine is 
PostgreSQL 8.2.  Until now I haven't run into any differences in 
behavior.  I have a query with a relatively wacky join, and while it was 
working on my development machine, it wouldn't work on the production 
machine.  The query is as follows:


SELECT tbl_claim.claimnum
FROM tbl_claim INNER JOIN vw_evaldate ON tbl_claim.claim_id = 
vw_evaldate.claim_id
LEFT OUTER JOIN tbl_claimbatchitem ON tbl_claim.claim_id = 
tbl_claimbatchitem.claim_id

LEFT OUTER JOIN
tbl_claimbatch ON (tbl_claimbatchitem.claimbatch_id = 
tbl_claimbatch.claimbatch_id AND coalesce(tbl_claimbatch.complete,0) = 0 )
LEFT OUTER JOIN tbl_subcontractor ON tbl_claim.subcontractor_id = 
tbl_subcontractor.subcontractor_id
LEFT OUTER JOIN tbl_claimstate ON tbl_claim.claimstate_id = 
tbl_claimstate.claimstate_id

LEFT OUTER JOIN tbl_employee ON tbl_claim.emp_id = tbl_employee.emp_id
LEFT OUTER JOIN tblworkorder ON tbl_claim.claimnum = tblworkorder.claimnum
LEFT OUTER JOIN tbl_claimqc ON tbl_claim.claim_id = tbl_claimqc.claim_id
WHERE tbl_claimbatch.claimbatch_id IS NULL AND tbl_claim.claimnum LIKE 
'%foo%'
ORDER BY tbl_claim.subcontractor_id, emp_lname, emp_fname, addr_zip, 
claimnum LIMIT 3


The basic idea is that if a record is found in tbl_claimbatchitem for 
the claimnum like '%foo%', it will not return a record unless the 
corresponding active column in tbl_claimbatch is 0 or null.  This query 
worked as expected on my 8.1.5 box, but not on the 8.2 box.  After some 
poking, I discovered that it was the coalesce.  I modified the query to:


SELECT tbl_claim.claimnum
FROM tbl_claim INNER JOIN vw_evaldate ON tbl_claim.claim_id = 
vw_evaldate.claim_id
LEFT OUTER JOIN tbl_claimbatchitem ON tbl_claim.claim_id = 
tbl_claimbatchitem.claim_id

LEFT OUTER JOIN
tbl_claimbatch ON (tbl_claimbatchitem.claimbatch_id = 
tbl_claimbatch.claimbatch_id AND tbl_claimbatch.complete = 0 )
LEFT OUTER JOIN tbl_subcontractor ON tbl_claim.subcontractor_id = 
tbl_subcontractor.subcontractor_id
LEFT OUTER JOIN tbl_claimstate ON tbl_claim.claimstate_id = 
tbl_claimstate.claimstate_id

LEFT OUTER JOIN tbl_employee ON tbl_claim.emp_id = tbl_employee.emp_id
LEFT OUTER JOIN tblworkorder ON tbl_claim.claimnum = tblworkorder.claimnum
LEFT OUTER JOIN tbl_claimqc ON tbl_claim.claim_id = tbl_claimqc.claim_id
WHERE tbl_claimbatch.claimbatch_id IS NULL AND tbl_claim.claimnum LIKE 
'%foo%'
ORDER BY tbl_claim.subcontractor_id, emp_lname, emp_fname, addr_zip, 
claimnum LIMIT 3



Which is just a removal of the coalesce, and the query works on both 
boxes.  (I didn't have any nulls in the column anyway).



What exactly is going on here?


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

Re: [GENERAL] Function, that returns set of 2 tables columns

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/13/07 07:46, Dmitriy Chumack wrote:
> Hi *
> 
>   I need to write a function, that returns a set of all columns from 2
>   tables.
[snip]
> for i in select * from "Table1", "Table2"
> loop
>return next i;
> end loop;
[snip]
> 
>   This two tables have about 20 columns together, so I don't want list
>   them each I call this function. Can I achieve this in some other
>   (right) way?
> 
>   P.S. I don't want to create a specific type for this purpose, but
>   if there is no other way, I should.

What do these tables join on?

Creating a view might work.

CREATE VIEW V_SIDE_BY_SIDE AS
SELECT T1.*, T2.*
FROM TABLE1 T1,
 TABLE2 T2
WHERE T1.PK = T2.PK;

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0ck4S9HxQb37XmcRAjPEAJ9qaHwa5mbKiFVqcfEZbPXIX7GmIwCgzvvs
zCPM45xZ590kv2xXnIoSsqM=
=NkbJ
-END PGP SIGNATURE-

---(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] Function, that returns set of 2 tables columns

[A. Kretschmer]

am  Tue, dem 13.02.2007, um 16:09:16 +0200 mailte Dmitriy Chumack folgendes:
> Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer:
> 
> > am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
> >> Hi *
> >> 
> >>   I need to write a function, that returns a set of all columns from 2
> >>   tables.
> >> 
> >>   e.g. I create such a function:
> >> 
> >> CREATE OR REPLACE FUNCTION func(val_ int8)
> >>   RETURNS SETOF record AS
> >> $BODY$
> >> DECLARE
> >> i record;
> >> BEGIN
> >> 
> >> for i in select * from "Table1", "Table2"
> 
> > Use something like:
> 
> > select * from table1 union all select * from table2
> 
> 
> > Andreas
> 
> 
> But how this solve my problem? I don't need standalone select, I need
> this select to be wrapped in function, and function has to return some
> type.

Do you need the columns from both tables in one single column? Then you
have to join this tables, i don't know how you want to join this tables.

What i meant was:

you can use this:

for i in select * from "Table1" union all select * from "Table2" loop
  -- do something
end loop;

within a pl/pgsql-function.



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] Keyword OWNED not recognized in pg v. 8.1

[Raymond O'Donnell]

dfx wrote:


I tryied also with .rpm downloaded from postgres official site but I get a
lot of unresolved dependencies.

So I ask if it exist a document that explain step by step the process to
upgrade v. 8.1 to v. 8.2 under Fedora Core 6, a document for novices, as I
am.


For what it's worth, I've always found it easier to compile from source 
than to try and grapple with packet managers, both under RedHat and 
Ubuntu, and I'm no Linux expert on Ubuntu in particular, apt-get 
seems to scatter files around the hard disk in all sorts of non-standard 
places.


The installation instructions in the docs are very comprehensive and 
easy to follow, and as long as you make sure that you have all the 
requirements (readline and zlib in particular) you should have no problem.


Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---


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

  http://archives.postgresql.org/

Re: [GENERAL] Function, that returns set of 2 tables columns

[Dmitriy Chumack]

Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer:

> am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
>> Hi *
>> 
>>   I need to write a function, that returns a set of all columns from 2
>>   tables.
>> 
>>   e.g. I create such a function:
>> 
>> CREATE OR REPLACE FUNCTION func(val_ int8)
>>   RETURNS SETOF record AS
>> $BODY$
>> DECLARE
>> i record;
>> BEGIN
>> 
>> for i in select * from "Table1", "Table2"

> Use something like:

> select * from table1 union all select * from table2


> Andreas


But how this solve my problem? I don't need standalone select, I need
this select to be wrapped in function, and function has to return some
type.


-- 
Best regards,
 Dmitriymailto:[EMAIL PROTECTED]


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

Re: [GENERAL] Function, that returns set of 2 tables columns

[Willy-Bas Loos]

You need to specify and cast explicitly from your calling statement: SELECT
* FROM func1(10) AS (col1 smallint, col2 bigint, col3 date);



On 2/13/07, Dmitriy Chumack <[EMAIL PROTECTED]> wrote:


Hi *

  I need to write a function, that returns a set of all columns from 2
  tables.

  e.g. I create such a function:

CREATE OR REPLACE FUNCTION func(val_ int8)
  RETURNS SETOF record AS
$BODY$
DECLARE
i record;
BEGIN

for i in select * from "Table1", "Table2"
loop
   return next i;
end loop;

return;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION func(val_ int8) OWNER TO postgres;

  But when I try to call it like this:

SELECT * FROM func1(10);

  I have an error:

  ERROR:  a column definition list is required for functions returning
  "record"

  This two tables have about 20 columns together, so I don't want list
  them each I call this function. Can I achieve this in some other
  (right) way?

  P.S. I don't want to create a specific type for this purpose, but
  if there is no other way, I should.

Thanks in advance.

--
Best regards,
Dmitriy Chumack   mailto:[EMAIL PROTECTED]


---(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] Function, that returns set of 2 tables columns

[A. Kretschmer]

am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
> Hi *
> 
>   I need to write a function, that returns a set of all columns from 2
>   tables.
> 
>   e.g. I create such a function:
> 
> CREATE OR REPLACE FUNCTION func(val_ int8)
>   RETURNS SETOF record AS
> $BODY$
> DECLARE
> i record;
> BEGIN
> 
> for i in select * from "Table1", "Table2"

Use something like:

select * from table1 union all select * from table2


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] PostgreSQL and OpenLdap

[Cristiano Panvel]

I am not trying thus passed the usuary, and this users does not
function is active and functioned in the OpenLdap.

ldap ldap://ldap.cb.sc.gov.br/uid=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br

something is very strange

Cris.

On 2/13/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:

On Mon, Feb 12, 2007 at 09:41:44PM -0200, Cristiano Panvel wrote:
> Thanks Magnus,
>
> Now I am passing the line thus pg_hba.conf
>
> ##
> hostall all 10.193.4.0/24  ldap
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br
> ##
>
> However error in login is occurring
>
> % psql postgresql -h server -U scott -W
> psql: FATAL:  LDAP authentication failed for user "scott"

That's one step further. Now is the time you need to check the server
log.

> I must pass plus some thing.
>
> My base Ldap:
> dc=cb,dc=sc,dc=gov,dc=br
>
> The Bind:
> ou=Users

Uh, you want to bind as an OU? That's not really possible? Or do you
mean that your actual bind shuold be as something like
cn=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br? If so, try something like
ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;;ou=users,

(note the double semicolons - the first one is for the prefix, the
second one for suffix)

But to re-iterate, your server log shuold contain more information about
the problem.

//Magnus



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

[GENERAL] Function, that returns set of 2 tables columns

[Dmitriy Chumack]

Hi *

  I need to write a function, that returns a set of all columns from 2
  tables.

  e.g. I create such a function:

CREATE OR REPLACE FUNCTION func(val_ int8)
  RETURNS SETOF record AS
$BODY$
DECLARE
i record;
BEGIN

for i in select * from "Table1", "Table2"
loop
   return next i;
end loop;

return;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION func(val_ int8) OWNER TO postgres;

  But when I try to call it like this:

SELECT * FROM func1(10);

  I have an error:

  ERROR:  a column definition list is required for functions returning
  "record"

  This two tables have about 20 columns together, so I don't want list
  them each I call this function. Can I achieve this in some other
  (right) way?

  P.S. I don't want to create a specific type for this purpose, but
  if there is no other way, I should.

Thanks in advance.

-- 
Best regards,
 Dmitriy Chumack   mailto:[EMAIL PROTECTED]


---(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] missing FROM-clause (more)

[A. Kretschmer]

am  Tue, dem 13.02.2007, um  9:46:26 -0300 mailte MaRCeLO PeReiRA folgendes:
> Hi guys,
> 
> (1) I change postgresql.conf:
> 
> add_missing_from = off
> 
> (2) pg_ctl stop
> (3) pg_ctl start
> 
> Without success!! The warning is still there!! :(
> 
> Any ideas

Change it to 'yes' ;-)

And, of course, change your SQL-Syntax.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] daylight savings patches needed?

[Jorge Godoy]

Joseph Shraibman  writes:

> Robert Treat wrote:
>
>> If you are running pre-8.0 versions you need to update your operating system
>> (as you indicated).  If you running an any 8.x version, you need to be on
>> the most current corresponding 8.x.y release.
>>
> So what happens if you have an old os with a new postgresql install? Will
> CURRENT_TIMESTAMP always return the correct value even if the system 'date'
> command is showing the wrong time?

This is something that isn't always good...  If your country changes the rules
to when DST starts / ends and PostgreSQL doesn't release a new version with
the changes (or a patch or...) then you would have to change this in two
places: your OS rules and PG's rules for timezones / DST / whatever.

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

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

   http://archives.postgresql.org/

[GENERAL] missing FROM-clause (more)

[MaRCeLO PeReiRA]

Hi guys,

(1) I change postgresql.conf:

add_missing_from = off

(2) pg_ctl stop
(3) pg_ctl start

Without success!! The warning is still there!! :(

Any ideas

Thanks in advance,

Marcelo Pereira

__
Fale com seus amigos  de graça com o novo Yahoo! Messenger 
http://br.messenger.yahoo.com/ 

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

[GENERAL] missing FROM-clause

[MaRCeLO PeReiRA]

Hi guys,

I upgraded my PostgreSQL server (7.4 to 8.2) and now
all my reports refuse to run because the warning
"missing FROM-clause".

How can I disable it, just to run as the old version??

I have tried:

# set add_missing_from to false

but, without success!! :(

The warning is still there!!

Thanks in advance,

Marcelo Pereira

__
Fale com seus amigos  de graça com o novo Yahoo! Messenger 
http://br.messenger.yahoo.com/ 

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

   http://archives.postgresql.org/

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

[Pavan Deolasee]

On 2/13/07, Walter Vaughan <[EMAIL PROTECTED]> wrote:



select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
  last_autovacuum | last_autoanalyze
-+--
  |
...snip lots of identically blank lines...
  |
  |
(939 rows)

Does that mean it's working or not configured right?



It means auto vacuum/analyze did not trigger on any of the
tables. You may want to try:

SELECT name, setting from pg_settings where name like  '%autovacuum%';

to get the settings of autovacuum and check if autovacuum is turned on or
not.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] How to append tables in a view

[Alban Hertroys]

Shoaib Mir wrote:
> So hmm a UNION with an ORDERY BY should be good for this scenario...

Only if the order matters to the OP, but he can always perform an ORDER
BY on the queries on his view. I don't really see the point.

The main difference between UNION and UINION ALL is that the latter
allows for duplicates, which removes the need to unduplicate the results
of the UNION (which requires ordering and is therefore relatively
expensive).

In short; if duplicates don't matter (or are desirtable even) UNION ALL
is faster.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] How to append tables in a view

[Shoaib Mir]

So hmm a UNION with an ORDERY BY should be good for this scenario...

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, Shane Ambler <[EMAIL PROTECTED]> wrote:


Shoaib Mir wrote:
> I guess UNION ALL should work good here instead of a UNION for the exact
> same kind of output he needs:

That would be UNION ordering the results to remove duplicate rows which
UNION ALL doesn't do. Technically the results from any query can come
back in any order unless an ORDER BY is included.

> SELECT id,desc FROM table1
> UNION ALL
> SELECT id,desc FROM table2;
>
> ---+---
> 1 | A
> 2 | B
> 3 | C
> 1 | D
> 2 | E
> 3 | F
>
>
> As UNION gave me a little different output, like this:
>
> ---+--
> 1 | A
> 1 | D
> 2 | B
> 2 | E
> 3 | C
> 3 | F
>
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 2/13/07, Shane Ambler <[EMAIL PROTECTED]> wrote:
>>
>> Ashish Karalkar wrote:
>> > Hello List,
>> >
>> > I want to append column of two different tables in a single column
>> of  a
>> view .
>> >
>> > data type of tow column of two diffrent tables will be same.
>> >
>> > WHAT I WANT TO DO IS:
>> >
>> >   Table 1
>> >   ID DESC
>> >   1 A
>> >   2 B
>> >   3 C
>> >
>> >
>> >
>> >
>> >
>> >   Table 2
>> >   ID DESC
>> >   1 D
>> >   2 E
>> >   3 F
>> >
>> >
>> >
>> >   View(Table1|| Table 2)
>> >   ID_view Desc
>> >
>> >   1 A
>> >   2 B
>> >   3 C
>> >   4 D
>> >   5 E
>> >   6 F
>> >
>> >
>> >
>> > Is there any way???
>>
>> A union -
>>
>> SELECT id,desc FROM table1
>> UNION
>> SELECT id,desc FROM table2;
>>
>> This will give you
>>
>> ID_view Desc
>>
>> 1 A
>> 2 B
>> 3 C
>> 1 D
>> 2 E
>> 3 F
>>
>>
>> If you actually want the id_view column to show 1 through 6 then you
>> will want to generate a sequence that is shown for that column instead
>> of the original id column. Or generate the id_view in the client, such
>> as use the row position in the returned set.
>>
>>
>>
>>
>> --
>>
>> Shane Ambler
>> [EMAIL PROTECTED]
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[hubert depesz lubaczewski]

On 2/12/07, Bill Moseley <[EMAIL PROTECTED]> wrote:


Also, the Nested Sets seem to solve problems I don't have -- such as
finding all descendants of a given node.



you can also check different way.
i described it here:
http://www.depesz.com/various/various-sqltrees-implementation.php
it is in polish, but it's full of examples and should be readable even for
somebody with 0 knowledge of polish.
basically - this is extension of adjacency list which allows all kinds of
access without recursive queries.

in case you'd have questions with this - please do not hesitate to ask,

best regards,

depesz

Re: [GENERAL] How to append tables in a view

[Shane Ambler]

Shoaib Mir wrote:

I guess UNION ALL should work good here instead of a UNION for the exact
same kind of output he needs:


That would be UNION ordering the results to remove duplicate rows which 
UNION ALL doesn't do. Technically the results from any query can come 
back in any order unless an ORDER BY is included.



SELECT id,desc FROM table1
UNION ALL
SELECT id,desc FROM table2;

---+---
1 | A
2 | B
3 | C
1 | D
2 | E
3 | F


As UNION gave me a little different output, like this:

---+--
1 | A
1 | D
2 | B
2 | E
3 | C
3 | F

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, Shane Ambler <[EMAIL PROTECTED]> wrote:


Ashish Karalkar wrote:
> Hello List,
>
> I want to append column of two different tables in a single column 
of  a

view .
>
> data type of tow column of two diffrent tables will be same.
>
> WHAT I WANT TO DO IS:
>
>   Table 1
>   ID DESC
>   1 A
>   2 B
>   3 C
>
>
>
>
>
>   Table 2
>   ID DESC
>   1 D
>   2 E
>   3 F
>
>
>
>   View(Table1|| Table 2)
>   ID_view Desc
>
>   1 A
>   2 B
>   3 C
>   4 D
>   5 E
>   6 F
>
>
>
> Is there any way???

A union -

SELECT id,desc FROM table1
UNION
SELECT id,desc FROM table2;

This will give you

ID_view Desc

1 A
2 B
3 C
1 D
2 E
3 F


If you actually want the id_view column to show 1 through 6 then you
will want to generate a sequence that is shown for that column instead
of the original id column. Or generate the id_view in the client, such
as use the row position in the returned set.




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz






--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] How to append tables in a view

[Shoaib Mir]

I guess UNION ALL should work good here instead of a UNION for the exact
same kind of output he needs:

SELECT id,desc FROM table1
UNION ALL
SELECT id,desc FROM table2;

---+---
1 | A
2 | B
3 | C
1 | D
2 | E
3 | F


As UNION gave me a little different output, like this:

---+--
1 | A
1 | D
2 | B
2 | E
3 | C
3 | F

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, Shane Ambler <[EMAIL PROTECTED]> wrote:


Ashish Karalkar wrote:
> Hello List,
>
> I want to append column of two different tables in a single column of  a
view .
>
> data type of tow column of two diffrent tables will be same.
>
> WHAT I WANT TO DO IS:
>
>   Table 1
>   ID DESC
>   1 A
>   2 B
>   3 C
>
>
>
>
>
>   Table 2
>   ID DESC
>   1 D
>   2 E
>   3 F
>
>
>
>   View(Table1|| Table 2)
>   ID_view Desc
>
>   1 A
>   2 B
>   3 C
>   4 D
>   5 E
>   6 F
>
>
>
> Is there any way???

A union -

SELECT id,desc FROM table1
UNION
SELECT id,desc FROM table2;

This will give you

ID_view Desc

1 A
2 B
3 C
1 D
2 E
3 F


If you actually want the id_view column to show 1 through 6 then you
will want to generate a sequence that is shown for that column instead
of the original id column. Or generate the id_view in the client, such
as use the row position in the returned set.




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

Re: [GENERAL] How to append tables in a view

[Shane Ambler]

Ashish Karalkar wrote:

Hello List,

I want to append column of two different tables in a single column of  a view .

data type of tow column of two diffrent tables will be same.

WHAT I WANT TO DO IS:

  Table 1 
  ID DESC 
  1 A 
  2 B 
  3 C 
  
  
  
  
  
  Table 2 
  ID DESC 
  1 D 
  2 E 
  3 F 
  
  
  
  View(Table1|| Table 2) 
  ID_view Desc 
  
  1 A 
  2 B 
  3 C 
  4 D 
  5 E 
  6 F 
  



Is there any way???


A union -

SELECT id,desc FROM table1
UNION
SELECT id,desc FROM table2;

This will give you

ID_view Desc

1 A
2 B
3 C
1 D
2 E
3 F


If you actually want the id_view column to show 1 through 6 then you 
will want to generate a sequence that is shown for that column instead 
of the original id column. Or generate the id_view in the client, such 
as use the row position in the returned set.





--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

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

Re: [GENERAL] How to append tables in a view

[Alban Hertroys]

Ashish Karalkar wrote:
> Hello List,
> 
> I want to append column of two different tables in a single column of  a view 
> .
> 
> data type of tow column of two diffrent tables will be same.
> 
> WHAT I WANT TO DO IS:
> 
>   Table 1 
>   ID DESC 
>   1 A 
>   2 B 
>   3 C 
>   
>   Table 2 
>   ID DESC 
>   1 D 
>   2 E 
>   3 F 
>   
>   View(Table1|| Table 2) 
>   ID_view Desc 
>   
>   1 A 
>   2 B 
>   3 C 
>   4 D 
>   5 E 
>   6 F 
> 
> Is there any way???

Looks like a UNION ALL to me.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] Testing embedded SQL in C

[Shane Ambler]

HT NB wrote:

Hi,

How are you doing?

First, I am testing if this email address is valid. It is the first time that I 
am using this account. I have a question about how to start write embedded SQL 
in C programming code. What are the basic requirements in the .pgc file to have 
the embedded SQL running.


This is my coding which is aimed at testing if embedded SQL in C programming 
code is possible for my operating system Debian GNU/Linux.


#include 


int main ( )
{

EXEC SQL CONNECT TO [EMAIL PROTECTED] address}.com  :80;


try -
EXEC SQL CONNECT TO [EMAIL PROTECTED] address}:5432 USER mylogin;

The one thing from your code I would question is your specifying port 
80. That is the port for a web server not postgresql. Can you access the 
postgresql server directly or are you trying to get to it through a web 
server? If you are going through the web server then embedded sql won't 
work, you will need to deal with http requests and responses.



EXEC SQL INSERT INTO database (tab_number) VALUES (4);
EXEC SQL SELECT * FROM database;

return 1;

}




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] Adjacency List or Nested Sets to model file system hierarchy?

[Alban Hertroys]

Bill Moseley wrote:
> On Mon, Feb 12, 2007 at 05:36:37PM +, Ian Harding wrote:
>> You don't mention the ltree contrib module, have you looked at it?  It
>> can easily meet your requirements without having to reinvent anything.
>> It may be what you're referring to as Nested Sets, I don't know.  I
>> use it and like it a lot.
> 
> Yes, I have seen it.  I just thought it seemed like a very large
> "hammer" to use form my task -- quite a few more query methods than I
> need .  But, perhaps I should look at it again and get a better
> understanding of what it can do.

What it can do is add indexed paths to your records ;)

You will still have to check consistency on updates to paths (fe. if the
root node changes name, all nodes need updating) though, but that's true
for any method including the entire path. And on delete (remove child
nodes as well) and insert (check parents existence), of course.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

[GENERAL] How to append tables in a view

[Ashish Karalkar]

Hello List,

I want to append column of two different tables in a single column of  a view .

data type of tow column of two diffrent tables will be same.

WHAT I WANT TO DO IS:

  Table 1 
  ID DESC 
  1 A 
  2 B 
  3 C 
  
  
  
  
  
  Table 2 
  ID DESC 
  1 D 
  2 E 
  3 F 
  
  
  
  View(Table1|| Table 2) 
  ID_view Desc 
  
  1 A 
  2 B 
  3 C 
  4 D 
  5 E 
  6 F 
  


Is there any way???

Thanks in advance
Ashish...

Re: [GENERAL] PostgreSQL and OpenLdap

[Magnus Hagander]

On Mon, Feb 12, 2007 at 09:41:44PM -0200, Cristiano Panvel wrote:
> Thanks Magnus,
>   
> Now I am passing the line thus pg_hba.conf
> 
> ##
> hostall all 10.193.4.0/24  ldap
> ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br
> ##
> 
> However error in login is occurring
> 
> % psql postgresql -h server -U scott -W
> psql: FATAL:  LDAP authentication failed for user "scott"

That's one step further. Now is the time you need to check the server
log.

> I must pass plus some thing.
> 
> My base Ldap:
> dc=cb,dc=sc,dc=gov,dc=br
> 
> The Bind:
> ou=Users

Uh, you want to bind as an OU? That's not really possible? Or do you
mean that your actual bind shuold be as something like
cn=scott,ou=Users,dc=cb,dc=sc,dc=gov,dc=br? If so, try something like
ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br;;ou=users,

(note the double semicolons - the first one is for the prefix, the
second one for suffix)

But to re-iterate, your server log shuold contain more information about
the problem.

//Magnus

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

[GENERAL] Keyword OWNED not recognized in pg v. 8.1

[dfx]

Thank you Tom,

I tryied in any way to upgrade to 8.2 but I get only v. 8.1.8 (with yum).

I tryied also with .rpm downloaded from postgres official site but I get a
lot of unresolved dependencies.

So I ask if it exist a document that explain step by step the process to
upgrade v. 8.1 to v. 8.2 under Fedora Core 6, a document for novices, as I
am.

Thank you again.

Domenico


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

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