Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Chris Mair]

 I have OS X tiger with all the updates:
 
 uname -r
 8.8.0
 
 Here is what I get when I try to initdb on a freshly compiled 8.2:
 
 selecting default max_connections ... 10
 selecting default shared_buffers/max_fsm_pages ... 400kB/2
 creating configuration files ... ok
 creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:
 could not create shared memory segment: Cannot allocate memory
 DETAIL:  Failed system call was shmget(key=2, size=1646592, 03600).
 HINT:  This error usually means that PostgreSQL's request for a shared
 memory segment exceeded available memory or swap space. To reduce the
 request size (currently 1646592 bytes), reduce PostgreSQL's
 shared_buffers parameter (currently 50) and/or its max_connections
 parameter (currently 10).
 The PostgreSQL documentation contains more information about
 shared memory configuration.
 child process exited with exit code 1
 

Works for me :|
(see initdb output below)...

 
 I read the documentation
 (http://www.postgresql.org/docs/8.2/static/kernel-resources.html) and
 added the appropriate items to /etc/sysctl.conf, and I rebooted for it
 to take effect.
 
 cat /etc/sysctl.conf
 kern.sysv.shmmax=4194304
 kern.sysv.shmmin=1
 kern.sysv.shmmni=32
 kern.sysv.shmseg=8
 kern.sysv.shmall=1024

Can you check whether the settings worked? Do:

ibook:~ chris$ sysctl -a | grep shm
kern.sysv.shmmax: 4194304
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024

Bye,
Chris.

-
ibook:/opt/pg chris$ initdb
The files belonging to this database system will be owned by user chris.
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /opt/pg/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 30
selecting default shared_buffers/max_fsm_pages ... 2400kB/2
creating configuration files ... ok
creating template1 database in /opt/pg/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling trust authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

postgres -D /opt/pg/data
or
pg_ctl -D /opt/pg/data -l logfile start

ibook:/opt/pg chris$ uname -r
8.8.0
-


---(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] Re: [GENERAL] 答复: [GENERAL] Need Help

[Shoaib Mir]

This should help you get the disk usage for a table:

select pg_size_pretty(pg_relation_size('tablename'));

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

On 12/28/06, 马庆 [EMAIL PROTECTED] wrote:


Maybe U can't get the disk usage info, but you can try command explain


-邮件原件-
发件人: [EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED] 代表 JM
发送时间: 2006年12月28日 10:43 MARK
收件人: pgsql-general@postgresql.org
抄送: Pgsql-Performance (E-mail)
主题: [GENERAL] Need Help

how can i get the disk usage for each table? can i do it via SQL?



Thanks,

Mailing-Lists

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

   http://archives.postgresql.org/


---(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] convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.

[Alexander Farber]

Hello Alvaro,

On 12/24/06, Alvaro Herrera [EMAIL PROTECTED] wrote:

Alexander Farber wrote:
 I started to prepare a test case and realized I had a bug.
 So convert() works for me, sorry for my previous message!

In any case, it's probably saner if you SET client_encoding at the start
of the session instead of using convert() everywhere.  The
server_encoding should be correctly set to Win1251 though!  (Using
SQL_ASCII, while not technically incorrect, is probably not doing you
any favor).


thanks for your comment! I've dropped my db
and recreated it with encoding set to WIN1251:

pref:afarber psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
.
phpbb= \l+
   List of databases
  Name|Owner| Encoding  |Description
---+-+---+---
phpbb | _postgresql | WIN1251   |
postgres  | _postgresql | SQL_ASCII |
template0 | _postgresql | SQL_ASCII |
template1 | _postgresql | SQL_ASCII | Default template database
(4 rows)

phpbb= show client_encoding;
client_encoding
-
WIN1251
(1 row)

Seems to work ok... My prepared query seems to work too:

#define SQL_FETCH_USER  \
   select convert(username using windows_1251_to_utf8),  \
   user_avatar from phpbb_users where user_active = 1  \
   and user_id = $1 and user_password = $2 and user_id not in  \
   (select ban_userid from phpbb_banlist where ban_userid is not null)

Regards
Alex


--
http://preferans.de

---(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] Improve response time of a SQL command

[Carlos H. Reimer]

Hi,

I would like to improve the response time of the following SQL command but I
need some help to indentify where is it taking most of the processing time.
It seams that it is in the Seq Scan on tt_gra gra, but not sure. Am I right?

Can a new index help in this issue?

Thank you in advance!


explain analyze
SELECT IPR.REFPRO, IPR.NOMPRO, IPR.MEDUNI,
   IVE.VLRMOV /IVE.QTDMOV AS PRECOV,
   VEN.DESCON, IVE.QTDMOV,
   COALESCE(IVE.TAX001,0) AS ICMS,
   SUBSTR(SIT.DESDOM,1,30) AS SITUACAO,
   IVE.VLRMOV AS TOTITE,
  IOR.FILRES
  FROM TT_IVE IVE LEFT OUTER JOIN TV_IPR IPR ON IPR.FILMAT =
IVE.FILMAT  AND IPR.CODMAT = IVE.CODMAT  AND IPR.CODCOR = IVE.CODCOR  AND
IPR.CODTAM = IVE.CODTAM
  LEFT OUTER JOIN TT_DOM SIT ON SIT.CODARQ = 'IVE'
AND SIT.NOMCPO = 'SITMOV'  AND SIT.CODCHR = IVE.SITMOV
  LEFT OUTER JOIN TT_VEN VEN ON IVE.CODFIL =
VEN.CODFIL  AND IVE.SEQUEN = VEN.SEQUEN
  LEFT OUTER JOIN TT_IOR IOR ON IVE.CODFIL =
IOR.FILIVE AND IVE.SEQUEN = IOR.SEQIVE AND  IVE.NUMITE = IOR.NUMIVE
 WHERE IVE.CODFIL= '001'
   AND IVE.SEQUEN= '113519'

;


  QUERY PLAN


-
---
 Nested Loop Left Join  (cost=2234.61..2326.73 rows=10 width=148) (actual
time=6641.168..6689.295 rows=1 loops=1)
   -  Nested Loop Left Join  (cost=2234.61..2291.17 rows=9 width=172)
(actual time=6630.985..6679.105 rows=1 loops=1)
 -  Hash Left Join  (cost=2234.61..2255.78 rows=9 width=163)
(actual time=6630.873..6678.987 rows=1 loops=1)
   Hash Cond: (outer.sitmov = inner.codchr)
   -  Nested Loop Left Join  (cost=2231.16..2252.28 rows=9
width=154) (actual time=6628.171..6676.282 rows=1 loo
ps=1)
 Join Filter: ((inner.filmat = outer.filmat) AND
(inner.codmat = outer.codmat) AND (inner.codco
r = outer.codcor) AND (inner.codtam = outer.codtam))
 -  Index Scan using pk_ive on tt_ive ive
(cost=0.00..17.88 rows=9 width=98) (actual time=17.450..17.45
5 rows=1 loops=1)
   Index Cond: ((codfil = '001'::bpchar) AND (sequen
= '113519'::bpchar))
 -  Materialize  (cost=2231.16..2231.28 rows=12
width=126) (actual time=3.803..6593.478 rows=32069 loops
=1)
   -  Subquery Scan ipr  (cost=4.69..2231.15
rows=12 width=126) (actual time=3.791..6541.255 rows=32
069 loops=1)
 -  Nested Loop  (cost=4.69..2231.03
rows=12 width=1264) (actual time=3.775..6353.475 rows=3
2069 loops=1)
   -  Hash Join  (cost=4.69..2081.99
rows=11 width=658) (actual time=3.257..1305.769 row
s=32069 loops=1)
 Hash Cond:
((outer.codcor)::text = ((inner.codite)::text || ''::text))
 -  Hash Join
(cost=2.06..2077.23 rows=161 width=613) (actual time=1.386..697.0
22 rows=32069 loops=1)
   Hash Cond:
((outer.codtam)::text = ((inner.codite)::text || ''::text))
   -  Seq Scan on tt_gra
gra  (cost=0.00..1672.14 rows=32114 width=551) (act
ual time=0.047..78.800 rows=32069 loops=1)
   -  Hash
(cost=2.06..2.06 rows=1 width=70) (actual time=0.087..0.087 rows
=0 loops=1)
 -  Nested Loop
(cost=0.00..2.06 rows=1 width=70) (actual time=0.05
2..0.060 rows=1 loops=1)
   Join Filter:
(outer.codsub = inner.codtab)
   -  Seq Scan
on tt_sub sub  (cost=0.00..1.01 rows=1 width=48)
(actual time=0.014..0.015 rows=1 loops=1)
   -  Seq Scan
on td_sub dsub  (cost=0.00..1.02 rows=2 width=31)
 (actual time=0.011..0.015 rows=2 loops=1)
 -  Hash  (cost=2.60..2.60
rows=13 width=54) (actual time=0.321..0.321 rows=0 lo
ops=1)
   -  Merge Join
(cost=2.40..2.60 rows=13 width=54) (actual time=0.219..0.2
66 rows=13 loops=1)
 Merge Cond:
(outer.codtab = inner.coddiv)
 -  Sort
(cost=1.03..1.03 rows=2 width=22) (actual time=0.120..0.12
2 rows=2 loops=1)
   Sort Key:
ddiv.codtab
   -  Seq Scan
on td_div ddiv  (cost=0.00..1.02 

[GENERAL] unique constraint on more than one tables

[Akbar]

Hi,

Imagine I have two tables, like this:

create table bla_a (
 id serial primary key,
 name varchar(31) not null,
 comment varchar(31)
);

create table bla_b (
 id serial primary key,
 name varchar(31) not null,
 blabla int
);

I want to make sure that both tables could not have the same value for
name column. Can I do that?

insert into bla_a ( id, name, comment ) values ( 1, 'bo', 'ha');
insert into bla_b ( id, name, comment ) values ( 1, 'bo', 3);

I want to make the second insertion failed because of unique
constraint. Can I do that?

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

[GENERAL] select union with table name

[Akbar]

Hi, I have two tables.
create table blue (
 id serial primary key,
 name text not null,
 kill text not null
);

create table red (
 id serial primary key,
 name text not null,
 kiss text not null
);

select blue.name from blue union select red.name from red; give me this:
name
'blabla'
'bubu'
'haha'
'kkk'

I want this:
nametable_name
'blabla'   blue
'bubu'blue
'haha'red
'kkk'  red

Could I?

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

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

Re: [GENERAL] select union with table name

[Joris Dobbelsteen]

Try:
select blue.name, 'blue' from blue union select red.name, 'red' from
red;

Not tested, but that should work.
One thing to remember:
If blabla is in both blue and red, it will appear twice, instead of only
once as in your example. 

- Joris

-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Akbar
Sent: donderdag 28 december 2006 13:10
To: pgsql-general@postgresql.org
Subject: [GENERAL] select union with table name

Hi, I have two tables.
create table blue (
  id serial primary key,
  name text not null,
  kill text not null
);

create table red (
  id serial primary key,
  name text not null,
  kiss text not null
);

select blue.name from blue union select red.name from red; 
give me this:
name
'blabla'
'bubu'
'haha'
'kkk'

I want this:
nametable_name
'blabla'   blue
'bubu'blue
'haha'red
'kkk'  red

Could I?

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

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


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

Re: [GENERAL] select union with table name

[Richard Broersma Jr]

 I want this:
 nametable_name
 'blabla'   blue
 'bubu'blue
 'haha'red
 'kkk'  red
 
 Could I?

Here is an example from the table inheritance chapter:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude  500 and c.tableoid = p.oid;

which returns: 

 relname  |   name| altitude
--+---+--
 cities   | Las Vegas | 2174
 cities   | Mariposa  | 1953
 capitals | Madison   |  845

http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html

it should do what you want.

Regards,

Richard Broersma Jr.

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

Re: [GENERAL] Re: [GENERAL] ??: [GENERA

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

What about empty space freed up by VACUUM?

On 12/28/06 03:11, Shoaib Mir wrote:
 This should help you get the disk usage for a table:
 
 select pg_size_pretty(pg_relation_size('tablename'));

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFk8VIS9HxQb37XmcRAsaMAJ9pznXfdZ0YB2pZD7V6dC2H3yp6ogCgxiET
ekRWyRU39ZRC6Y6hpGqpL6w=
=bBml
-END PGP SIGNATURE-

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

FW: [GENERAL] select union with table name

[antsumees]

Try this

Select name, 'blue' as table_name from blue 
Union all
Select name, 'red' as table_name from red

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Akbar
Sent: Thursday, December 28, 2006 2:10 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] select union with table name

Hi, I have two tables.
create table blue (
  id serial primary key,
  name text not null,
  kill text not null
);

create table red (
  id serial primary key,
  name text not null,
  kiss text not null
);

select blue.name from blue union select red.name from red; give me this:
name
'blabla'
'bubu'
'haha'
'kkk'

I want this:
nametable_name
'blabla'   blue
'bubu'blue
'haha'red
'kkk'  red

Could I?

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

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


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

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

Re: [GENERAL] Improve response time of a SQL command

[Tom Lane]

Carlos H. Reimer [EMAIL PROTECTED] writes:
 I would like to improve the response time of the following SQL command

Try getting rid of the silly concatenate-with-empty-string operations
that appear to lurk within some view underlying this command.  Those
seem to be defeating the planner's ability to guess how many join rows
there will be.

  -  Nested Loop  (cost=4.69..2231.03
 rows=12 width=1264) (actual time=3.775..6353.475 rows=32069 loops=1)
-  Hash Join  (cost=4.69..2081.99
 rows=11 width=658) (actual time=3.257..1305.769 rows=32069 loops=1)
  Hash Cond:
 ((outer.codcor)::text = ((inner.codite)::text || ''::text))

It would surely not have used a nestloop here if it had had a better
idea of how many rows would come out of the lower join ... but since it
has no statistics about the result of the concatenate, it's just
guessing about that.

regards, tom lane

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

[GENERAL] LDAP configuration problem

[Wenjian Yang]

Hi,

I've just installed 8.2.0 from rpms on FC6 i386 and wanted to try out the
built-in LDAP support.

The following LDAP authentication was added to pg_hba.conf

host  all  all  0.0.0.0  0.0.0.0
ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\

However when trying to connect from another machine, it gave the error
FATAL: missing or erroneous pg_hba.conf file.

Looking at the server log, the comma in dc=domain,dc=com has become ^A
while all the other characters are intact.

Does this have to do with some other packages or I specified the the line
wrong? Thanks in advance.

Wenjian

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Tom Lane]

Karen Hill [EMAIL PROTECTED] writes:
 I still get the error when I initdb.  OS X and PostgreSQL has worked
 before for me, compiled from the source.

Works for me.  What do you get from sysctl -a | grep sysv ?

regards, tom lane

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

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Karen Hill]

