Re: [NOVICE] [GENERAL] DB insert Error

[Harpreet Dhaliwal]

Forgot to write that that was my question.
I mean can we call a stored procedure as an action of a trigger? 
On 8/16/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:


Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function.

 
Harpreet 


On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]
> wrote: 

On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar 
> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.Are you committing the transaction?  See the bottom of the 
following page:http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
"In the default mode, statements are committed only when EXEC SQL COMMIT is issued."--Michael Fuhr---(end of broadcast)---TIP 4: Have you searched our list archives?
   http://archives.postgresql.org
 

[GENERAL] limitations of constraints, possible bugs

[craigp]

i'm curious about how constraints work (perhaps vs how they are supposed to
work). 

for instance, if i create a domain (D) with some constraint, and create a table
with a column of type D[], will that domain constraint be enforced for each
element of the array? could i create a column constraint on an array to enforce
some kind of relationship among the elements (say, that they are sorted, or
that there are no NULL elements if/when NULL elements are supported in arrays),
or would it be necessary to create a trigger? finally, is it meaningful to have
a foreign key refer to an array column (and if so, what does it mean)?

i have noticed in the docs that there are various limitations in the current
implementation: domain constraints aren't enforced when returned from
functions, rowtype (from create table) constraints aren't enforced, constraints
can't be defined for composite types, etc. i see the first one is on the TODO
list; what about the others? finally, oracle has a %ROWTYPE attribute for
tables; has there been any consideration for adopting such a feature (it might
clarify the difference between a table/relation with its constraints, indexes,
etc and the implied type)?

are these sort of questions answered anywhere? if i do some testing, and find
something i think is a bug, should i ask about it here or just post it as bug
and let it be resolved by that process?

thanks!
--craig


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

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

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

[GENERAL] Timezones -- what comes out does not go in?

[Julian Scarfe]

The timezone on our boxes is set to Etc/UCT (possibly a distro default).

(psql 8.1.4)

=> select to_char(current_timestamp, 'HH24:MI:SS TZ');
  to_char
--
09:05:48 UCT
(1 row)

=>  select '09:05:48 UCT'::time;
ERROR:  invalid input syntax for type time: "09:05:48 UCT"

UTC works, of course:

=> select '09:05:48 UTC'::time;
  time
--
09:05:48
(1 row)


Is this behaviour expected? Desirable?

Thanks
Julian


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

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

Re: [GENERAL] How to add days to date

[Alban Hertroys]

Alejandro Michelin Salomon ( Adinet ) wrote:

Hi:

I have problem trying to add same days to a date.

'2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) --> This
results in

EX :
'2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44


Looks like you could use the interval type here too. I don't know what 
the above is supposed to do, but I'd prefer to write it like:


'2006-08-01'::date + '1 month'::interval + (3-1) * '1 week'::interval

Or:
'2006-08-01'::date + INTERVAL '1 month' + (3-1) * INTERVAL '1 week'

It at least saves you the trouble of determining how long what month 
takes, and it handles DST changes correctly.
The drawback is that it's not a linear data type, which can cause some 
trouble if you need to convert values from your application to intervals.


For details, have a look at the documentation:
http://www.postgresql.org/docs/8.1/static/datatype-datetime.html

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

Re: [GENERAL] Partial indexes Vs standard indexes : Insert performance

[MaXX]

Gregory Stark wrote:

MaXX <[EMAIL PROTECTED]> writes:

In my understanding, a partial index is only touched when a matching row is
inserted/updated/deleted (index constraint is true), so if I create a partial
index for each protocol, I will slow down my machine as if I had created a
single "normal" index, but it will find rows faster (the distribution is not
uniform)...
Is this correct?

[snip]

So for example if there are a million packets to a given host but only 100k
that were TCP then a partial index on  would let you
scan only the 100k instead of having to scan the million and look at each one
to discard it. And it would let you do that without having to create a much
larger index on  or combine two indexes one on  and one on
 either of which would be much slower and take more space.

OK. I made some test with the queries actually run by my app and I found
a new usefull indexes to replace another.
I can see a real improvement from 112ms to 4ms in the query to find ICMP 
pkts.



But if you're just looking up a single record I wouldn't expect it to be much
faster to look it up in the smaller partial index than in the larger index.
Indexes find records in log(n) time and log() grows awfully slowly. At best
you're basically skipping a single tree level in favour of earlier query
planning which is probably not going to be noticeable.


I'm taking good note of this.

Thanks a lot,
--
MaXX

---(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] Partial indexes Vs standard indexes : Insert

[MaXX]

Jeff Davis wrote:

On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:

[snip]

I have a table in which I store log from my firewall.
For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP 
~1%, the table contains 1.7M rows), I use a partial index to find ICMP 
packets faster.

It's ICMP ~0.1%
In my understanding, a partial index is only touched when a matching row 
is inserted/updated/deleted (index constraint is true), so if I create a 
partial index for each protocol, I will slow down my machine as if I had 
created a single "normal" index, but it will find rows faster (the 
distribution is not uniform)...

Is this correct?

That should work. Keep in mind that the main idea of an index is to
reduce the number of pages that have to be fetched from disk. If the
record size is small, you may have at least one ICMP packet on 50% (or
more) of the disk pages even if ICMP packets only make up 1% of the
total records. Even if they aren't inserted randomly, updates/deletes
may randomize the distribution somewhat. If you have an ICMP packet on
every other page, you might not be impressed with the performance versus
a sequential scan. However, it could be a big win if you have other
WHERE conditions aside from just the packet type.
OK, so that works well for queries where there is a very few rows in the 
index in regard of the table size, and as long as this still true.



The planner tries to take all of these things into consideration to some
degree. The best test is to try EXPLAIN or EXPLAIN ANALYZE to see what
plan it makes. Also, try forcing different types of plans to see if the
planner is making the right choice.
I did some test and with both your reply and the one of Gregory Stark, I 
was able identify what are good indexes and speed up the thing...


Thanks a lot,
--
MaXX

---(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] Massive slowdown when LIMIT applied

[Graeme Hinchliffe]