Chris Mair wrote:
  I have OS X tiger with all the updates:
 
  uname -r
  8.8.0
 
  Here is what I get when I try to initdb on a freshly compiled 8.2:
 
  selecting default max_connections ... 10
  selecting default shared_buffers/max_fsm_pages ... 400kB/2
  creating configuration files ... ok
  creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:
  could not create shared memory segment: Cannot allocate memory
  DETAIL:  Failed system call was shmget(key=2, size=1646592, 03600).
  HINT:  This error usually means that PostgreSQL's request for a shared
  memory segment exceeded available memory or swap space. To reduce the
  request size (currently 1646592 bytes), reduce PostgreSQL's
  shared_buffers parameter (currently 50) and/or its max_connections
  parameter (currently 10).
  The PostgreSQL documentation contains more information about
  shared memory configuration.
  child process exited with exit code 1
 

 Works for me :|
 (see initdb output below)...

 
  I read the documentation
  (http://www.postgresql.org/docs/8.2/static/kernel-resources.html) and
  added the appropriate items to /etc/sysctl.conf, and I rebooted for it
  to take effect.
 
  cat /etc/sysctl.conf
  kern.sysv.shmmax=4194304
  kern.sysv.shmmin=1
  kern.sysv.shmmni=32
  kern.sysv.shmseg=8
  kern.sysv.shmall=1024

 Can you check whether the settings worked? Do:

 ibook:~ chris$ sysctl -a | grep shm
 kern.sysv.shmmax: 4194304
 kern.sysv.shmmin: 1
 kern.sysv.shmmni: 32
 kern.sysv.shmseg: 8
 kern.sysv.shmall: 1024

 Bye,
 Chris.

Here is what I get:
sysctl -a | grep shm
kern.sysv.shmmax: 4194304
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024

This is very strange as I just updated OS X with the latest updates and
then compiled and installed 8.2.  I have used OS X Tiger and postgresql
8.1 compiled from source with no problem.


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

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

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Shane Ambler]

Karen Hill wrote:

I have OS X tiger with all the updates:

uname -r
8.8.0

Here is what I get when I try to initdb on a freshly compiled 8.2:

selecting default max_connections ... 10
selecting default shared_buffers/max_fsm_pages ... 400kB/2
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:
could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=2, size=1646592, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space. To reduce the
request size (currently 1646592 bytes), reduce PostgreSQL's
shared_buffers parameter (currently 50) and/or its max_connections
parameter (currently 10).
The PostgreSQL documentation contains more information about
shared memory configuration.
child process exited with exit code 1



do you have another copy of postgresql running when you run initdb?

--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(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] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Karen Hill]

Tom Lane wrote:
 Karen Hill [EMAIL PROTECTED] writes:
  I still get the error when I initdb.  OS X and PostgreSQL has worked
  before for me, compiled from the source.

 Works for me.  What do you get from sysctl -a | grep sysv ?


sysctl -a | grep sysv
kern.sysv.shmmax: 4194304
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024
kern.sysv.semmni: 87381
kern.sysv.semmns: 87381
kern.sysv.semmnu: 87381
kern.sysv.semmsl: 87381
kern.sysv.semume: 10


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

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Scott Ribe]

 and
 added the appropriate items to /etc/sysctl.conf

1) The location may be out of date. Don't add to /etc/sysctl.conf; edit the
values already in /etc/rc. Where you set these values has shifted around
somewhat in OS X releases. I know that sysctl didn't work in 10.3. I know
that rc works in 10.4.

2) The values are out of date. All you're doing with those values is
re-specifying the OS X defaults. With 8.2, IIRC postgres defaults to using
more memory (the old default was extremely frugal), so out of the box you
may need to increase those values rather than only having to increase the OS
X values if you increased the postgres values. Just tack an extra zero onto
the end of shmmax and shmall and postgres will work with its default values.
If you want to increase postgres memory, you may have to revisit these
values.

3) The changes don't take effect until reboot, and often an OS X update will
reset them so you have to be prepared to re-edit /etc/rc.

Finally, the error message you're getting doesn't indicate that postgres is
actually requesting much memory. I suspect that you may have something else
running on your system which is using SysV shared memory. Nothing to worry
about, but something to be aware of when trying to match /etc/rc shm values
to postgres settings.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(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] unique constraint on more than one tables

[George Pavlov]

First of all explicitly inserting IDs into your serial column sounds
like a bad idea (in the general case).

Unfortunately, I don't think inheritance can help you with this. Key
quote from the docs: A serious limitation of the inheritance feature is
that indexes (including unique constraints) and foreign key constraints
only apply to single tables, not to their inheritance children.

So, you can create some triggers. Maybe something like this (untested):

create or replace function tf_nodupes()
  returns trigger as