Hi,	I am using postgres 7.4.7 under Debian Sarge for RADACCT, this means a fairly large table of data allowing us to hold approx 12 months of data.  I have used a trigger to break the system in 2 and keep the live table which takes updates small, and all closed connections are inserted into a large historical table.  The histrorical table is only ever inserted into and queried.It is indexed on all mayor attributes for which it may be searched or ordered.The problem is this:If I run the query:SELECT username,acctstarttime FROM radacct WHERE username='user';The results come back nice and quick (indexes are are username, and acctstarttime BTW)If I do the following:SELECT username,acctstarttime FROM radacct WHERE username='user' ORDER BY acctstarttime;again the results come up slightly slower as would be expected but still very quickly.. (a few hundred rows in this case)Now, if I do the following:SELECT username,acctstarttime FROM radacct WHERE username='user' ORDER BY acctstarttime LIMIT 50;I have to wait a very long time, IE instead of 2-3 seconds.. can be as slow as 10+ minutes.this seems very odd to me, any ideas?Thanks in advance.-Graeme Hinchliffe (BSc)Core Systems DesignerZen Internet (http://www.zen.co.uk/)Direct: 0845 058 9074Main  : 0845 058 9000Fax   : 0845 058 9005  

RES: [GENERAL] How to add days to date

[Alejandro Michelin Salomon \( Adinet \)]

Michael :

I change my query to this :

SELECT CAST( '2006-08-01' AS DATE ) + FP.carencia + ( FP.prazo * (
MFP.parcela - 1 )) AS vencimento
  FROM fi_mov_formas_pagamento MFP 
   LEFT OUTER JOIN fi_formas_pagamento FP ON ( MFP.idformapagamento =
FP.idformapagamento AND MFP.idempresa = FP.idempresa )
   INNER JOIN hd_cabecalho HDC ON ( MFP.idmovimento = HDC.idhelpdesk AND
MFP.idempresa = HDC.idempresa )
  WHERE MFP.idmovimento = 1
 AND MFP.idempresa = 1
 AND MFP.idtipomovimentacao = 1

And i run ok now.

Thanks for your help.

Alejandro

-->-Mensagem original-
-->De: Michael Fuhr [mailto:[EMAIL PROTECTED] 
-->Enviada em: terça-feira, 15 de agosto de 2006 22:17
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] How to add days to date
-->
-->
-->On Tue, Aug 15, 2006 at 10:10:27PM -0300, Alejandro Michelin 
-->Salomon ( Adinet ) wrote:
-->> EX :
-->> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44
-->> 
-->> All my trys fails.
-->
-->The error message hints at what's wrong:
-->
-->test=> SELECT '2006-08-01' + 30 + (7 * (3 - 1));
-->ERROR:  invalid input syntax for integer: "2006-08-01"
-->
-->PostgreSQL doesn't know that the untyped string is supposed 
-->to be interpreted as a date.  Use a cast:
-->
-->test=> SELECT '2006-08-01'::date + 30 + (7 * (3 - 1));
-->  ?column?  
-->
--> 2006-09-14
-->(1 row)
-->
-->or
-->
-->test=> SELECT CAST('2006-08-01' AS date) + 30 + (7 * (3 - 1));
-->  ?column?  
-->
--> 2006-09-14
-->(1 row)
-->
-->-- 
-->Michael Fuhr
-->


---(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] Segmentation Fault

[Poul Møller Hansen]

Last night one of my databases broke down temporary because of a 
segmentation fault.
It has only happended this time and the database was fully recovered 
afterwards,
but I was wondering what I can do anything to prevent it from happening 
again


It happened while the backup was running (pg_dump & pgdumpall)
Here are some details from the logs etc.

The system is running Ubuntu Linux and I'm using the PostgreSQL package 
from the dapper repository:

uname -a
Linux db 2.6.15-26-amd64-server #1 SMP Fri Jul 7 20:02:26 UTC 2006 
x86_64 GNU/Linux


select version()
PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt 
(GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)


pgsql log
2006-08-16 00:38:22 CEST - LOG:  server process (PID 4792) was 
terminated by sig

nal 11
2006-08-16 00:38:22 CEST - LOG:  terminating any other active server 
processes
2006-08-16 00:38:22 CEST - WARNING:  terminating connection because of 
crash of

another server process
2006-08-16 00:38:22 CEST - DETAIL:  The postmaster has commanded this 
server pro
cess to roll back the current transaction and exit, because another 
server proce

ss exited abnormally and possibly corrupted shared memory.
2006-08-16 00:38:22 CEST - HINT:  In a moment you should be able to 
reconnect to

the database and repeat your command.

DETAIL and HINT repeated for every connection

2006-08-16 00:38:23 CEST - LOG:  all server processes terminated; 
reinitializing
2006-08-16 00:38:23 CEST - LOG:  database system was interrupted at 
2006-08-16 0

0:36:21 CEST
2006-08-16 00:38:23 CEST - LOG:  checkpoint record is at 5/4F9FDC00
2006-08-16 00:38:23 CEST - LOG:  redo record is at 5/4F9B3558; undo 
record is at

0/0; shutdown FALSE
2006-08-16 00:38:23 CEST - LOG:  next transaction ID: 5408607; next OID: 
30199
2006-08-16 00:38:23 CEST - LOG:  next MultiXactId: 1; next 
MultiXactOffset: 0
2006-08-16 00:38:23 CEST - LOG:  database system was not properly shut 
down; aut

omatic recovery in progress
2006-08-16 00:38:23 CEST - FATAL:  the database system is starting up
2006-08-16 00:38:23 CEST - LOG:  redo starts at 5/4F9B3558
2006-08-16 00:38:23 CEST - LOG:  record with zero length at 5/4FB63C18
2006-08-16 00:38:23 CEST - LOG:  redo done at 5/4FB63BE8
2006-08-16 00:38:26 CEST - LOG:  database system is ready
2006-08-16 00:38:26 CEST - LOG:  transaction ID wrap limit is 
1073864149, limite

d by database "db"

At 00:36:21 this was happening in the pgsql log
2006-08-16 00:36:21 CEST - LOG:  duration: 14673.110 ms  statement: 
EXECUTE 
amed>  [PREPARE:  select * from app.insert_unitstat($1,$2,$3,$4,$5,$6,$7,
$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)  as result]
2006-08-16 00:36:21 CEST - LOG:  duration: 8730.029 ms  statement: 
EXECUTE 
med>  [PREPARE:  select * from app.insert_unitstat($1,$2,$3,$4,$5,$6,$7,$
8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)  as result]
2006-08-16 00:36:21 CEST - LOG:  duration: 5982.330 ms  statement: 
EXECUTE 
med>  [PREPARE:  select * from app.insert_unitstat($1,$2,$3,$4,$5,$6,$7,$
8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)  as result]
2006-08-16 00:36:21 CEST - LOG:  duration: 10404.601 ms  statement: 
EXECUTE 
amed>  [PREPARE:  select * from app.insert_unitstat($1,$2,$3,$4,$5,$6,$7,
$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)  as result]

These statements are called in a plpgsql function and the function is 
called via JDBC

using postgresql-8.1-407.jdbc3.jar

dmesg
[2425253.737383] postmaster[4792]: segfault at 2aaab6f0e000 rip 
2b73795b rsp 7f8c9228 error 4



Any suggestions ?

Thanks,
Poul



---(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] Segmentation Fault

[Bill Moran]

In response to "Poul Møller Hansen" <[EMAIL PROTECTED]>:

> Last night one of my databases broke down temporary because of a 
> segmentation fault.
> It has only happended this time and the database was fully recovered 
> afterwards,
> but I was wondering what I can do anything to prevent it from happening 
> again
> 
> It happened while the backup was running (pg_dump & pgdumpall)
> Here are some details from the logs etc.
> 
> The system is running Ubuntu Linux and I'm using the PostgreSQL package 
> from the dapper repository:
> uname -a
> Linux db 2.6.15-26-amd64-server #1 SMP Fri Jul 7 20:02:26 UTC 2006 
> x86_64 GNU/Linux
> 
> select version()
> PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt 
> (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)
> 
> pgsql log
> 2006-08-16 00:38:22 CEST - LOG:  server process (PID 4792) was 
> terminated by sig
> nal 11

Sig 11 are frequently the result of hardware problems.  Make sure the
system has enough cooling and consistent power.  Stress test the RAM, MMU,
and other components to ensure that they will function reliably under
load.

-- 
Bill Moran
Collaborative Fusion Inc.

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

Re: [GENERAL] Segmentation Fault

[Chris Mair]

> dmesg
> [2425253.737383] postmaster[4792]: segfault at 2aaab6f0e000 rip 
> 2b73795b rsp 7f8c9228 error 4
> 
> 
> Any suggestions ?

Do you trust that machine's RAM?
Can you try running memtest86 for some extended period of time?

(just to make sure it's not a hardware issue)

Bye, Chris.


-- 

Chris Mair
http://www.1006.org



---(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] Is it possible (postgresql/mysql)

[louis gonzales]

Hello List,
PostgreSQL 8.0.1 (on Solaris 9)
There is a PERL program that a friend purchased which is used to create 
tables on a MySQL database, and of course ;) I want to run this on a 
PostgreSQL database server instead.  The below is the code:

$sth=runSQL("CREATE TABLE someTable (
   date_create bigint NOT NULL,
   date_end bigint NOT NULL,
   username VARCHAR(20) NOT NULL,
   $cat_definition
   id serial PRIMARY KEY,
   status VARCHAR(20) NOT NULL,
   $adfields
  visibility TEXT NOT NULL,
  priority TEXT NOT NULL,
  template TEXT NOT NULL,
  view bigint DEFAULT 0 NOT NULL,
  reply bigint DEFAULT 0 NOT NULL,
  save bigint DEFAULT 0 NOT NULL,
  updated bigint,
  photo VARCHAR(1) NOT NULL DEFAULT '0',
  INDEX(username),
  $cat_index
  INDEX(date_create) );");

What my question is, the "INDEX(...)" function calls, which work this 
way on MySQL, don't work in PostgreSQL.  Does anybody know what a 
synonymous way to modify the above code, for compatibility with PostgreSQL?


FYI:yourVariable  INT UNSIGNED AUTO_INCREMENT(MySQL)
   can be replaced by   
  yourVariable serial


Thanks group!

--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

Re: [GENERAL] Is it possible (postgresql/mysql)

[Harald Armin Massa]

Louis,indizes are simply created outside the create tableCREATE INDEX someTable_Date_create  ON someTable  USING btree  (date_create);As you are working on transferring, maybe you like to drop those varchar(xx) and replace them with text. Saves a lot of hassle lateron.
HaraldOn 8/16/06, louis gonzales <[EMAIL PROTECTED]> wrote:
Hello List,PostgreSQL 8.0.1 (on Solaris 9)There is a PERL program that a friend purchased which is used to createtables on a MySQL database, and of course ;) I want to run this on aPostgreSQL database server instead.  The below is the code:
 $sth=runSQL("CREATE TABLE someTable (date_create bigint NOT NULL,date_end bigint NOT NULL,username VARCHAR(20) NOT NULL,$cat_definition
id serial PRIMARY KEY,status VARCHAR(20) NOT NULL,$adfields   visibility TEXT NOT NULL,   priority TEXT NOT NULL,
   template TEXT NOT NULL,   view bigint DEFAULT 0 NOT NULL,   reply bigint DEFAULT 0 NOT NULL,   save bigint DEFAULT 0 NOT NULL,   updated bigint,
   photo VARCHAR(1) NOT NULL DEFAULT '0',   INDEX(username),   $cat_index   INDEX(date_create) );");What my question is, the "INDEX(...)" function calls, which work this
way on MySQL, don't work in PostgreSQL.  Does anybody know what asynonymous way to modify the above code, for compatibility with PostgreSQL?FYI:yourVariable  INT UNSIGNED AUTO_INCREMENT(MySQL)can be replaced by
   yourVariable serialThanks group!--Email:[EMAIL PROTECTED]WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey"Good morning starshine, the Earth says hello." -Willy Wonka---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart
0173/9409607-Let's set so double the killer delete select all.

Re: [GENERAL] Is it possible (postgresql/mysql)

[louis gonzales]

Harald,
I had thought of that, but I wasn't sure if there was/is a way to create 
the index's upon table creation, as it appears is possible with MySQL.


As for the replacing of varchar(xx) with a text data type, why do you 
recommend this?  I want to stay as close as I can to the original 
code...but if you think there is a good reason and that it won't 
conflict with something else, then I'd like to know.  Granted, 
varchar(xx) is nothing but a string of characters, potentially xx in 
length, and a text datatype is also just a string of characters, I would 
wonder if possibly there would be a string comparison that would treat 
these different on the single fact of different datatype?


Thanks for your help Harald!

Harald Armin Massa wrote:


Louis,

indizes are simply created outside the create table


CREATE INDEX someTable_Date_create
  ON someTable
  USING btree
  (date_create);

As you are working on transferring, maybe you like to drop those 
varchar(xx) and replace them with text. Saves a lot of hassle lateron.


Harald



On 8/16/06, *louis gonzales* <[EMAIL PROTECTED] 
> wrote:


Hello List,
PostgreSQL 8.0.1 (on Solaris 9)
There is a PERL program that a friend purchased which is used to
create
tables on a MySQL database, and of course ;) I want to run this on a
PostgreSQL database server instead.  The below is the code:
$sth=runSQL("CREATE TABLE someTable (
date_create bigint NOT NULL,
date_end bigint NOT NULL,
username VARCHAR(20) NOT NULL,
$cat_definition
id serial PRIMARY KEY,
status VARCHAR(20) NOT NULL,
$adfields
   visibility TEXT NOT NULL,
   priority TEXT NOT NULL,
   template TEXT NOT NULL,
   view bigint DEFAULT 0 NOT NULL,
   reply bigint DEFAULT 0 NOT NULL,
   save bigint DEFAULT 0 NOT NULL,
   updated bigint,
   photo VARCHAR(1) NOT NULL DEFAULT '0',
   INDEX(username),
   $cat_index
   INDEX(date_create) );");

What my question is, the "INDEX(...)" function calls, which work this
way on MySQL, don't work in PostgreSQL.  Does anybody know what a
synonymous way to modify the above code, for compatibility with
PostgreSQL?

FYI:yourVariable  INT UNSIGNED AUTO_INCREMENT(MySQL)
can be replaced by
   yourVariable serial

Thanks group!

--
Email:[EMAIL PROTECTED]

WebSite:  http://www.linuxlouis.net 
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all. 




--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

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

Re: [GENERAL] Segmentation Fault

[Poul Møller Hansen]

dmesg
[2425253.737383] postmaster[4792]: segfault at 2aaab6f0e000 rip 
2b73795b rsp 7f8c9228 error 4



Any suggestions ?



Do you trust that machine's RAM?
Can you try running memtest86 for some extended period of time?

(just to make sure it's not a hardware issue)

  

Well even that it's not cheap hardware one can never be sure that it's ok.
It's a production server, so I guess it has to be a night job...

Thanks,
Poul


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

Re: [GENERAL] Restarting Slony crashes Postgresql?

[Christopher Browne]

In an attempt to throw the authorities off his trail, "Nico" <[EMAIL 
PROTECTED]> transmitted:
> Hi group,
>
> I'm using Slony-I 1.1.5 with Postgresql 8.1.4 on 3 DB server (OS =
> debian sarge).
> I set a replication from a database on server A (master) to 2 servers B
> and C (slaves).
>
> Note that the slaves databases are in production and have a quite big
> load.
>
> I noticed last day that when I stop Slony daemon (for any reason) on a
> slave server (B or C), a few seconds later, the server doesn't respond
> anymore. Then I need to reboot the system. I noticed some "fetch 100
> from LOG" queries on the master database too...
>
> => To prevent the server crashing, It seems that I need to restart
> postgresql just after restarting Slony (and before it all crashes).
>
> Is it always necessary to restart Postgresql after restarting a Slony
> daemon ? (though, it doesn't seem necessary when the servers have not
> many connections)
>
> Any information will be appreciated.

That seems exceeding unusual.

slon daemons are not terribly special; they are merely client apps
written in C that use libpq that connect to multiple databases.

When the Slony-I code was less mature, we used to very frequently shut
down and restart slons on a routine basis...  

  "There seems to be a problem with replication..."

  "... Why not restart the slons and see if that helps..."

- If your system is heavily loaded (notably, if there's a lot of
  updates to be replicated), it's no surprise to see "fetch 100 from
  LOG" queries

- There are *some* objects that Slony-I locks, but this should
  certainly not cover things your applications are using

- That the server would stop responding after stopping slons is *very*
  surprising.

I'd be inclined to look at pg_locks to see what's locked; I'm not sure
what to expect, because I'd not expect there to be anything that's
Slony-I's fault.

There is an exception, of course; if the cluster is processing an
EXECUTE SCRIPT event, that locks lots of stuff.  But that would seem
worthy of your mention, and again, stopping slons for subscribers
wouldn't appear to be the cause of the problem...
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/rdbms.html
Sometimes if you have a cappuccino and then try again it will work OK.
- Dr. Brian Reid, 1992
Sometimes one cappucino isn't enough.
- Marcus J. Ranum

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

Re: [GENERAL] Is it possible (postgresql/mysql)

[Harald Armin Massa]

Louis,there may be an a unimportant performance penality for varchar, because of filling up and whatever. But the real reason: as much as I researched, NOBODY stated that varchar2 would be faster or better; but it limits the length. And I spend to much time in working around fixed field lengths in my life; and I saw enough people being frustrated and wasting time, especially for fields like "name". 
i.E.: in germany the ZIP-code was extended from 4 to 5 digits. Can you imagine HOW MANY applications had to be fixed?On the other hand I use the "TEXT" type to argue when somebody wants me to port my application to Oracle :) ... "yes, of course. You need Oracle Enterprise Edition because of Table Partitioning; and we need to refactor all text fields so they can deal with Oracles limited VARCHAR2 type, or you simply accept that we will only use the first xxx chars."
Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.

Re: [GENERAL] limitations of constraints, possible bugs

[Tom Lane]

craigp <[EMAIL PROTECTED]> writes:
> for instance, if i create a domain (D) with some constraint, and create a 
> table
> with a column of type D[], will that domain constraint be enforced for each
> element of the array?

Currently we don't support arrays of domain types (this should get fixed
sometime).  If we did, then yeah, that's how it'd work.

> could i create a column constraint on an array to enforce
> some kind of relationship among the elements (say, that they are sorted, or
> that there are no NULL elements if/when NULL elements are supported in 
> arrays),

If you could express it as a SQL boolean expression, then it'd work as a
constraint.  For something like a sortedness check, it'd likely be
easier to check with a loop in a trigger function.

> i have noticed in the docs that there are various limitations in the current
> implementation: domain constraints aren't enforced when returned from
> functions, rowtype (from create table) constraints aren't enforced, 
> constraints
> can't be defined for composite types, etc. i see the first one is on the TODO
> list; what about the others?

The first of these is done in CVS HEAD, the others probably will get
done someday.  If you want to see them happen sooner rather than later,
consider working on 'em yourself ...

regards, tom lane

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

   http://archives.postgresql.org

[GENERAL] Weird join result

[Peter Nixonn]

Hi Guys

I am getting a result for an JOIN that I think is wrong. Maybe its my
understanding that is wrong here however, so please be gentle :-)

The "phones" table contains a list of phone numbers and an associated
customer ID. The radacct table contains a list of all calls made (RADIUS
Accounting records).

I am doing the following:

SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 GROUP BY
phones.CALLINGSTATIONID;

This query as expected returns 1386 rows (for customer ID 1) which includes
a number of rows which have a NULL sum as they have not ever connected.

Now, what I want to do is to return the same result set (of 1386 phones),
but only for a particular time period. I therefore do the following:

SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and radacct.ACCTSTARTTIME
BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' GROUP BY
phones.CALLINGSTATIONID;

This returns 1280 rows, none of which are have a NULL value for sum. This
surprised me at first as I thought the WHERE clause should apply before the
OUTER JOIN but apparently not. I then tried the following:

SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and
(radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16
15:16:42' OR radacct.ACCTSTARTTIME ISNULL) GROUP BY
phones.CALLINGSTATIONID;

This query returns 1368 rows, which includes some NULL values for sum,
however still short of the 1386 rows I am looking for. Close, but no cigar!

Can someone please point out to me what I (or alternatively Postgresql) is
doing wrong so that I can get a list of all my customer's phones with the
usage for the month beside them (and NULL/Zero if they haven't been used
that month)

select version();
   version
-
 PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0
(SUSE Linux)


Thanks in Advance
-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc


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

Re: [GENERAL] Timezones -- what comes out does not go in?

[Tom Lane]

"Julian Scarfe" <[EMAIL PROTECTED]> writes:
> The timezone on our boxes is set to Etc/UCT (possibly a distro default).
> (psql 8.1.4)

> => select to_char(current_timestamp, 'HH24:MI:SS TZ');
>to_char
> --
>  09:05:48 UCT
> (1 row)

> =>  select '09:05:48 UCT'::time;
> ERROR:  invalid input syntax for type time: "09:05:48 UCT"

This works in CVS HEAD, thanks to some hard work by Joachim Wieland.

http://archives.postgresql.org/pgsql-patches/2006-07/msg00077.php
http://archives.postgresql.org/pgsql-committers/2006-07/msg00258.php

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] Timezones -- what comes out does not go in?

[Alvaro Herrera]

Julian Scarfe wrote:
> The timezone on our boxes is set to Etc/UCT (possibly a distro default).
> 
> (psql 8.1.4)
> 
> => select to_char(current_timestamp, 'HH24:MI:SS TZ');
>   to_char
> --
> 09:05:48 UCT
> (1 row)
> 
> =>  select '09:05:48 UCT'::time;
> ERROR:  invalid input syntax for type time: "09:05:48 UCT"
> 
> Is this behaviour expected? Desirable?

It is expected, because back in 8.1 the timezones recognized came from a
hardcoded table.  It's not desirable of course.  But on the other hand,
it's fixed in the development code:

alvherre=# set timezone to 'Etc/UCT';
SET
alvherre=# select to_char(current_timestamp, 'HH24:MI:SS TZ');
   to_char
--
 14:19:05 UCT
(1 fila)

alvherre=# select '14:19:05 UCT'::time;
   time   
--
 14:19:05
(1 fila)


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] Massive slowdown when LIMIT applied

[Tom Lane]

Graeme Hinchliffe <[EMAIL PROTECTED]> writes:
> If I do the following:

> SELECT username,acctstarttime FROM radacct WHERE username='user'  
> ORDER BY acctstarttime;

> again the results come up slightly slower as would be expected but  
> still very quickly.. (a few hundred rows in this case)

> Now, if I do the following:

> SELECT username,acctstarttime FROM radacct WHERE username='user'  
> ORDER BY acctstarttime LIMIT 50;

> I have to wait a very long time, IE instead of 2-3 seconds.. can be  
> as slow as 10+ minutes.