$body$
begin
  if new.id in (select id from bla_a union select id from bla_b)
  then
raise exception 'a suitable message about dupes goes here';
  end if;
  return new;
end;
$body$
language 'plpgsql';

create trigger t_nodupes 
  before insert on bla_a 
  for each row 
  execute procedure tf_nodupes();

create trigger t_nodupes 
  before insert on bla_b 
  for each row 
  execute procedure tf_nodupes();



 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Akbar
 Sent: Thursday, December 28, 2006 4:01 AM
 To: pgsql-general@postgresql.org
 Subject: [GENERAL] unique constraint on more than one tables
 
 Hi,
 
 Imagine I have two tables, like this:
 
 create table bla_a (
   id serial primary key,
   name varchar(31) not null,
   comment varchar(31)
 );
 
 create table bla_b (
   id serial primary key,
   name varchar(31) not null,
   blabla int
 );
 
 I want to make sure that both tables could not have the same value for
 name column. Can I do that?
 
 insert into bla_a ( id, name, comment ) values ( 1, 'bo', 'ha');
 insert into bla_b ( id, name, comment ) values ( 1, 'bo', 3);
 
 I want to make the second insertion failed because of unique
 constraint. Can I do that?
 

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

[GENERAL] Backup Restore

[Bob Pawley]

PostgreSQL 8.1 running on Win XP. PGAdmin Version 1.6.2

When I backup my database on one computer using PG Admin 3 then attempt to open 
the file on a second computer - PG Admin does NOT recognize the file. The 
documentation isn't helpful, at least for a neophyte such as I.

Can someone point me to a simple instruction set for a backup/restore operation 
using the PG Admin resource??

Bob

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Tom Lane]

Scott Ribe [EMAIL PROTECTED] writes:
 Finally, the error message you're getting doesn't indicate that postgres is
 actually requesting much memory. I suspect that you may have something else
 running on your system which is using SysV shared memory.

Recent versions of OS X have ipcs, so you could try sudo ipcs -m to
find out about that.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Backup Restore

[Shoaib Mir]

Well I haven't use much of PGAdmin but I will always be using pg_dump and
pg_restore for that as they are really easy to use.

You can find help on backup and restore at --
http://www.postgresql.org/docs/8.2/static/backup.html


Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 12/28/06, Bob Pawley [EMAIL PROTECTED] wrote:


 PostgreSQL 8.1 running on Win XP. PGAdmin Version 1.6.2

When I backup my database on one computer using PG Admin 3 then attempt to
open the file on a second computer - PG Admin does NOT recognize the file.
The documentation isn't helpful, at least for a neophyte such as I.

Can someone point me to a simple instruction set for a backup/restore
operation using the PG Admin resource??

Bob

[GENERAL] Seeking help on subscription problem

[Péter Kovács]

Hi,

I have repeatedly tried subscribing to a number of postgresql mailing 
lists before Christmas on behalf of the following e-mail address: 
[EMAIL PROTECTED]

.

No confirmation e-mail has arrived to the above e-mail address yet.

I also sent an e-mail (from the above e-mail address) to 
[EMAIL PROTECTED] seeking for help on December 23 and got no 
answer yet.


Do e-mails from certain addresses go automatically to /dev/null? Or do I 
have to patiently wait for the webmaster to return from holiday?


Please, could someone help me find out what to do?

Thanks a lot and sorry for being off topic

Peter

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

Re: [GENERAL] Backup Restore

[Dave Page]

Shoaib Mir wrote:
Well I haven't use much of PGAdmin but I will always be using pg_dump 
and pg_restore for that as they are really easy to use.


You can find help on backup and restore at -- 
http://www.postgresql.org/docs/8.2/static/backup.html


pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes. 
Perhaps Bob could share whatever error message he gets with us?


Oh, and there is no released 1.6.2 version of pgAdmin - that would be 
head of the current stable branch.


Regards, Dave.

---(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] Backup Restore

[Bob Pawley]

I'm not getting an error message. The restore utility just doesn't see the 
backup file. It's looking for a file *.backup which is there but noot seen.


I am running a version that identifies itself as 1.6.2. I downloaded it a 
few weeks ago from the official site. Should I be using an earlier version??


Bob


- Original Message - 
From: Dave Page [EMAIL PROTECTED]

To: Shoaib Mir [EMAIL PROTECTED]
Cc: Bob Pawley [EMAIL PROTECTED]; Postgresql 
pgsql-general@postgresql.org

Sent: Thursday, December 28, 2006 11:30 AM
Subject: Re: [GENERAL] Backup Restore



Shoaib Mir wrote:
Well I haven't use much of PGAdmin but I will always be using pg_dump and 
pg_restore for that as they are really easy to use.


You can find help on backup and restore at -- 
http://www.postgresql.org/docs/8.2/static/backup.html


pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes. 
Perhaps Bob could share whatever error message he gets with us?


Oh, and there is no released 1.6.2 version of pgAdmin - that would be head 
of the current stable branch.


Regards, Dave.

---(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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?

[Hunter Hillegas]

I've seen this error on systems that don't realize they have Apple  
Remote Desktop installed which has its own copy of Postgres - you may  
be running a copy and not know it.


On Dec 28, 2006, at 11:04 AM, Tom Lane wrote:


Scott Ribe [EMAIL PROTECTED] writes:
Finally, the error message you're getting doesn't indicate that  
postgres is
actually requesting much memory. I suspect that you may have  
something else

running on your system which is using SysV shared memory.


Recent versions of OS X have ipcs, so you could try sudo ipcs -m to
find out about that.

regards, tom lane

---(end of  
broadcast)---

TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/



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

Re: [GENERAL] Backup Restore

[Dave Page]

Bob Pawley wrote:
I'm not getting an error message. The restore utility just doesn't see 
the backup file. It's looking for a file *.backup which is there but 
noot seen.


It's a standard file dialogue as provided by your operating system. I 
can't think of any reason it wouldn't see the file unless you are 
looking in the wrong place.


I am running a version that identifies itself as 1.6.2. I downloaded it 
a few weeks ago from the official site. Should I be using an earlier 
version??


You must have downloaded an SVN snapshot version. They are available on 
the official site, but it's not easy to mistake them for actual release 
versions.


Regards, Dave

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

[GENERAL] Optimising full outer join where for muti-row to multi-column view

[Phil Endecott]

Dear PostgreSQL experts,

I have a database that records the EXIF data for a collection of 
photos.  (EXIF is a method for embedding arbitary name-value data 
in a JPEG, and digital cameras typically use it to record things 
like exposure information.)  My exif table looks something like 
this:

 photo_id |   tag|  value 
--+--+--
 1001 | DateTime | 2006:10:26 11:19:29
 1001 | Orientation  | top - left
 1001 | PixelXDimension  | 3888
 1001 | PixelYDimension  | 2592
 1002 | DateTimeOriginal | 2006:10:26 13:34:06
 1002 | Orientation  | left - bottom
 1002 | PixelXDimension  | 3888
 1002 | PixelYDimension  | 2592

photo_id and tag together form the primary key.  The data also 
includes many tags that I'm not currently interested in.  From 
this I create a view containing only the tags of interest:

 photo_id |  orientation  |  datetime   | xsize | ysize 
--+---+-+---+---
 1001 | top - left| 2006:10:30 11:19:29 | 3888  | 2592
 1002 | left - bottom | 2006:10:26 13:34:06 | 3888  | 2592

My first attempt did this in the obvious way by joining the 
exif table with itself once per column:

create view photo_info_v as
select photo_id,
   e1.value as orientation, 
   e2.value as datetime, 
   e3.value as xsize,
   e4.value as ysize
  from exif e1
  join exif e2 using(photo_id)
  join exif e3 using(photo_id)
  join exif e4 using(photo_id)
  where e1.tag='Orientation' 
and e2.tag='DateTime' 
and e3.tag='PixelXDimension' 
and e4.tag='PixelYDimension';

This works well for one important query, where I find one photo's 
information from the view:

photos= explain analyse select * from photo_info_v where photo_id=1201;
  QUERY PLAN
  
--
 Nested Loop  (cost=0.00..12.09 rows=1 width=60) (actual time=1.198..1.290 
rows=1 loops=1)
   -  Nested Loop  (cost=0.00..9.07 rows=1 width=46) (actual time=0.953..1.009 
rows=1 loops=1)
 -  Nested Loop  (cost=0.00..6.04 rows=1 width=32) (actual 
time=0.693..0.731 rows=1 loops=1)
   -  Index Scan using exif_pkey on exif e1  (cost=0.00..3.02 
rows=1 width=18) (actual time=0.384..0.394 rows=1 loops=1)
 Index Cond: ((photo_id = 1201) AND (tag = 
'Orientation'::text))
   -  Index Scan using exif_pkey on exif e4  (cost=0.00..3.02 
rows=1 width=18) (actual time=0.189..0.205 rows=1 loops=1)
 Index Cond: ((1201 = photo_id) AND (tag = 
'PixelYDimension'::text))
 -  Index Scan using exif_pkey on exif e3  (cost=0.00..3.02 rows=1 
width=18) (actual time=0.186..0.194 rows=1 loops=1)
   Index Cond: ((1201 = photo_id) AND (tag = 
'PixelXDimension'::text))
   -  Index Scan using exif_pkey on exif e2  (cost=0.00..3.02 rows=1 width=18) 
(actual time=0.171..0.195 rows=1 loops=1)
 Index Cond: ((1201 = photo_id) AND (tag = 'DateTime'::text))
 Total runtime: 3.064 ms

However, I might just want one column from the view:

photos= explain analyse select orientation from photo_info_v where 
photo_id=1201;
  QUERY PLAN
  
--
 Nested Loop  (cost=0.00..12.09 rows=1 width=14) (actual time=1.266..1.353 
rows=1 loops=1)
   -  Nested Loop  (cost=0.00..9.07 rows=1 width=18) (actual time=1.009..1.062 
rows=1 loops=1)
 -  Nested Loop  (cost=0.00..6.04 rows=1 width=18) (actual 
time=0.752..0.787 rows=1 loops=1)
   -  Index Scan using exif_pkey on exif e1  (cost=0.00..3.02 
rows=1 width=18) (actual time=0.410..0.418 rows=1 loops=1)
 Index Cond: ((photo_id = 1201) AND (tag = 
'Orientation'::text))
   -  Index Scan using exif_pkey on exif e4  (cost=0.00..3.02 
rows=1 width=4) (actual time=0.183..0.199 rows=1 loops=1)
 Index Cond: ((1201 = photo_id) AND (tag = 
'PixelYDimension'::text))
 -  Index Scan using exif_pkey on exif e3  (cost=0.00..3.02 rows=1 
width=4) (actual time=0.168..0.176 rows=1 loops=1)
   Index Cond: ((1201 = photo_id) AND (tag = 
'PixelXDimension'::text))
   -  Index Scan using exif_pkey on exif e2  (cost=0.00..3.02 rows=1 width=4) 
(actual time=0.168..0.191 rows=1 loops=1)
 Index Cond: ((1201 = photo_id) AND (tag = 'DateTime'::text))
 Total runtime: 3.123 ms

I only wanted the orientation information, which 

Re: [GENERAL] Optimising full outer join where for muti-row to multi-column view

[Martijn van Oosterhout]

On Thu, Dec 28, 2006 at 08:29:56PM +, Phil Endecott wrote:
 To try and avoid this, I tried using a full outer join in the view 
 definition.  In this case the row would always be present in the view, 
 even if the data for the other columns were not present.  I hoped that 
 the query would then be optimised to look up only the orientation 
 information:

snip

You really want a left outer join, not a full outer (is the full outer
join even producing the answer you expect?). A full outer join can
never be optimised away.

That said, I don't know if the logic exists to optimise away a left
join either. It would only be possible if the join were on the primary,
thus you could be assured of exactly one row.

The only other alternative would be to make each column a subselect
instead. If you don't take that column as output, maybe it optimises
the subquery out entirely?

Like so:

create view view photo_info_v as
select photo_id,
  (select orientation from e2 where e2.tag='Orientation' and e2.photo_id = 
e1.photo_id), 
etc

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Backup Restore

[Bob Pawley]

When I change it to view all files it's there - but it won't do anything.

Bob
- Original Message - 
From: Dave Page [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Shoaib Mir [EMAIL PROTECTED]; Postgresql 
pgsql-general@postgresql.org

Sent: Thursday, December 28, 2006 12:06 PM
Subject: Re: [GENERAL] Backup Restore



Bob Pawley wrote:
I'm not getting an error message. The restore utility just doesn't see 
the backup file. It's looking for a file *.backup which is there but noot 
seen.


It's a standard file dialogue as provided by your operating system. I 
can't think of any reason it wouldn't see the file unless you are looking 
in the wrong place.


I am running a version that identifies itself as 1.6.2. I downloaded it a 
few weeks ago from the official site. Should I be using an earlier 
version??


You must have downloaded an SVN snapshot version. They are available on 
the official site, but it's not easy to mistake them for actual release 
versions.


Regards, Dave

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




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

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

Re: [GENERAL] convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.

[Alvaro Herrera]

Alexander Farber wrote:
 Hello Alvaro,
 
 On 12/24/06, Alvaro Herrera [EMAIL PROTECTED] wrote:
 Alexander Farber wrote:
  I started to prepare a test case and realized I had a bug.
  So convert() works for me, sorry for my previous message!
 
 In any case, it's probably saner if you SET client_encoding at the start
 of the session instead of using convert() everywhere.  The
 server_encoding should be correctly set to Win1251 though!  (Using
 SQL_ASCII, while not technically incorrect, is probably not doing you
 any favor).
 
 thanks for your comment! I've dropped my db
 and recreated it with encoding set to WIN1251:

Well, what I was suggesting (not explicitely enough, now that I look)
was that you SET client_encoding to UTF8, and then use the query this
way:

 #define SQL_FETCH_USER  \
select username,  \
user_avatar from phpbb_users where user_active = 1  \
and user_id = $1 and user_password = $2 and user_id not in  \
(select ban_userid from phpbb_banlist where ban_userid is not null)

Good to hear that it worked anyway.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [GENERAL] out of memory woes

[Angva]

Just wanted to post an update. Not going too well. Each time the
scripts were run over this holiday weekend, more statements failed with
out of memory errors, including more and more create index statements
(it had only been clusters previously). Eventually, psql could not even
be called with a very simple one-row update:

psql: could not fork new process for connection: Cannot allocate memory

The logs are nothing but this message repeated at this point:

2006-12-26 00:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 00:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 00:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 00:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 02:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 02:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory
2006-12-26 02:00:01 ESTLOG:  could not fork new process for
connection: Cannot allocate memory

I think I'm about ready to bounce the server every night unfortunately.

Thanks for listening,
Mark


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

Re: [GENERAL] NEED URGENT HELP....

[Sandip G]

  Great !!!  Its working Thanks to all for the great help. I am new
  to Postgre and like it's performance... I would like to learn it in
  depth, Please provide me any good resource to learn Postgre with lots
  of sample code/queries. Cheers !!!Best Regards,Sandip.

  - Original Message -
  From: Gurjeet Singh
  To: Martijn van Oosterhout , Henrique P Machado , Guy Rouillier
  , pgsql-general@postgresql.org, Sandip G
  Subject: Re: [GENERAL] NEED URGENT HELP
  Date: Tue, 26 Dec 2006 20:39:01 +0530

  It works Martijn...

  but with a few changes will be required in your function Sandip; you
  will have to pass an ARRAY constructor and return a SETOF record.
  Here's a sample:

  postgres= create table tab ( a int, b int );
  CREATE TABLE
  postgres= insert into tab values ( 1, 9 );
  INSERT 0 1
  postgres= insert into tab values (2,8);
  INSERT 0 1
  postgres= insert into tab values (3,7);
  INSERT 0 1
  postgres= insert into tab values (4,6);
  INSERT 0 1
  postgres= insert into tab values (5,5);
  INSERT 0 1
  postgres= create or replace function fun ( character varying [] )
  returns setof
   tab as
  postgres- $$
  postgres$ select * from tab where a = any ($1)
  postgres$ $$ language 'sql' volatile;
  CREATE FUNCTION
  postgres= select fun('{1}');
  fun
  ---
   (1,9)
  (1 row)

  postgres= select fun('{2,3}');
  fun
  ---
   (2,8)
   (3,7)
  (2 rows)

  postgres=

  Hope it helps


  On 12/26/06, Martijn van Oosterhout kleptog@svana.org wrote:

On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado
wrote:
 WHERE   (USER_ID = $1) AND (COMPANY_ID = $2) AND
BOOK_NO IN ($3)

 Could'nt he use an array in this 3rd parameter?

I think so, if it's written:

AND BOOK_NO = ANY($3)

Have a nice day,
--
Martijn van Oosterhout   kleptog@svana.org  
http://svana.org/kleptog/
 From each according to his ability. To each according to his
ability to litigate.


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

iD8DBQFFkQ1LIB7bNG8LQkwRApbCAJsH26IcDusO5Vi5kNC1UQ185usbnACeOxdC
xQo+z5Z7+Xofks/h3MmeF7w=
=Rq6g
-END PGP SIGNATURE-





  --
  [EMAIL PROTECTED]
  [EMAIL PROTECTED] gmail | hotmail | yahoo }.com

-- 
___
Search for products and services at:
http://search.mail.com

[GENERAL] could not open relation:no such file or directory

[karthik]

hello,

my name is karthik .

 i facing a problem when trying to select values from a table in
postgresql.

   when i execute a query like select title from itemsbytitle;   i
get error as

   Error:Could not open relation itemsbytitle. no such file or
directory.

   can anybody help me to find an answer for this problem.


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

[GENERAL] Geographical redundancy

[Dennis]

Is there any feasible way to achieve geographical redundancy of postgresql 
database?

Say you have a website which uses PG on the backend to read/write data and you 
want to have the website running on 2 separate servers distributed 
geographically and have the data synchronize somehow over the internet. 

In case one data center fails, website is still up and running from 2nd 
geographical location (from say 2nd DNS server).

Thank you.

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[GENERAL] questions about migrating form postgres 7 to 8

[Piotr Szałamacha]

Hello

I have question - is it som kind of guide for migrating from Postgres 7 
to 8 database?


I tried to simply dump 7 and inject it to 8 but operation failed.

Regards

PS

--
-
Piotr Szałamacha Horyzont Technologie Internetowe
E-mail: [EMAIL PROTECTED]   Web: http://www.horyzont.net
tel. +48 61 858 22 55, +48 61 622 95 00, fax. +48 61 858 22 56
ul. Półwiejska 24 61-888 Poznań Polska


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

[GENERAL] Date

[ice]

Hello,
   I have a couple of tables.  The client tables and the contacted
tables.
I am not sure how to start on this, what I need is a way to query  all
my clients
then show any client that the last visit  and or called day is greater
than 30 days.
Now it gets confusing, Suppose the client was visited more than 30 days
ago
but was called only 10 days ago, I really would like to have this
appear on the same
query.
So the report would look similar to this below (based on info given
later).
Client ID   Visit Date Called Date
1   ---   ---
22006-11-01
32006-11-01
52006-11-03
6  2006-11-03


Fields (Simplified)
Clients:  Name, Address, Phone.
Contacted:  Name, Date, Visit, Call.
I need to query all names, but I only need the last visit and last
phone call(which could be the same record in the contacted table).
Then determine if either  date is greater than 30 days  if
so, display the last date of each type of contact. And if there is
nothing for the client in the contacted table this needs to show also,
ClientD.
Any tips, ideas would be greatly appreciated
Thanks
Ice

CREATE TABLE contacted (
key serial NOT NULL,
Date date NOT NULL,
Phone boolean DEFAULT false NOT NULL,
Visit boolean DEFAULT false NOT NULL,
Reason character varying(255),
Results character varying(255),
Comments character varying(255),
id integer NOT NULL,
Enumber integer NOT NULL,
fup boolean DEFAULT true NOT NULL,
fupdate date
);

CREATE TABLE clients (
lname character varying(30),
fname character varying(30),
company character varying(40),
address1 character varying(30),
address2 character varying(30),
city character varying(30),
state character(2),
zip character(10),
active boolean DEFAULT true,
id integer DEFAULT nextval('id_seq'::regclass) NOT NULL
);

COPY clients (lname, fname, company, address1, address2, city, state,
zip, active, id) FROM stdin;
Smith   Joe Small CoSmallville Rd   Bigton  NY
12234   t   1
Doe JaneDust Grabber Inc10 Dirt Drive   Dustin
PA  12345-1222  t   2
Smacher Frank   Woodwerkers Inc 100 Forest
LaneOakland CA  12346-  t   3
Zimbob  Roger   Drywallz Inc1 Gympsum Place Quarryville
NY  12347
t   4
Deckem  WillPorches are us  2 Backyard Lane Gazeboton
CO  12348   t   5
Crimp   GregKidocker2 Tenfly Rd Metropolis
NY  10002   t   6
\.

--

COPY contacted (key, Date, Phone, Visit, Reason, Results,
Comments, id, Enumber, fup, fupdate) FROM stdin;
1   2006-11-01  t   f   Promote new filters Would
like a sample Sounds very
interested  2   602 t   2006-11-15
2   2006-11-01  t   f   Promote new filter  Send
Sample Sounds very
interested  3   602 t   2006-11-15
3   2006-11-02  f   t   Demo new air purifier   Glitch
in servo motor, would not
rotate the exhaust fan. Smoke coming from inside.   Demo bombed,
due to a
faulty oscilator motor.  \nThey will call us.\nDiscussed with
engineers.  4   602 f   \N
4   2006-11-03  t   f   Setup appointment   Setup
appointment for
11/28/2006  need to remind the day before   5   603 t
2008-11-27
5   2006-11-03  f   t   Demo Puro-203   Demo went
flawless.  William seemed to
be very impressed   Left several pamphlets on other models for home
and
business.   6   605 t   2006-11-17
6   2006-12-10  t   f   Just to see if they would like
to have 30 day eval of
the Puro-206d   Seemed interested they needed to talk with their
facilities manager  Need this sale after last
disaster4   605 t   2006-12-15
7   2006-12-15  t   f   Follow up   Have
appointment to install 2 30 day evals
of the Puro-206d 12/20/2006 Need to appease 4   605 t
2006-12-20
\.


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

[GENERAL] Why ContinueUpdateOnError is not implemented in npgsql

[Andrus]

.NET data adapter must implement DataAdapter.ContinueUpdateOnError
property in transaction.

ContinueUpdateOnError requires that transaction continues after error. In 
this case .NET can mark all bad rows in DataGrid. User can see all errors 
together.

Unfortunately inside transaction after error PostgreSQL returns message

Current transaction aborted, command ignored after end of transaction 
block

for all subsequent commands.

npgsql does NOT have any support for fix this.
It marks ALL subsequent commands as invalid.
So npgsql cannot used as reliable .NET data provider.
Please confirm this.

There are only two ways fix this issue.
NpgsqlDataAdapter must invoke automatic ROLLBACK after each error
or use checkpoints before each command.

Why this is not implemented ?


SIDENOTE.

In this case PosgreSQL acts like ill-designed compiler or spell checker 
which hangs on first error. Todays compilers return as many errors as 
possible.
Word spell checker mark all misspelled words in document, not only first 
one.
Using PostgreSQL+npgsql to import or edit large amounts of data having minor 
errors is huge loss in perfomance since major ADO.NET property is not 
supported. After each error whole process must started again.

Andrus 



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

Re: [GENERAL] ERROR: could not access status of transaction

[Stuart Grimshaw]

On 12/23/06, Stuart Grimshaw [EMAIL PROTECTED] wrote:

berble=# select * from headlines ;
ERROR:  could not access status of transaction 1668180339
DETAIL:  could not open file pg_clog/0636: No such file or directory

Using Postgres 8.1.5 from Debian unstable, I got the above error, and
I have no idea what it means?


Is there any more info I can give you guys to help sort this out?

--
-S

Sports Photography in South Yorkshire  Derbyshire
http://www.stuartgrimshaw.co.uk

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

[GENERAL] odbc and pgadmin.support newsgroup from news server are not functional

[Andrus]

News reader cannot read

pgsql.interfaces.odbc
pgsql.interfaces.pgadmin.support

groups  from news.postgresql.org news server

last message in odbc group is from 14.12.6 and in pgadmin group is from 
18.12.6

pgsql.general and pgsql.announce newsgroups works OK.

Andrus. 



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

   http://archives.postgresql.org/

Re: [GENERAL] DB problem

[Filip Rembiałkowski]

2006/12/26, [EMAIL PROTECTED] [EMAIL PROTECTED]:


I think the logs are not enabled in my DB. So I set the following options in
postgresql.con file


you are using some options which came with later postgresql releases.


log_timestamp= true

ok

log_directory=pg_log

wrong (available from 8.0)

log_filename=%Y-%m-%d_%H%M%S.log

wrong (available from 8.0)

log_disconnections=true

wrong (available from 8.0)

log_hostname=true

wrong (available from 7.4)

log_statement=true

OK

you can review all runtime options for 7.3 here:
http://www.postgresql.org/docs/7.3/static/runtime-config.html



When you install the DB, the logs are enabled by default?
Would you pls tell me still what setting need to be done?


for 7.3, the default was to log to standard output.
so you will have to redirect it, either to syslog (by setting syslog =
1), or to a file using shell redirection, or with pg_ctl option.
see http://www.postgresql.org/docs/7.3/static/app-pg-ctl.html


F.

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

Re: [GENERAL] questions about migrating form postgres 7 to 8

[Joshua D. Drake]

On Thu, 2006-12-28 at 10:03 +0100, Piotr Szałamacha wrote:
 Hello
 
 I have question - is it som kind of guide for migrating from Postgres 7 
 to 8 database?
 
 I tried to simply dump 7 and inject it to 8 but operation failed.

Use the version 8 pg_dump to connect to version 7 postgresql.

 
 Regards
 
 PS
 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

   http://archives.postgresql.org/

Re: [GENERAL] Why ContinueUpdateOnError is not implemented in npgsql

[Tom Lane]

Andrus [EMAIL PROTECTED] writes:
 ContinueUpdateOnError requires that transaction continues after error. In 
 this case .NET can mark all bad rows in DataGrid. User can see all errors 
 together.
 ...
 There are only two ways fix this issue.
 NpgsqlDataAdapter must invoke automatic ROLLBACK after each error
 or use checkpoints before each command.

Yup, a savepoint before each command is required if that's the behavior
you want.  Yes, that adds overhead.  The reason it's not automatic is
exactly that it adds overhead, which many applications don't need or
want.  But if you have to have it, that's what you do.

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] questions about migrating form postgres 7 to 8