Have you compared EXPLAIN ANALYZE outputs for the two cases?  Presumably
the planner is picking a different plan for the LIMIT case (and guessing
wrong :-()

regards, tom lane

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

Re: [GENERAL] Segmentation Fault

[Tom Lane]

=?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= <[EMAIL PROTECTED]> writes:
> Last night one of my databases broke down temporary because of a 
> segmentation fault.

> At 00:36:21 this was happening in the pgsql log
> 2006-08-16 00:36:21 CEST - LOG:  duration: 14673.110 ms  statement: 
> EXECUTE  amed>  [PREPARE:  select * from app.insert_unitstat($1,$2,$3,$4,$5,$6,$7,
> $8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21)  as result]
> ...
> These statements are called in a plpgsql function and the function is 
> called via JDBC

Given that you're using duration logging and JDBC, I wonder whether you
didn't trip over this recently-identified bug:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00815.php
Patch is here:
http://archives.postgresql.org/pgsql-committers/2006-08/msg00278.php

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] Weird join result

[Harald Armin Massa]

Peter,This returns 1280 rows, none of which are have a NULL value for sum. This
surprised me at first as I thought the WHERE clause should apply before theOUTER JOIN but apparently not. I then tried the following:what gives you the impression that WHERE clauses should be applied before the JOINs ? 
Clearly that is different. First joins, then where propably you want sth like:
SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phonesLEFT OUTER JOIN 
(select * from raddact where radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' OR radacct.ACCTSTARTTIME ISNULL)  raddact ON (phones.CALLINGSTATIONID =radacct.CALLINGSTATIONID
) WHERE  phones.CUSTID = 1 GROUP BY phones.CALLINGSTATIONID;
best wishes Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.

[GENERAL] Determine Transactions Per Second

[John R. Allgood]

Hello

I am needing to determine the tps for a production application
running on PostgreSQL 7.4.7. I know you can use pg_bench and create a
new database to determine an estimate of tps. Is there a way I can get
this info out of my production database.

Thanks

-- 
"What I've done, of course, is total garbage."
-- R. Willard, Pure Math 430a


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

Re: [NOVICE] [GENERAL] DB insert Error

[Jasbinder Bali]

Also, i tried to mannualy run the insert query in the query toolinsert into raw_email ( id  , raw_email  ) values ( 1 ,  'From 
[EMAIL PROTECTED] Tue Apr 15 20:24:47 2003X-MultiHeader: oneX-MultiHeader: twoX-MultiHeader: threeFrom: Simon Cozens <[EMAIL PROTECTED]>To: test
Bcc: [EMAIL PROTECTED]Subject: fooMime-Version: 1.0Content-Type: image/gifContent-Disposition: attachment; filename="1.gif"Content-Transfer-Encoding: base64
X-Operating-System: Linux deep-dark-truthful-mirror 2.4.9X-POM: The Moon is Waxing Gibbous (98% of Full)X-Addresses: The [EMAIL PROTECTED] address is deprecated due to being broken. 
[EMAIL PROTECTED] still works, but simon-cozens.org or netthink.co.uk are preferred.X-Mutt-Fcc: =outbox-200304
Status: ROContent-Length: 1205Lines: 17R0lGODlhDAAMAPcAAAgICBAQEBgYGCkpKTExMTk5OUpKSoyMjJSUlJycnKWlpbW1tc7Ozufn5+/v7/f39///



/ywADAAMAAAIXwAjRICQwIAAAQYUQBAYwUEB
AAACEIBYwMHAhxARNIAIoAAEBBAPOICwkSMCjBAXlKQYgCMABSsjtuQI02UAlC9jFgBJMyYCCCgRMODoseFElx0tCvxYIEAAAwkWRggIADs=' )But it doesn't store the full raw_email field contents
raw_email field has just the follwing stored in it---"From [EMAIL PROTECTED] Tue Apr 15 20:24:47 2003
X-MultiHeader: oneX-MultiHeader: twoX-MultiHeader: threeFrom: Simon Cozens <[EMAIL PROTECTED]>To: testBcc: [EMAIL PROTECTED]
Subject: fooMime-Version: 1.0Content-Type: image/gifContent-Dispo (..)"---Also, as i've already told, even after i commit my transaction, it doesn't insert the record.
Thanks,~JasOn 8/16/06, Jasbinder Bali <[EMAIL PROTECTED]> wrote:
I did commit the transaction by writing EXEC SQL COMMIT; right after the insert statement.Then also its not inserting the record and says:[2998]: ECPGexecute line 97 Ok: INSERT 0 1
and SQLCODE is still 0
~JasOn 8/1/06, Michael Fuhr <
[EMAIL PROTECTED]> wrote:

On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.
Are you committing the transaction?  See the bottom of thefollowing page:
http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
"In the default mode, statements are committed only when EXEC SQLCOMMIT is issued."--Michael Fuhr

Re: [NOVICE] [GENERAL] DB insert Error

[Jasbinder Bali]

I did commit the transaction by writing EXEC SQL COMMIT; right after the insert statement.Then also its not inserting the record and says:[2998]: ECPGexecute line 97 Ok: INSERT 0 1and SQLCODE is still 0
~JasOn 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.
Are you committing the transaction?  See the bottom of thefollowing page:http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
"In the default mode, statements are committed only when EXEC SQLCOMMIT is issued."--Michael Fuhr

Re: [GENERAL] Segmentation Fault

[Poul Møller Hansen]

Given that you're using duration logging and JDBC, I wonder whether you
didn't trip over this recently-identified bug:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00815.php
Patch is here:
http://archives.postgresql.org/pgsql-committers/2006-08/msg00278.php
  


Sorry but didn't, but I noticed this:

Also I must notice that the segfault only occur if
log_min_duration_statement is set to 0

It's currently 1000, so will the patch help ?


Regards, 
Poul




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

Re: [NOVICE] [GENERAL] DB insert Error

[Michael Fuhr]

On Wed, Aug 16, 2006 at 02:59:23AM -0400, Harpreet Dhaliwal wrote:
> I mean can we call a stored procedure as an action of a trigger?

You'll need to write a trigger function that calls the non-trigger
function (what you're referring to as "stored procedure").  Example:

CREATE FUNCTION trigger_function() RETURNS trigger AS $$
BEGIN
PERFORM other_function();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE ON table_name
  FOR EACH ROW EXECUTE PROCEDURE trigger_function();

> Its actually something like the trigger should start a C function after
> insert and the C function has the ECPG code for some more inserts.
> Its similar to the way we dynamically load a shared library while
> executing a stored procedure, as in , executing a fucntion in C file using
> stored procedure/ function.

You have a server-side C function that uses ECPG?  Is there a reason
you're not using SPI?  Are you connecting to a different server?
Or by "function" do you really mean "program," meaning a separate
executable?

http://www.postgresql.org/docs/8.1/interactive/spi.html

-- 
Michael Fuhr

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

   http://archives.postgresql.org

[GENERAL] hint unique result fro union

[Matteo Bertini]

Hello all!I'm quite new to pg, but I'm using it quite a lot in the last few monts.Deeping in new features, I found a question: is it possible to hint an unique result from a select?Mainly, thinking about partitions, I'd like to create a small, frequently accessed partition and a big, rarely accessed partition.
I'd like to hint pg to stop the query on the parent partition at the first found item  (and so hint not to analyze all the childs), because I know the index I'm using is unique.Possible? Usefull?Thanks,
Matteo Bertini

Re: [GENERAL] limitations of constraints, possible bugs

[Bruce Momjian]

Tom Lane wrote:
> craigp <[EMAIL PROTECTED]> writes:
> > for instance, if i create a domain (D) with some constraint, and create a 
> > table
> > with a column of type D[], will that domain constraint be enforced for each
> > element of the array?
> 
> Currently we don't support arrays of domain types (this should get fixed
> sometime).  If we did, then yeah, that's how it'd work.

Added to TODO:

* Add support for arrays of domains

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

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

Re: [GENERAL] Determine Transactions Per Second

[Jeff Davis]

On Wed, 2006-08-16 at 10:24 -0400, John R. Allgood wrote:
> I am needing to determine the tps for a production application
> running on PostgreSQL 7.4.7. I know you can use pg_bench and create a
> new database to determine an estimate of tps. Is there a way I can get
> this info out of my production database.
> 

Look in the pg_stat_database or pg_stat_get_db_xact_commit(oid) (where
oid is the oid of the database).

This number is reset when the statistics are reset, so it may not be the
number of transactions since the database was created. But you can use
it to test TPS easily.

Regards,
Jeff Davis


---(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] Weird join result

[Stephan Szabo]

On Wed, 16 Aug 2006, Peter Nixonn wrote:

> I am getting a result for an JOIN that I think is wrong. Maybe its my
> understanding that is wrong here however, so please be gentle :-)
>
> The "phones" table contains a list of phone numbers and an associated
> customer ID. The radacct table contains a list of all calls made (RADIUS
> Accounting records).
>
> I am doing the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 GROUP BY
> phones.CALLINGSTATIONID;
>
> This query as expected returns 1386 rows (for customer ID 1) which includes
> a number of rows which have a NULL sum as they have not ever connected.
>
> Now, what I want to do is to return the same result set (of 1386 phones),
> but only for a particular time period. I therefore do the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and radacct.ACCTSTARTTIME
> BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' GROUP BY
> phones.CALLINGSTATIONID;
>
> This returns 1280 rows, none of which are have a NULL value for sum. This
> surprised me at first as I thought the WHERE clause should apply before the
> OUTER JOIN but apparently not.

No, in fact it explicitly happens after the join (the order of evaluation
in the theoretical model is basically evaluate the from clause as a table
then apply where on that table to make a new table and so on). You can
either use a subselect in from list or put the condition into the ON to
make its evaluation earlier.