[Tom Lane]

=?ISO-8859-2?Q?Piotr_Sza=B3amacha?= [EMAIL PROTECTED] writes:
 I have question - is it som kind of guide for migrating from Postgres 7 
 to 8 database?

 I tried to simply dump 7 and inject it to 8 but operation failed.

Failed how?  And which 7.x and 8.x versions are you talking about?
You need to be a *lot* more specific than that if you want useful
help.

However, the one bit of useful advice I could give you is to read the
release notes for all PG versions between the two you are dealing with.
In theory all the incompatibilities should be mentioned there somewhere.
This URL is always up to date:
http://developer.postgresql.org/pgdocs/postgres/release.html

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] select union with table name

[Reece Hart]

On Thu, 2006-12-28 at 19:09 +0700, Akbar wrote:
 select blue.name from blue union select red.name from red
  give me this:
 name
 'blabla'
 'bubu'
 'haha'
 'kkk'
 
 I want this:
 nametable_name
 'blabla'   blue
 'bubu'blue
 'haha'red
 'kkk'  red
 
 Could I? 

select name,'blue' as table_name from blue union all select name,'red'
as table_name from red;

Note the 'all' after union... I suspect you'll want that or should at
least consider it.

-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.


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

   http://archives.postgresql.org/

Re: [GENERAL] Geographical redundancy

[Ben]

Sure, there are lots of ways. Factors that start constraining things  
are:


- do both sites have to be online (making changes to the data) at the  
same time?

- how tightly do both sites have to stay in sync?
- is data loss acceptable if one site suffers a disaster?
- what platform are you running on?
- how much throughput latency do you have between sites?
- how much downtime is acceptable in switching sites?

On Dec 26, 2006, at 11:41 PM, Dennis wrote:

Is there any feasible way to achieve geographical redundancy of  
postgresql database?


Say you have a website which uses PG on the backend to read/write  
data and you want to have the website running on 2 separate servers  
distributed geographically and have the data synchronize somehow  
over the internet.


In case one data center fails, website is still up and running from  
2nd geographical location (from say 2nd DNS server).


Thank you.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com



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

  http://archives.postgresql.org/

Re: [GENERAL] Date

[A. Kretschmer]

am  Thu, dem 28.12.2006, um  7:24:30 -0800 mailte ice folgendes:
 Hello,
I have a couple of tables.  The client tables and the contacted
 tables.
 I am not sure how to start on this, what I need is a way to query  all
 my clients
 then show any client that the last visit  and or called day is greater
 than 30 days.

You are searching for the CASE-statement, a simple example:

test=# select * from visits;
 id |  typ   |   datum