> I then tried the following:
>
> SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets) FROM phones
> LEFT OUTER JOIN radacct ON (phones.CALLINGSTATIONID =
> radacct.CALLINGSTATIONID) WHERE phones.CUSTID = 1 and
> (radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16
> 15:16:42' OR radacct.ACCTSTARTTIME ISNULL) GROUP BY
> phones.CALLINGSTATIONID;
>
> This query returns 1368 rows, which includes some NULL values for sum,
> however still short of the 1386 rows I am looking for. Close, but no cigar!

Imagine you had
phones (callingstationid = 1, custid = 1)
phones (callingstationid = 2, custid = 1)
phones (callingstationid = 3, custid = 1)
radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00)
radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

Now, I believe the outer join should above give you
(callingstationid = 1, custid = 1, acctstarttime 2006-05-10 00:00:00)
(callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid = 1, acctstarttime NULL)

Then apply the where clause
(callingstationid = 2, custid =1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid =1, acctstarttime NULL)

---

With the subselect in from you'd have
 phones (callingstationid = 1, custid = 1)
 phones (callingstationid = 2, custid = 1)
 phones (callingstationid = 3, custid = 1)
 radacct (callingstationid = 1, acctstarttime 2006-05-10 00:00:00)
 radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

The first radacct row doesn't pass, so it's not in the subselect output
which should then look like:
 subsel_radacct (callingstationid = 2, acctstarttime 2006-08-10 00:00:00)

Then, I believe the outer join should give you
(callingstationid = 1, custid = 1, acctstarttime NULL)
(callingstationid = 2, custid = 1, acctstarttime 2006-08-10 00:00:00)
(callingstationid = 3, custid = 1, acctstarttime NULL)

And then you apply the where clause again and all the rows go through.

---(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] Triggers invoking a stored procedure or a C function

[Harpreet Dhaliwal]

Hi,
Conventionally a trigger would fire a few sql queries on a particular event and we have standard code for that.
 
My requirement is to start a stored procedure or a C function as a trigger action.
 
Is this possible?
 
Thanks,
~Harpreet

Re: [GENERAL] hint unique result fro union

[Steve Atkins]

On Aug 16, 2006, at 8:23 AM, Matteo Bertini wrote:


Hello all!

I'm quite new to pg, but I'm using it quite a lot in the last few  
monts.


Deeping in new features, I found a question: is it possible to hint  
an unique result from a select?


Mainly, thinking about partitions, I'd like to create a small,  
frequently accessed partition and a big, rarely accessed partition.


I'd like to hint pg to stop the query on the parent partition at  
the first found item (and so hint not to analyze all the childs),  
because I know the index I'm using is unique.


Possible? Usefull?



"select foo from bar limit 1" ?

I don't know if there's any guaranteed ordering of results from
a union query, though, and that's what a query on a set of
inherited tables will expand to, pretty much.

Cheers,
  Steve



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

Re: [GENERAL] Partial indexes Vs standard indexes : Insert

[Jeff Davis]

On Wed, 2006-08-16 at 12:15 +0200, MaXX wrote:
> > That should work. Keep in mind that the main idea of an index is to
> > reduce the number of pages that have to be fetched from disk. If the
> > record size is small, you may have at least one ICMP packet on 50% (or
> > more) of the disk pages even if ICMP packets only make up 1% of the
> > total records. Even if they aren't inserted randomly, updates/deletes
> > may randomize the distribution somewhat. If you have an ICMP packet on
> > every other page, you might not be impressed with the performance versus
> > a sequential scan. However, it could be a big win if you have other
> > WHERE conditions aside from just the packet type.
> OK, so that works well for queries where there is a very few rows in the 
> index in regard of the table size, and as long as this still true.
> 

Indexes work well when you have a WHERE clause that's highly restrictive
and reduces the number of pages needed from disk substantially. Partial
indexes work well when you're concerned about the index growing too
large (and requiring more maintenance), especially with keys you don't
need.

> > The planner tries to take all of these things into consideration to some
> > degree. The best test is to try EXPLAIN or EXPLAIN ANALYZE to see what
> > plan it makes. Also, try forcing different types of plans to see if the
> > planner is making the right choice.
> I did some test and with both your reply and the one of Gregory Stark, I 
> was able identify what are good indexes and speed up the thing...
> 

Excellent! Results are what count :)

Regards,
Jeff Davis


---(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] Move db files from one pgsql instance to another

[Javier de la Torre]

Hi,

I am working right now in an strategy to improve the performance on my
server. The situation is this:

I have a very large database that it is only update once a month, but
when is updated I have to process a lot of things on the data to
create caches, aditional tables, etc.

The processing of the data can take up to 2 days in my machine so I
was considering the following strategy.

I have another machine that takes the new data, process it and when is
done I replace the files from the production server with the ones of
the processing server.

In this way I avoid my production server to become really slow for 2
days every month.

Does this sound reasonable? Am I going to find any trouble just moving
the db files inside the data/base folder? I thought I would stop
PostgreSQL, do the replacement of files, actually just changing a
symbolyc link, and start the server again. I will only have my db
stopped for 5 sec. once a month.

Has anybody experince on something like this?

Thanks in advance.

Javier.

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

Re: [GENERAL] Best approach for a "gap-less" sequence

[elein]