++
  1 | visit  | 2006-01-01
  2 | visit  | 2006-02-01
  3 | called | 2006-03-01
  4 | called | 2006-05-01
  5 | called | 2006-11-01
  6 | visit  | 2006-11-02
  7 | visit  | 2006-12-02
  8 | called | 2006-12-03
(8 rows)

test=# select case when typ='visit' then datum else null end as visit, 
  case when typ='called' then datum else null end as called 
  from visits 
  where datum  current_date-60;
   visit|   called
+
| 2006-11-01
 2006-11-02 |
 2006-12-02 |
| 2006-12-03
(4 rows)





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

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

Re: [GENERAL] LDAP configuration problem

[Magnus Hagander]

Wenjian Yang wrote:
 Hi,
 
 I've just installed 8.2.0 from rpms on FC6 i386 and wanted to try out
 the built-in LDAP support.
 
 The following LDAP authentication was added to pg_hba.conf
 
 host  all  all  0.0.0.0 http://0.0.0.0  0.0.0.0 http://0.0.0.0 
 ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\
 
 However when trying to connect from another machine, it gave the error
 FATAL: missing or erroneous pg_hba.conf file.
 
 Looking at the server log, the comma in dc=domain,dc=com has become ^A
 while all the other characters are intact.
 
 Does this have to do with some other packages or I specified the the
 line wrong? Thanks in advance.


The first thing you've done wrong is not actually include the log lines
that had something to do with it. What are those server log lines?

Anyway, I beleive you should write:
host all all 0.0.0.0 0.0.0.0 ldap
ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\


//Magnus

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

Re: [GENERAL] LDAP configuration problem

[Wenjian Yang]

Sorry, below are the lines in the log file:

LOG:  invalid entry in file /pub/pgsql/data/pg_hba.conf at line 78, token
ldap://dc.domain.com/dc=domain^Adc=com;DOMAIN\;
FATAL:  missing or erroneous pg_hba.conf file
HINT:  See server log for details.

And the pg_hba.conf line 78, is

host  all  all  0.0.0.0   0.0.0.0
ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\

Since the previous email, I had the same problem on another FC6 x86_64 box.

Thanks.

Wenjian

On 12/29/06, Magnus Hagander [EMAIL PROTECTED] wrote:


Wenjian Yang wrote:
 Hi,

 I've just installed 8.2.0 from rpms on FC6 i386 and wanted to try out
 the built-in LDAP support.

 The following LDAP authentication was added to pg_hba.conf

 host  all  all  0.0.0.0 http://0.0.0.0  0.0.0.0 http://0.0.0.0
 ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\

 However when trying to connect from another machine, it gave the error
 FATAL: missing or erroneous pg_hba.conf file.

 Looking at the server log, the comma in dc=domain,dc=com has become ^A
 while all the other characters are intact.

 Does this have to do with some other packages or I specified the the
 line wrong? Thanks in advance.


The first thing you've done wrong is not actually include the log lines
that had something to do with it. What are those server log lines?

Anyway, I beleive you should write:
host all all 0.0.0.0 0.0.0.0 ldap
ldap://dc.domain.com/dc=domain,dc=com/DOMAIN\


//Magnus