On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
> On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> > On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> > > Jorge Godoy wrote:
> > > > Chris <[EMAIL PROTECTED]> writes:
> > > >>I'm not sure what type of lock you'd need to make sure no other
> > > >> transactions updated the table (see
> > > >>http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in
> > > >> theory" something like this should work:
> > > >>
> > > >>begin;
> > > >>select id from table order by id desc limit 1;
> > > >>insert into table (id, blah) values (id+1, 'blah');
> > > >>commit;
> > > >
> > > > This is part of the solution, yes.  But I would still need locking this
> > > > table so that no other concurrent transaction gets another "id".  I
> > > > don't want to lock the main table --
> > >
> > > Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
> > > locking the table?
> >
> > Nope, concurrent transactions won't work.
> >
> > Let current max id = x
> >
> > Transaction 1 (t1) does a select max(id) for update, gets a lock on the
> > last tuple at the time of the select, and gets x as a value for max id
> >
> > Transaction 2 (t2) does a select max(id) for update, has to wait for t1
> > to release its lock.
> >
> > t1 inserts (x+1) as the new max id of the table.  t1 releases its lock
> >
> > t2 is granted the lock on the tuple it has been waiting for, which
> > contains the max id of x
> >
> > t2 tries to insert a value of x+1, insert fails (if it doesn't, you
> > really want to have a close look at your constraints :-)
> >
> 
> I am still working through this stuff myself, but the following excerpt from 
> the documentation would seem to contradict what you are saying. See the part 
> marked with ***. t2 should see a new max(id) after t1 commits and therefore 
> insert(x+1) would succeed.
> 
> http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE
> 
> "FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as 
> though for update. This prevents them from being modified or deleted by other 
> transactions until the current transaction ends. That is, other transactions 
> that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be 
> blocked until the current transaction ends.*** Also, if an UPDATE, DELETE, or 
> SELECT FOR UPDATE from another transaction has already locked a selected row 
> or rows, SELECT FOR UPDATE will wait for the other transaction to complete, 
> and will then lock and return the updated row (or no row, if the row was 
> deleted).***"

If this is true the solution for a transactional, gapless sequence is possible
for table.gl_id  where updated from count.gl_id.  It is simple.  However, it
*depends* on the fact that the second transaction getting the newly updated
record from the first transaction.  It seems pretty clear, not counting 
aggregates,
that this is true from this doc snippet.  Speak now, if someone doesn't read it
this way!  I'd like to understand why.

If it weren't true, there would also be a workaround which caught a duplicate
value and tried again, looping.

I may publish the gapless sequence technique on general bits if there is no
discrepancy in the understanding of the status of the second transaction's
row value (updated).

--elein
[EMAIL PROTECTED]


 

> -- 
> Adrian Klaver 
> [EMAIL PROTECTED]
> 
> ---(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

Re: [GENERAL] Move db files from one pgsql instance to another

[Jeff Davis]

On Wed, 2006-08-16 at 20:04 +0200, Javier de la Torre wrote:
> I am working right now in an strategy to improve the performance on my
> server. The situation is this:
> 
> I have a very large database that it is only update once a month, but
> when is updated I have to process a lot of things on the data to
> create caches, aditional tables, etc.
> 
> The processing of the data can take up to 2 days in my machine so I
> was considering the following strategy.
> 
> I have another machine that takes the new data, process it and when is
> done I replace the files from the production server with the ones of
> the processing server.
> 

Start out here:
http://www.postgresql.org/docs/8.1/static/backup-online.html

You might also look into Slony-I:
http://www.slony.info

Do those seem like they will solve your problem?

Regards,
Jeff Davis


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

   http://archives.postgresql.org

Re: [GENERAL] hint unique result fro union

[Jaime Casanova]

Mainly, thinking about partitions, I'd like to create a small, frequently
accessed partition and a big, rarely accessed partition.

I'd like to hint pg to stop the query on the parent partition at the first
found item (and so hint not to analyze all the childs), because I know the
index I'm using is unique.



what you want is know as "Constraint Exclusion"
http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
  Richard Cook

---(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] Move db files from one pgsql instance to another

[Javier de la Torre]

Yes!

Thanks. It pointed me out to File system level backup. And there I
found a very important hint... you have to restore the whole database
cluster. I suppose this means that I have to transfer all databases in
my postgresql...

What is actually a pity because there is a db on the production site
that will be lost... well, I can backup this one without ptoblems and
reinsert it after.

Thanks again.

Javier.



On 8/16/06, Jeff Davis <[EMAIL PROTECTED]> wrote:

On Wed, 2006-08-16 at 20:04 +0200, Javier de la Torre wrote:
> I am working right now in an strategy to improve the performance on my
> server. The situation is this:
>
> I have a very large database that it is only update once a month, but
> when is updated I have to process a lot of things on the data to
> create caches, aditional tables, etc.
>
> The processing of the data can take up to 2 days in my machine so I
> was considering the following strategy.
>
> I have another machine that takes the new data, process it and when is
> done I replace the files from the production server with the ones of
> the processing server.
>

Start out here:
http://www.postgresql.org/docs/8.1/static/backup-online.html

You might also look into Slony-I:
http://www.slony.info

Do those seem like they will solve your problem?

Regards,
Jeff Davis




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

  http://archives.postgresql.org

Re: [GENERAL] limitations of constraints, possible bugs

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Added to TODO:
>   * Add support for arrays of domains

I think BTW that this should be pretty low-hanging fruit given the
recent rewrite of domain_in().  I don't think array_in() should need to
care now whether the array elements are domains, whereas in the original
design there would have had to be explicit support for domains added to
the array code.  There might be some performance issues, ie, making sure
the stack of array_in and domain_in can cache data at both levels, but
that seems eminently solvable.

regards, tom lane

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

   http://archives.postgresql.org

Re: [NOVICE] [GENERAL] DB insert Error

[Guy Rouillier]

Harpreet Dhaliwal wrote:
> Forgot to write that that was my question.
> I mean can we call a stored procedure as an action of a trigger?

Sure, here's a working example from a running application:

CREATE OR REPLACE FUNCTION assign_detail_device_type_seq() RETURNS
trigger AS $$
BEGIN
IF NEW.det_device_type_id is NULL THEN
NEW.det_device_type_id := (SELECT
NEXTVAL('rumba.det_device_type_seq'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tDetDevType
   BEFORE INSERT
   ON rumba.DETAIL_DEVICE_TYPE
   FOR EACH ROW
  EXECUTE PROCEDURE assign_detail_device_type_seq();


> 
> 
> On 8/16/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
> Its actually something like the trigger should start a C function
> after insert and the C function has the ECPG code for some more
> inserts.  
> Its similar to the way we dynamically load a shared library while
> executing a stored procedure, as in , executing a fucntion in C file
> using stored procedure/ function.  
> 
> Harpreet
> 
> 
> On 8/16/06, Michael Fuhr <[EMAIL PROTECTED] > wrote:
> On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:
>> I changed the datatype from varchar[] to varchar
>> ECPGdebug(1,stderr) says
>> [2998]: ECPGexecute line 97 Ok: INSERT 0 1
>> 
>> Its not inserting any record even though sqlcode is 0.
> 
> Are you committing the transaction?  See the bottom of the
> following page:
> 
> http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
> 
> "In the default mode, statements are committed only when EXEC SQL
> COMMIT is issued."



-- 
Guy Rouillier

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

   http://archives.postgresql.org

Re: [GENERAL] Timezones -- what comes out does not go in?

[Julian Scarfe]

From: "Tom Lane" <[EMAIL PROTECTED]>


This works in CVS HEAD, thanks to some hard work by Joachim Wieland.


One of these days I'll find an issue *before* you folks have patched it. :-)

Thanks

Julian 




---(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: [NOVICE] [GENERAL] DB insert Error

[Jasbinder Bali]

Tried everthing but nothing seems to work.:(On 8/16/06, Jasbinder Bali <[EMAIL PROTECTED]> wrote:
Also, i tried to mannualy run the insert query in the query tool
insert into raw_email ( id  , raw_email  ) values ( 1 ,  'From 
[EMAIL PROTECTED] Tue Apr 15 20:24:47 2003X-MultiHeader: oneX-MultiHeader: twoX-MultiHeader: threeFrom: Simon Cozens <
[EMAIL PROTECTED]>To: test
Bcc: [EMAIL PROTECTED]Subject: fooMime-Version: 1.0Content-Type: image/gifContent-Disposition: attachment; filename="
1.gif"Content-Transfer-Encoding: base64
X-Operating-System: Linux deep-dark-truthful-mirror 2.4.9X-POM: The Moon is Waxing Gibbous (98% of Full)X-Addresses: The 
[EMAIL PROTECTED] address is deprecated due to being broken. 
[EMAIL PROTECTED] still works, but 
simon-cozens.org or netthink.co.uk are preferred.X-Mutt-Fcc: =outbox-200304
Status: ROContent-Length: 1205Lines: 17R0lGODlhDAAMAPcAAAgICBAQEBgYGCkpKTExMTk5OUpKSoyMjJSUlJycnKWlpbW1tc7Ozufn5+/v7/f39///



/ywADAAMAAAIXwAjRICQwIAAAQYUQBAYwUEB
AAACEIBYwMHAhxARNIAIoAAEBBAPOICwkSMCjBAXlKQYgCMABSsjtuQI02UAlC9jFgBJMyYCCCgRMODoseFElx0tCvxYIEAAAwkWRggIADs=' )But it doesn't store the full raw_email field contents
raw_email field has just the follwing stored in it---"From 
[EMAIL PROTECTED] Tue Apr 15 20:24:47 2003
X-MultiHeader: oneX-MultiHeader: twoX-MultiHeader: threeFrom: Simon Cozens <[EMAIL PROTECTED]
>To: testBcc: [EMAIL PROTECTED]
Subject: fooMime-Version: 1.0Content-Type: image/gifContent-Dispo (..)"---Also, as i've already told, even after i commit my transaction, it doesn't insert the record.
Thanks,~JasOn 8/16/06, Jasbinder Bali <
[EMAIL PROTECTED]> wrote:

I did commit the transaction by writing EXEC SQL COMMIT; right after the insert statement.Then also its not inserting the record and says:[2998]: ECPGexecute line 97 Ok: INSERT 0 1
and SQLCODE is still 0
~JasOn 8/1/06, Michael Fuhr <

[EMAIL PROTECTED]> wrote:

On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.
Are you committing the transaction?  See the bottom of thefollowing page:

http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
"In the default mode, statements are committed only when EXEC SQLCOMMIT is issued."--Michael Fuhr

Re: [GENERAL] plpgsql dynamic queries and optional arguments

[Curtis Scheer]

Allan,

Thanks for the reply I guess what I am actually looking for is an example of
a dynamic SQL select statement similar to how a static sql select can select
into a variable.

Thanks,
Curtis


Curtis,

Here is an example function that uses dynamic sql.
I use it under 7.4.5

Hope this helps.

Allan


---(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: [NOVICE] [GENERAL] DB insert Error

[Michael Fuhr]

On Wed, Aug 16, 2006 at 02:42:46PM -0400, Jasbinder Bali wrote:
> Tried everthing but nothing seems to work.
> :(

Could you provide a simplified but complete example that shows what
you're doing?  That is, all SQL statements and C code necessary to
create a table and whatever functions you're using.  I've done a
little testing and haven't been able to reproduce your results, so
apparently my tests don't match what you're doing.

-- 
Michael Fuhr

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

Re: [GENERAL] Best approach for a "gap-less" sequence

[Berend Tober]

elein wrote:


On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:


On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:


On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:

Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
locking the table?


If this is true the solution for a transactional, gapless sequence ...
I may publish the gapless sequence technique on general bits if there is no
discrepancy in the understanding of the status of the second transaction's
row value (updated).



/*
Hi Elein, I'm an avid reader of your General Bits column.

One of my favorite sayings is "nothing beats empirical evidence", so 
regardless of what people interpret the documentation to say, here is a 
simplified description of an actual working implementation of how it is 
done:


The background:

A business requirement is to generate table rows that have uniformly 
increasing, whole number sequences, i.e., the "gap-less" sequence. In 
this particular case the situation requires multiple such sequences 
within the same table -- for each employee, there is a 
uniformly-sequenced set of expense reports. I use the term "compound 
sequence" for this situation because the expense reports are sequenced 
independently on a per-employee basis.


Specifically, I have employee data in
*/

CREATE SCHEMA test;
SET search_path = test, public, pg_catalog;

CREATE TABLE employee
(
employee_pk SERIAL, -- Identifies the employee.
/*
...lots of non-relevent columns omitted ...
*/
expense_report_seq int4 DEFAULT 0, -- Compound sequence control.
CONSTRAINT employee_pkey PRIMARY KEY (employee_pk)
);

/*
The expense_report_seq column stores the most-recently-used expense 
report number for each employee, i.e., it is the control value for the 
compound sequences that appear in

*/

CREATE TABLE expense
(
employee_pk int4 NOT NULL,
expense_report_pk int4 NOT NULL,
/*
...lots of non-relevent columns omitted ...
*/
  CONSTRAINT expense_report_pkey PRIMARY KEY (employee_pk, 
expense_report_pk),

CONSTRAINT expense_fkey FOREIGN KEY (employee_pk)
REFERENCES employee (employee_pk)
);


/*
A before-insert trigger handles the compound sequence:
*/

CREATE OR REPLACE FUNCTION expense_bit()
  RETURNS "trigger" AS
'
BEGIN
UPDATE employee
SET expense_report_seq = (expense_report_seq + 1)
WHERE employee_pk = NEW.employee_pk;
SELECT INTO NEW.expense_report_pk expense_report_seq
FROM employee WHERE employee_pk = NEW.employee_pk;
  RETURN new;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

/*
Other triggers handle allowed deletion and correction of some expense 
report data under certain circumstances.

*/

CREATE TRIGGER expense_bit
  BEFORE INSERT
  ON expense
  FOR EACH ROW
  EXECUTE PROCEDURE expense_bit();


/*
Turns out the SELECT ... FOR UPDATE syntax is not even required because 
code inside functions, particularly trigger functions as illustrated 
here, is treated as a transaction and the UPDATE statement locks the 
effected row until the trigger completes.

*/

-- Then test it:

INSERT INTO employee DEFAULT VALUES;
INSERT INTO employee DEFAULT VALUES;

-- In two separate sessions, run many competing inserts:

SET search_path = test, public, pg_catalog;
INSERT INTO expense VALUES (1);
INSERT INTO expense VALUES (1);
/*
...
*/
INSERT INTO expense VALUES (1);


INSERT INTO expense VALUES (2);
INSERT INTO expense VALUES (2);
/*
...
*/
INSERT INTO expense VALUES (2);

-- And check your results:
SELECT * FROM expense order by 1,2;
/*
Regards,
Berend Tober
*/


---(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] Triggers invoking a stored procedure or a C function

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-16 11:55:39 -0400:
> Hi,
> Conventionally a trigger would fire a few sql queries on a particular event
> and we have standard code for that.
> 
> My requirement is to start a stored procedure or a C function as a trigger
> action.
> 
> Is this possible?

Besides the fact that PostgreSQL doesn't have stored procedures,
only "SQL-invoked routines", both SQL and external, you can.
After all,
http://www.postgresql.org/docs/8.1/static/sql-createtrigger.html
says

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

The above page also links to "33.4. A Complete Example"
(http://www.postgresql.org/docs/8.1/static/trigger-example.html),
which revolves around a trigger function written in C.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

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

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

Re: [NOVICE] [GENERAL] DB insert Error

[Jasbinder Bali]

I'm sending u the main() function that has the ECPG Insert statement and commit in it.---START---#include <
stdio.h>#include #include #include 
EXEC SQL INCLUDE sqlca;
#define MP 10#define MSG_LEN 1024000#define FLD_LEN 512
char *msg_parts[MP], *msg_header[MP], *msg_body[MP];char headers[MSG_LEN]="", body[MSG_LEN]="";char mime_version[FLD_LEN]="", content_type[FLD_LEN]="", content_id[FLD_LEN]="";
char content_description[FLD_LEN]="", content_transfer_encoding[FLD_LEN]="";char content_length[FLD_LEN]="",content_disposition[FLD_LEN]="";int part_num=0;int pnum=0;

char *dst;
int main(int argc, char *argv[])  {
ECPGdebug (1, stderr);
EXEC SQL CONNECT TO tcp:postgresql://192.168.0.110/xyz USER jsbali;
printf("sql code is %d \n",SQLCODE);    void split_head_from_body(char *ch, char *o_headers, char *o_body);    void limit_fields(char *instr, char *outstr, char *fieldname);    void unlimit_fields(char *instr, char *outstr, char *fieldname);
    void parts(char *intype, char *bdy, char *inbody, int pcount);
    FILE   *fp;
EXEC SQL BEGIN DECLARE SECTION;    char ch[MSG_LEN];EXEC SQL END DECLARE SECTION;
 
  int  i,j;    int  lines;    unsigned int len;    char from[FLD_LEN]="", to[FLD_LEN]="", subject[FLD_LEN]="", date[FLD_LEN]="";    char mesid[FLD_LEN]="", sender[FLD_LEN]="", cc[FLD_LEN]="", bcc[FLD_LEN]="";
    char replyto[FLD_LEN]="", inreplyto[FLD_LEN]="", ref[FLD_LEN]="";    char rec[FLD_LEN]="", comments[FLD_LEN]="", keywords[FLD_LEN]="", return_path[FLD_LEN]="";
    char xapp[FLD_LEN]="";    char resent_date[FLD_LEN]="",
resent_from[FLD_LEN]="", resent_sender[FLD_LEN]="",
resent_to[FLD_LEN]="", resent_cc[FLD_LEN]="", resent_bcc[FLD_LEN]="",
resent_mid[FLD_LEN]="";
    char boundary[FLD_LEN]="--";    char *str, *pstr;
    if(argc!=2)  {printf("You forgot to enter a filename\n");   exit(0);  }
    if((fp=fopen(argv[1],"r"))==NULL)  {printf("cannot open the file\n");   exit(0);  }
/*  read the email from a file  */
    lines=0;    while(!feof(fp))  {ch[i]=fgetc(fp);   if(ch[i]=='\n') lines++;   i++;  }    ch[i-1]='\0';    fclose(fp);    len=strlen(ch);
//815    printf("- Content of the Raw email---\n");//815    printf("%s\n",ch);
 
//815    printf("- the end of the content \n");
//EXEC SQL SELECT MAX(case_no) INTO :caseno FROM raw_email;
EXEC SQL INSERT INTO raw_email (id,raw_email) VALUES (1,:ch);EXEC SQL COMMIT;printf("sql code is %d \n",SQLCODE);
//    printf("-There are %d lines in the email.-\n", lines);//    printf("-The character length is  %d -\n", len);
/*  seperate the header and the message body  */
    split_head_from_body(ch,headers,body);
//    printf("-\n");//    printf("--The header of the email:\n");//    printf("%s\n",headers);//    printf("-\n");

//    printf("-\n");//    printf("--The body of the email:\n");//    printf("%s\n",body);//    printf("-\n");

/*  all the fields in an email header    *//*  Bcc, Cc, Comments, Content-Type, *//*  Content-Transfer-Encoding, Content-Disposition,  *//*  Date, From, Keywords, Message-ID, MIME-Version,  */
/*  Organizaion, Received, Reference, Reply-To,  *//*  Resent-From, Resent-To, Return-Path, Sender, *//*  Subject, To, X-***   */
/*  Now, get the fields in the headers   *//*  for limited fields   *//*   from, sender, reply-to, to, cc, bcc,    *//*   message-id, in-reply-to, reference, */
/*   subject */
 
 limit_fields(headers,from,"From: ");    limit_fields(headers,to,"To: ");    limit_fields(headers,subject,"Subject: ");    limit_fields(headers,date,"Date: ");    limit_fields(headers,mesid,"Message-ID: ");
    limit_fields(headers,sender,"Sender: ");    limit_fields(headers,cc,"Cc: ");    limit_fields(headers,bcc,"Bcc: ");    limit_fields(headers,replyto,"Reply-To: ");
    limit_fields(headers,inreplyto,"In-Reply-To: ");    limit_fields(headers,ref,"Reference: ");
/*  new for MIME *//*   Mime-Version, Content-Type, *//*   Content-ID, Content-Description,    *//*   Content-Transfer-Encoding, Content-Length,  */
/*   Content-Disposition */
    limit_fields(headers,mime_version,"MIME-Version: ");    limit_fields(headers,content_type,"Content-Type: ");    limit_fields(headers,content_transfer_encoding,"Content-Transfer-Encoding: ");
    limit_fields(headers,content_id,"Content-ID: ");    limit_fields(headers,content_description,"Content-Description: ");    limit_fields(headers,content_length,"

Re: [NOVICE] [GENERAL] DB insert Error

[Jasbinder Bali]

This is the create table statement..CREATE TABLE raw_email(  id int4 NOT NULL,  raw_email varchar,  parsed_flag bool NOT NULL DEFAULT false,  CONSTRAINT pk_rawemail PRIMARY KEY (id)) WITHOUT OIDS;
ALTER TABLE raw_email OWNER TO postgres;On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 02:42:46PM -0400, Jasbinder Bali wrote:> Tried everthing but nothing seems to work.
> :(Could you provide a simplified but complete example that shows whatyou're doing?  That is, all SQL statements and C code necessary tocreate a table and whatever functions you're using.  I've done a
little testing and haven't been able to reproduce your results, soapparently my tests don't match what you're doing.--Michael Fuhr

Re: [GENERAL] plpgsql dynamic queries and optional arguments

[Michael Fuhr]

On Wed, Aug 16, 2006 at 02:36:44PM -0500, Curtis Scheer wrote:
> Thanks for the reply I guess what I am actually looking for is an example of
> a dynamic SQL select statement similar to how a static sql select can select
> into a variable.

In 8.1 you can select a single row or columns of a single row with
INTO:

  EXECUTE 'SELECT * FROM foo' INTO rec;

Earlier versions don't support INTO with EXECUTE but you can use a
loop to achieve the same effect:

  FOR rec IN EXECUTE 'SELECT * FROM foo' LOOP
  -- do stuff with rec
  END LOOP;

Here are links to the relevant documentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

-- 
Michael Fuhr

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

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

Re: [GENERAL] Segmentation Fault

[Tom Lane]

=?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= <[EMAIL PROTECTED]> writes:
>> Given that you're using duration logging and JDBC, I wonder whether you
>> didn't trip over this recently-identified bug:
>> http://archives.postgresql.org/pgsql-hackers/2006-08/msg00815.php
>> Patch is here:
>> http://archives.postgresql.org/pgsql-committers/2006-08/msg00278.php

> Sorry but didn't, but I noticed this:
> Also I must notice that the segfault only occur if
> log_min_duration_statement is set to 0

I don't believe that statement actually ... it might have chanced to act
that way in one or two trials for Sergey, but since the bug essentially
consists in access to already-freed-and-perhaps-reused memory, it's not
very predictable whether it will fail visibly or not.  In any case the
problem could occur for any duration-logging attempt.

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] COLLATE

[Kevin Murphy]

Filip Rembiałkowski wrote:

Greg Stark wrote:

But the closest partial solution suggested so far is the pg_xfrm (sic) 
function that
has been implemented and posted at least three times by three different
posters to the postgres mailing lists. In the interest of avoiding a fourth
independent implementation I'll attach the one I use below, it's not big.


But I have no idea how to compile/install it.

I tried compiling but it failed:

pgdba ( at ) sulaco:~/src/postgresql-8.1.3/src$ gcc -I ./include -fPIC -c 
pg_strxfrm.c
pg_strxfrm.c: In function 'pg_strxfrm':
pg_strxfrm.c:98: error: 'Warn_restart' undeclared (first use in this function)
pg_strxfrm.c:98: error: (Each undeclared identifier is reported only once
pg_strxfrm.c:98: error: for each function it appears in.)


What is the answer to Filip's question?  I didn't see an answer in the list 
archives.  I've seen several copies of Joe Conway's pg_strxfrm.c code on the 
web, and it always refers to the Warn_restart variable, which doesn't seem to 
exist in the 8.1.4 code that I'm using.

I am working with a database in UTF-8 encoding using "C" collation - but I'd occasionally like to ORDER BY columns containing real UTF-8 data. 


Would the pg_strxfrm() function get used in a new operator class function?   
I'll read up on operator classes in chapter 32.14 of the docs, but if someone 
has a simple example, it might help other searchers of the archives.

Thanks,
Kevin Murphy




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

Re: [NOVICE] [GENERAL] DB insert Error

[Michael Fuhr]

On Wed, Aug 16, 2006 at 04:11:23PM -0400, Jasbinder Bali wrote:
> I'm sending u the main() function that has the ECPG Insert statement and
> commit in it.

The program you posted isn't a simplified example as I requested.
The idea is that anybody should be able to compile the code and run
it; that's not possible with the code you posted because it's
incomplete.  It also contains many lines that aren't relevant to
the problem, which makes it harder to focus on what is relevant.

While stripping down the code I noticed a problem: it never initializes
the i variable before doing this:

>while(!feof(fp))
>  {ch[i]=fgetc(fp);
>   if(ch[i]=='\n') lines++;
>   i++;
>  }
>ch[i-1]='\0';

On my system that results in a segmentation fault and core dump
because i contains garbage, causing ch[i] to point somewhere illegal.
It's possible that on your system i contains garbage but that ch[i]
points to valid memory, just not to where it should.  As a result,
the ch you insert into the database doesn't contain the data it's
supposed to.  See if initializing i = 0 fixes the problem.  Most
compilers have options to warn about uninitialized variables; I'd
recommend using them.

If that doesn't help then please post a small (10-20 line), complete
program that anybody could compile and run.

-- 
Michael Fuhr

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

Re: [GENERAL] Best approach for a "gap-less" sequence

[Dawid Kuroczko]

On 8/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote:

I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.

We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the transaction fails then when I rollback the
sequence will already have been incremented.

So, today I have a control table and I acquire a SHARE ROW EXCLUSIVE lock to
it, read the value, increase it, do what I need and then I COMMIT the
transaction, ensuring that the sequence has no gaps.

Is there a better way to guarantee that there will be no gaps in my sequence
if something goes wrong with my transaction?


Hmm, I would do it this way:

-- First prepare a table for keeping gapless sequence, say:
CREATE TABLE gapless_seq (
   gseq_name varchar(256) PRIMARY KEY,
   gseq_value integer NOT NULL
);
-- ...and populate it:
INSERT INTO gapless_seq VALUES('tax_id', '1');

-- then create a function to retrieve the values:
CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
   DECLARE
  n integer;
   BEGIN
  SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t
FOR UPDATE;
  UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t;
  RETURN n;
   END;
$$ STABLE LANGUAGE PLpgsql;

-- ...and use it as default in table definiton
CREATE TABLE taxdata (
   tax_id integer PRIMARY KEY DEFAULT gseq_nextval('tax_id'),
   customer text,
   when timestamptz
);

...etc.  SELECT ... FOR UPDATE woud ensure a row lock on "gapless sequence",
a PLpgsql function would make a nice wrapper for it (so it would be usable more
or less similar to real sequences), and it should work.

I did not test the code right now, but I've written something similar to
it some time ago, and it worked fine.  Remember to vacuum gapless_seq
table frequently and don't expect stellar performance from it.

  Regards,
 Dawid

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

Re: [GENERAL] Best approach for a "gap-less" sequence

[Dawid Kuroczko]

On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:

-- then create a function to retrieve the values:
CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
DECLARE
   n integer;
BEGIN
   SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t
FOR UPDATE;
   UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t;
   RETURN n;
END;
$$ STABLE LANGUAGE PLpgsql;

  ^^^
VOLATILE of course!


Regards,
  Dawid

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

  http://archives.postgresql.org

Re: [GENERAL] COLLATE

[Tom Lane]

Kevin Murphy <[EMAIL PROTECTED]> writes:
> What is the answer to Filip's question?  I didn't see an answer in the list 
> archives.  I've seen several copies of Joe Conway's pg_strxfrm.c code on the 
> web, and it always refers to the Warn_restart variable, which doesn't seem to 
> exist in the 8.1.4 code that I'm using.

Warn_restart hasn't existed since PG 7.4.  I would imagine that the code
needs to be tweaked to use a PG_TRY construct instead of direct setjmp
hacking.

regards, tom lane

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

Re: [GENERAL] Move db files from one pgsql instance to another

[Jeff Davis]

On Wed, 2006-08-16 at 20:37 +0200, Javier de la Torre wrote:
> Yes!
> 
> Thanks. It pointed me out to File system level backup. And there I
> found a very important hint... you have to restore the whole database
> cluster. I suppose this means that I have to transfer all databases in
> my postgresql...
> 
> What is actually a pity because there is a db on the production site
> that will be lost... well, I can backup this one without ptoblems and
> reinsert it after.

If you look at Slony, that's a table-level backup solution that may fit
your needs. Online backup and PITR operate on the entire database
cluster, but with Slony you can replicate on a per-table basis.

Regards,
Jeff Davis


---(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: [NOVICE] [GENERAL] DB insert Error

[Harpreet Dhaliwal]

Also, I think you should check the special characters in the text that you
are trying to store.
 
I realize from one of your emails that when u run the same query using the PGAdmin query tool, only a part
of the the varchar gets stores and rest it trimmed.
 
This has something to do with the special characters like single quote or a back slash.
 
Harpreet. 
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 04:11:23PM -0400, Jasbinder Bali wrote:> I'm sending u the main() function that has the ECPG Insert statement and
> commit in it.The program you posted isn't a simplified example as I requested.The idea is that anybody should be able to compile the code and runit; that's not possible with the code you posted because it's
incomplete.  It also contains many lines that aren't relevant tothe problem, which makes it harder to focus on what is relevant.While stripping down the code I noticed a problem: it never initializesthe i variable before doing this:
>while(!feof(fp))>  {ch[i]=fgetc(fp);>   if(ch[i]=='\n') lines++;>   i++;>  }>ch[i-1]='\0';On my system that results in a segmentation fault and core dump
because i contains garbage, causing ch[i] to point somewhere illegal.It's possible that on your system i contains garbage but that ch[i]points to valid memory, just not to where it should.  As a result,the ch you insert into the database doesn't contain the data it's
supposed to.  See if initializing i = 0 fixes the problem.  Mostcompilers have options to warn about uninitialized variables; I'drecommend using them.If that doesn't help then please post a small (10-20 line), complete
program that anybody could compile and run.--Michael Fuhr---(end of broadcast)---TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Best approach for a "gap-less" sequence

[Adrian Klaver]

On Wednesday 16 August 2006 10:59 am, elein wrote:
> On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
> > On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> > > On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> > > > Jorge Godoy wrote:
> > > > > Chris <[EMAIL PROTECTED]> writes:
> > > > >>I'm not sure what type of lock you'd need to make sure no other
> > > > >> transactions updated the table (see
> > > > >>http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but
> > > > >> "in theory" something like this should work:
> > > > >>
> > > > >>begin;
> > > > >>select id from table order by id desc limit 1;
> > > > >>insert into table (id, blah) values (id+1, 'blah');
> > > > >>commit;
> > > > >
> > > > > This is part of the solution, yes.  But I would still need locking
> > > > > this table so that no other concurrent transaction gets another
> > > > > "id".  I don't want to lock the main table --
> > > >
> > > > Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
> > > > locking the table?
> > >
> > > Nope, concurrent transactions won't work.
> > >
> > > Let current max id = x
> > >
> > > Transaction 1 (t1) does a select max(id) for update, gets a lock on the
> > > last tuple at the time of the select, and gets x as a value for max id
> > >
> > > Transaction 2 (t2) does a select max(id) for update, has to wait for t1
> > > to release its lock.
> > >
> > > t1 inserts (x+1) as the new max id of the table.  t1 releases its lock
> > >
> > > t2 is granted the lock on the tuple it has been waiting for, which
> > > contains the max id of x
> > >
> > > t2 tries to insert a value of x+1, insert fails (if it doesn't, you
> > > really want to have a close look at your constraints :-)
> >
> > I am still working through this stuff myself, but the following excerpt
> > from the documentation would seem to contradict what you are saying. See
> > the part marked with ***. t2 should see a new max(id) after t1 commits
> > and therefore insert(x+1) would succeed.
> >
> > http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UP
> >DATE-SHARE
> >
> > "FOR UPDATE causes the rows retrieved by the SELECT statement to be
> > locked as though for update. This prevents them from being modified or
> > deleted by other transactions until the current transaction ends. That
> > is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE
> > of these rows will be blocked until the current transaction ends.***
> > Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction
> > has already locked a selected row or rows, SELECT FOR UPDATE will wait
> > for the other transaction to complete, and will then lock and return the
> > updated row (or no row, if the row was deleted).***"
>
> If this is true the solution for a transactional, gapless sequence is
> possible for table.gl_id  where updated from count.gl_id.  It is simple. 
> However, it *depends* on the fact that the second transaction getting the
> newly updated record from the first transaction.  It seems pretty clear,
> not counting aggregates, that this is true from this doc snippet.  Speak
> now, if someone doesn't read it this way!  I'd like to understand why.
>
> If it weren't true, there would also be a workaround which caught a
> duplicate value and tried again, looping.
>
> I may publish the gapless sequence technique on general bits if there is no
> discrepancy in the understanding of the status of the second transaction's
> row value (updated).
>
> --elein
> [EMAIL PROTECTED]

After I discovered that aggregates did not work I did some simple tests 
updating a single row table. As I far as I could determine the docs hold 
true :) I only ran three transactions at a time but each saw the incremented 
value from the previous transaction.
-- 
Adrian Klaver   
[EMAIL PROTECTED]

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