date:20110614


On 15/06/2011 7:50 AM, Craig Ringer wrote:


I searched online and found crash dump handler idea has been proposed
and patch for that has already been released if I am not wrong.


It is integrated into PostgreSQL 9.0 as a core part of the server.


Correction - it's in 9.1 not 9.0 . Whoops, I should know that!

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

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

Re: [GENERAL] random backend crashes - how to debug ( Is crash dump handler released ? )

On 15/06/2011 7:50 AM, Craig Ringer wrote:

There's no reason it can't be compiled for PostgreSQL 8.4, though I
never tested that. It shouldn't take long so I'll give it a go and get
back to you.

Okies. I've built a version for 8.4.

You can download it (32-bit only) from:

http://www.postnewspapers.com.au/~craig/webfiles/crashdump_pg_84_32bit/crashdump.dll

If you're on Windows XP or Vista you will also need:

http://www.postnewspapers.com.au/~craig/webfiles/crashdump_pg_84_32bit/dbghelp.dll

... and since this DLL was compiled with VC++ 2008, you'll need the
VC++2008 redist installed if you don't already have it:

http://www.microsoft.com/downloads/en/details.aspx?familyid=9b2da534-3e03-4391-8a4d-074b9f2bc1bf&displaylang=en

Put crashdump.dll and dbghelp.dll into
C:\Program Files\PostgreSQL\8.4\lib

Edit postgresql.conf, uncomment shared_preload_libraries if it's
commented out and add 'crashdump' to it, eg:

shared_preload_libraries = 'crashdump'

Create a folder called "crashdumps" inside the data directory, at the
same level as the "pg_log", "pg_xlog", "base" etc directories. Get
properties on the new "crashdumps" directory and in the security tab add
"Full Control" to the "postgres" user. Save your changes.

Stop and start the postgresql-8.4 service from Start->Run->services.msc.

You should now have a working crash dump handler. To test it, run:

CREATE FUNCTION crashdump_crashme() RETURNS void AS 'crashdump.dll'
LANGUAGE 'C';

then invoke it to crash your database system:

SELECT crashdump_crashme();

If all goes well (heh) you'll lose your connection and the server will
crash and - hopefully - restart. If it doesn't restart, relaunch it
manually using services.msc.

You should now see a file in the "crashdumps" folder.You can email it to
me directly and I'll extract a backtrace.

Alternately, if you want to get the backtrace yourself you will need to
set up your NT_SYMBOL_PATH environment variable to match your install as
per the instructions here:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows#Configuring_the_symbol_path

You will then be able to open it for debugging using Microsoft Visual
Studio 2008 Express Edition (or any paid Visual Studio edition). Once
open, right click on the dump file in the left bar and choose "debug new
instance". Alternately you can use windbg.exe from Debugging Tools for
Windows to analyse the dump as per the instructions here:

http://archives.postgresql.org/message-id/4cab4294.2070...@postnewspapers.com.au

You'll probably want to

DROP FUNCTION crashdump_crashme();

after running your crashme test, though it's harmless if left in place
so long as it's not invoked. Dropping the crashme function doesn't
affect the crashdump handler; it's loaded by shared_preload_libraries
and will remain in place.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

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

Re: [GENERAL] random backend crashes - how to debug ( Is crash dump handler released ? )


On 06/14/2011 10:26 PM, BangarRaju Vadapalli wrote:

Hi Everybody,

We are using PostGRE 8.4 version and experiencing random backend
crashes. We have enabled logging and are able to see some logging
happening in pg_log directory but not of much use. Here are the logs.


Thankyou for collecting the logs and including your version. A little 
more information would be helpful, like the exact version, your OS and 
architecture, etc. See this link for a list of suggested information:


http://wiki.postgresql.org/wiki/Guide_to_reporting_problems


I searched online and found crash dump handler idea has been proposed
and patch for that has already been released if I am not wrong.


It is integrated into PostgreSQL 9.0 as a core part of the server.

There's no reason it can't be compiled for PostgreSQL 8.4, though I 
never tested that. It shouldn't take long so I'll give it a go and get 
back to you.



Could
anyone please detail the steps to install crash dump handler in windows?
Also could you please help me with the ways to debug the crashes
happening as shown above.


The first thing to do is to try to figure out if they're really random, 
or if they're related to a particular query or event. Enable more 
detailed logging in PostgreSQL - at least query logging, possibly also 
additional debug levels - and examine the logs to see if you can find a 
pattern.


--
Craig Ringer

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

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

2011-06-14 Thread Adrian Klaver

On Tuesday, June 14, 2011 3:31:12 pm Stefan Keller wrote:
> Hi Thom
> 
> 2011/6/14 Thom Brown :
> > Shouldn't you be looking for mytable2_pkey?
> 
> Yes; but that was my typo. I tried it several times on two tables.
> My explanation is that the message (saying that an index was
> implicitly created) is simply wrong.

Works here:

test(5432)aklaver=>SELECT version();
 version
  
--
 PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 
4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)


test(5432)aklaver=>  ALTER TABLE mytable2 ADD PRIMARY KEY(id);  

   
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
"mytable2_pkey" for table "mytable2"
 
ALTER TABLE 

   
test(5432)aklaver=>\d+ mytable2
Table "public.mytable2" 

   
 Column |  Type   | Modifiers | Storage  | Description  

   
+-+---+--+- 

   
 id | integer | not null  | plain| 
 name   | text|   | extended | 
Indexes:
"mytable2_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

Note the btree designation.

> 
> Yours, S.

-- 
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

Stefan Keller  writes:
> My explanation is that the message (saying that an index was
> implicitly created) is simply wrong.

The correct explanation is that you're misinterpreting whatever output
you're looking at.  Every unique or pkey constraint has an underlying
index --- the index is the implementation mechanism for the constraint,
so this is assuredly so.  Some tools that show both constraints and
indexes will omit constraint-associated indexes from the listing, since
otherwise they'd be showing duplicate information.

regards, tom lane

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

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

2011-06-14 Thread Stefan Keller

Hi Thom

2011/6/14 Thom Brown :
> Shouldn't you be looking for mytable2_pkey?

Yes; but that was my typo. I tried it several times on two tables.
My explanation is that the message (saying that an index was
implicitly created) is simply wrong.

Yours, S.

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

Re: [GENERAL] trouble building user defined agg function with plpython

Rick Harding  writes:
> CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data)
>  RETURNS integer
>  AS $$
> try:
> curr = curr + vals['weight']
> except UnboundLocalError:
> plpy.notice("UNBOUND")
> curr = 0
> return curr
>  $$ LANGUAGE plpythonu;

This function doesn't work when called manually; it's got nothing to do
with the aggregate context.  You should read the last half of this page:

http://www.postgresql.org/docs/9.0/static/plpython-funcs.html

regards, tom lane

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

Re: [GENERAL] system command in dblink?

2011-06-14 Thread mike stanton

you could try creating a perl function

CREATE OR REPLACE FUNCTION "public"."system" (cmd varchar) RETURNS text AS
$body$
return `$_[0]`;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

then using it in this way

 sql = 'select system( '
|| ' ''/home/iv_fmaster/bin/main_wms 99 '
|| new.iddocsalida || ''' )';

 select dblink_exec('paso_lx',sql,TRUE)
   into ifres;

cheers mike stanton

  - Original Message - 
  From: AI Rumman 
  To: pgsql-general General 
  Sent: Tuesday, June 14, 2011 2:17 AM
  Subject: [GENERAL] system command in dblink?


  Is it possible to execute system commands in dblink connections?

  I need to execute \i /tmp/test.sh in a remote connection from my psql client 
prompt.
  I connected with the remote db using dblink_connect.

  select dblink_connect('conn_1', 'dbname=newdb');

  Any help please.



__ Información de ESET NOD32 Antivirus, versión de la base de firmas de 
virus 6208 (20110614) __

ESET NOD32 Antivirus ha comprobado este mensaje.

http://www.eset.com

[GENERAL] trouble building user defined agg function with plpython

2011-06-14 Thread Rick Harding

I'm trying to test out a user defined aggregation function. The problem I'm
getting is that the state is never passed to the function after the first
call. I'm wondering if this is an issue with having my function defined as
a pypython function or something. 

Each call I get an UnboundLocalError exception. I can try/catch it and set
a default value for the state, but then it's triggered on every invocation
during execution.

A small demo function:

CREATE TYPE group_data AS (
id  integer,
weight  decimal
);

CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data)
 RETURNS integer
 AS $$

try:
curr = curr + vals['weight']
except UnboundLocalError:
plpy.notice("UNBOUND")
curr = 0

return curr

 $$ LANGUAGE plpythonu;

CREATE AGGREGATE mysumagg (group_data)
(
sfunc = mysum,
stype = integer
);


I get the following when testing it:

-- SELECT  
--mysumagg(ROW(res.idx, 1)::group_data)
-- FROM (
-- SELECT r."D6" as idx
-- FROM t_fct_respondent r
-- LIMIT 2
--  ) AS res;

-- NOTICE:  UNBOUND
--  CONTEXT:  PL/Python function "mysum"
-- NOTICE:  UNBOUND
--  CONTEXT:  PL/Python function "mysum"
 mysumagg 
--
0
(1 row)


Thanks for any pointers on what I'm missing from the way to handle the
agggregate definition.

-- 

Rick Harding
@mitechie
http://blog.mitechie.com
http://lococast.net

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

Re: [GENERAL] Functional dependencies

2011-06-14 Thread Darren Duncan


Alpha Beta wrote:

Hi list,  (newbie with databases)

I was looking out in net about how can we determine or find all 
functional dependencies in a  relational database, but didn't find.


So can please anyone here tell me if functional dependecies for each 
table of a relational database can be found explicitly or
we need algorithms for it or any other way? 


Best regards!


Look for pairs of columns/attributes X and Y (or X may be a set of 
columns/attrs) where, for every row/tuple having the same value for X, the 
row/tuple always has the same value for Y.  In this situation, for the data you 
have at least, there would seem to be a relationship where X determines Y and Y 
depends on X.  Use an algorithm that does this looking for pairs. -- Darren Duncan



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

[GENERAL] Functional dependencies

2011-06-14 Thread Alpha Beta

Hi list,  (newbie with databases)

I was looking out in net about how can we determine or find all functional
dependencies in a  relational database, but didn't find.

So can please anyone here tell me if functional dependecies for each table
of a relational database can be found explicitly or
we need algorithms for it or any other way?

Best regards!

Re: [GENERAL] psql reports back wrong number of affected rows.

2011-06-14 Thread David Johnston

> alter table tblissue add constraint
> "tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid)
> REFERENCES tblissue(issueid) ON DELETE CASCADE;
> =
> 
> Then:
> delete from tblissue where issueid=1;
> DELETE 1
> 
> Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my
> testcase).
> That was correct, and as I intended, but why does Postgres answer "DELETE
> 1" instead of DELETE 6?
> 
> Can somebody explain that to me please?
> Thanks for your time.

You only explicitly deleted a single row; all the rest were done via the
CASCADE and thus are not counted in the delete count.

Make sense; If I delete a record and see "DELETE 1000" because 999 FK
records were deleted I would have no way of know if I foo-barred the DELETE
query itself and actually killed 1000 records using the DELETE itself or got
it right and hit the 1 intended record and simply got 999 more deletions
indirectly.

I can see where a more helpful response would be: "DELETE 1 \n NOTICE: 999
FK references were deleted due to Cascade" but the "DELETE 1" MUST show me
explicitly how many records were deleted solely due to my DELETE statement's
FROM and WHERE clauses.

David J.




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

Re: [GENERAL] Per-query local timezone


Steve Crawford wrote:

On 06/14/2011 05:13 AM, Mark Morgan Lloyd wrote:

Karsten Hilbert wrote:

On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote:


Is it possible to incorporate SET TIMEZONE into a query, so that
to_char(...'TZ') etc. is appropriately localised?


You seem to want "AT TIME ZONE".


Thanks for that. How can I do /this/

select to_char(now() at time zone 'GMT0BST', 'TZ');

It appears to return '', while if I used a separate SET TIMEZONE I'd 
expect 'BST'.




The "now()" function returns a timestamp with time zone (aka a point in 
time). When you ask for a timestamp with time zone at a specific time 
zone, you get a timestamp *without* time zone (you provided and 
therefore know the desired time zone and PostgreSQL returned the 
timestamp in that zone).


I'm a bit concerned with your initial statement that "The development 
environment I'm working with uses short-lifetime sessions, and it's 
proving difficult to get a set command and a query associated with the 
same handle.". Do I take this to mean that connections are going through 
some sort of pooler that is allocating connections on as short as a 
per-statement basis so you might end up with a different connection 
between the "set time zone.." statement and the query? If so, you may 
start to find all sorts of other issues.


It's a bit convoluted, but you could get the zone from a subquery and 
select the timestamp converted to that zone along with the zone itself 
from the outer query:


select now() at time zone foo.tz, foo.tz from (select 'est5edt'::text as 
tz) as foo;


Looking back through the mailing list, the issue appears to be the way 
that AT TIME ZONE is parsed into a function which returns a string. I 
think the easiest way round most of this is going to be to use the PGTZ 
shell variable, otherwise I think I can pull the info I need out of 
pg_timezone_names subject to using the correct zone name.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

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

Re: [GENERAL] random backend crashes - how to debug ( Is crash dump handler released ? )

On Tue, Jun 14, 2011 at 9:26 AM, BangarRaju Vadapalli
 wrote:
> Hi Everybody,
>
>
>
>   We are using PostGRE 8.4 version and experiencing random backend
> crashes. We have enabled logging and are able to see some logging happening
> in pg_log directory but not of much use. Here are the logs.
>
>
>
>    2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash
> of another server process
>
> 2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
>
> 2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
>
> 2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of
> another server process
>
> 2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
>
> 2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
>
> 2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of
> another server process
>
> 2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
>
> 2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
>
> 2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of
> another server process
>
> 2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
>
>
>
>   I searched online and found crash dump handler idea has been proposed and
> patch for that has already been released if I am not wrong. Could anyone
> please detail the steps to install crash dump handler in windows? Also could
> you please help me with the ways to debug the crashes happening as shown
> above.

right.  well, are you running any third party code?  C functions?
external modules?  Is it practical to log queries from the client? On
the server?

Which exact version of postgres 8.4 are you running?  How often do you
see the crashes?

merlin

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

[GENERAL] random backend crashes - how to debug ( Is crash dump handler released ? )

2011-06-14 Thread BangarRaju Vadapalli

Hi Everybody,

  We are using PostGRE 8.4 version and experiencing random backend crashes. 
We have enabled logging and are able to see some logging happening in pg_log 
directory but not of much use. Here are the logs.

   2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of 
another server process
2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.
2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.
2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of 
another server process
2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.
2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.
2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of 
another server process
2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.
2011-06-14 18:06:04 IST HINT:  In a moment you should be able to reconnect to 
the database and repeat your command.
2011-06-14 18:06:04 IST WARNING:  terminating connection because of crash of 
another server process
2011-06-14 18:06:04 IST DETAIL:  The postmaster has commanded this server 
process to roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory.

  I searched online and found crash dump handler idea has been proposed and 
patch for that has already been released if I am not wrong. Could anyone please 
detail the steps to install crash dump handler in windows? Also could you 
please help me with the ways to debug the crashes happening as shown above.

Thanks,
Bangar Raju

Re: [GENERAL] system command in dblink?

On Tue, Jun 14, 2011 at 1:17 AM, AI Rumman  wrote:
> Is it possible to execute system commands in dblink connections?
>
> I need to execute \i /tmp/test.sh in a remote connection from my psql client
> prompt.
> I connected with the remote db using dblink_connect.
>
> select dblink_connect('conn_1', 'dbname=newdb');

the '\i' syntax only means anything to psql, so no.  This might help
though: http://plsh.projects.postgresql.org/

merlin

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

Re: [GENERAL] Executing \i of psql command using libpq library

"Edmundo Robles L."  writes:
> How can i get the same behavior of psql -c "\\i './a_lot_of_sentences'" bd 
> user, using the libpq library???

libpq does not contain any such behavior, so you can't.

regards, tom lane

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

[GENERAL] Executing \i of psql command using libpq library

2011-06-14 Thread Edmundo Robles L.

Hi! 

How can i get the same behavior of psql -c "\\i './a_lot_of_sentences'" bd 
user, using the libpq library???

i tried  Pqexec(pgconn,"\\i './a_lot_of_sentences'")   but  didn't work. :(



--
SENSA Control Digital.
Ing. Edmundo Robles Lopez.
Analista Programador.





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

Re: [GENERAL] psql reports back wrong number of affected rows.

2011-06-14 Thread er...@darwine.nl


On 6/14/2011 5:05 PM, Tom Lane wrote:

Erwin Moller  writes:

Then:
delete from tblissue where issueid=1;
DELETE 1
Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my
testcase).
That was correct, and as I intended, but why does Postgres answer
"DELETE 1" instead of DELETE 6?

It's reporting the number of rows deleted from tblissue.


Hi,

But I deleted 6 from tblissue  in my example.
(1 directly, the other 5 by cascade)
Hence my confusion.



  Cascade
effects are not relevant.



Ok, clear.
So the number of deleted rows (DELETE x) are only the ones that directly 
matched the ones in the where-clause.


I suspected that much, but it is better to check. :-)

Thanks for your time, Tom

Regards,
Erwin Moller



regards, tom lane



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

Re: [GENERAL] Per-query local timezone


Tom Lane wrote:

John R Pierce  writes:

On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:
The development environment I'm working with uses short-lifetime 
sessions, and it's proving difficult to get a set command and a query 
associated with the same handle. 



this environment doesn't support even a transaction?


Sounds kinda broken :-( ... but maybe Mark could wrap the operations
he needs into custom functions.


Is always a possibility. The problem is that particular component I'm 
using conflates the open and issue-query operations and has an implicit 
transaction, the developers are aware that this has undesirable 
implications.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

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

Re: [GENERAL] Per-query local timezone

2011-06-14 Thread Steve Crawford


On 06/14/2011 05:13 AM, Mark Morgan Lloyd wrote:

Karsten Hilbert wrote:

On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote:


Is it possible to incorporate SET TIMEZONE into a query, so that
to_char(...'TZ') etc. is appropriately localised?


You seem to want "AT TIME ZONE".


Thanks for that. How can I do /this/

select to_char(now() at time zone 'GMT0BST', 'TZ');

It appears to return '', while if I used a separate SET TIMEZONE I'd 
expect 'BST'.




The "now()" function returns a timestamp with time zone (aka a point in 
time). When you ask for a timestamp with time zone at a specific time 
zone, you get a timestamp *without* time zone (you provided and 
therefore know the desired time zone and PostgreSQL returned the 
timestamp in that zone).


I'm a bit concerned with your initial statement that "The development 
environment I'm working with uses short-lifetime sessions, and it's 
proving difficult to get a set command and a query associated with the 
same handle.". Do I take this to mean that connections are going through 
some sort of pooler that is allocating connections on as short as a 
per-statement basis so you might end up with a different connection 
between the "set time zone.." statement and the query? If so, you may 
start to find all sorts of other issues.


It's a bit convoluted, but you could get the zone from a subquery and 
select the timestamp converted to that zone along with the zone itself 
from the outer query:


select now() at time zone foo.tz, foo.tz from (select 'est5edt'::text as 
tz) as foo;


Cheers,
Steve


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

Re: [GENERAL] psql reports back wrong number of affected rows.

Erwin Moller  writes:
> Then:
> delete from tblissue where issueid=1;
> DELETE 1

> Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my 
> testcase).
> That was correct, and as I intended, but why does Postgres answer 
> "DELETE 1" instead of DELETE 6?

It's reporting the number of rows deleted from tblissue.  Cascade
effects are not relevant.

regards, tom lane

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

Re: [GENERAL] thoughts on interactive query

On Tue, Jun 14, 2011 at 2:39 AM, Sim Zacks  wrote:
> I am playing around with making interactive queries and was wondering if
> anyone had any comments.
>
> If your comment is "That is a stupid idea", please try to qualify that with
> something constructive as well.
>
>
> The idea is that sometimes during a process, user input is required. The way
> we have been doing this is to  return an error code and then the GUI asks
> the user the question and restarts the query with the answer passed as a
> parameter.
>
>
> The problem with this is that it is sometimes a long, complicated
> transaction and ending it in the middle just to ask the user "yes or no" and
> then running the entire transaction again seems awfully inefficient.

It's widely considered bad mojo to bock a transaction while waiting on
input.  I completely agree with this, and all else aside I think that
even if your idea could be made to work it encourages bad behaviors.
Gather all your answers before running your query (and if that can't
be done, your question is too complicated).

merlin

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

Re: [GENERAL] duplicate key violate error

On Tue, Jun 14, 2011 at 3:30 AM, AI Rumman  wrote:
> Hi,
>
> I got duplicate key violate error in the db log for the following query:
> INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS (
> SELECT 1 FROM tab1         WHERE id='1611576' AND id2='1187865'    )    )
>
> The error occured during production time.
> But when I manually executed the query, it inserted one row with success and
> next time it inserted 0 rows.

Unfortunately the operation above is not atomic.  This is a classic
concurrency problem that everyone has to deal with -- there is no way
at present to rely on a simple row level lock to prevent concurrent
inserts to the same key.  You have a few of ways to deal with this:

*) retry the statement (personally not a big fan of this method)
*) lock the table (lousy concurrency)
*) advisory lock might work, if you must have concurrency and your key
is an integer. be careful, and do not overuse the technique.

merlin

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

[GENERAL] psql reports back wrong number of affected rows.

2011-06-14 Thread Erwin Moller


Hi,

I was surprised by the following behavior of Postgres (8.1).
Consider the following table and constraint:

=
CREATE TABLE tblissue(
 issueid SERIAL PRIMARY KEY,
 title TEXT,
 comment TEXT,
 createtimestamp TIMESTAMP DEFAULT (current_timestamp),
 parentissueid INTEGER,
 caseclosed CHAR(1)
);

alter table tblissue add constraint 
"tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid) 
REFERENCES tblissue(issueid) ON DELETE CASCADE;

=

So the parentissueid references the same table's PK, but can NULL too.
All fine so far.

Next I insert a few rows that use not null values for parentissueid, so 
the foreign key constraint is in effect.

Suppose I created a few rows that have 1 as value for parentissueid.

Then:
delete from tblissue where issueid=1;
DELETE 1

Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my 
testcase).
That was correct, and as I intended, but why does Postgres answer 
"DELETE 1" instead of DELETE 6?


Can somebody explain that to me please?
Thanks for your time.

Regards,
Erwin Moller

PS: I found a few possible relevant postings. One of them (by Tom Lane) 
pointed to here:

http://www.postgresql.org/docs/8.0/static/rules-status.html
but I am still unsure if it is relevant.


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

Re: [GENERAL] Per-query local timezone

John R Pierce  writes:
> On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:
>> The development environment I'm working with uses short-lifetime 
>> sessions, and it's proving difficult to get a set command and a query 
>> associated with the same handle. 

> this environment doesn't support even a transaction?

Sounds kinda broken :-( ... but maybe Mark could wrap the operations
he needs into custom functions.

regards, tom lane

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

Re: [GENERAL] how to install plpython?

2011-06-14 Thread Adrian Klaver

On Tuesday, June 14, 2011 2:17:32 am AI Rumman wrote:
> Okay. Thanks for the guidance.
> Could you please tell where I can get the postgresql-python lib files?
> 

The short answer is it depends,  hence Craigs request for information on how it 
was installed. The various packages handle it in different ways and if you are 
configuring from source you have to specify that the files are built. With out 
more information your question can not be answered.

> > Craig Ringer

-- 
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] configure error... please help 9.0.4

2011-06-14 Thread Adrian Klaver

On Tuesday, June 14, 2011 5:51:59 am akp geek wrote:
> Thanks all for the responses.
> 
> 
> 
> $./configure --prefix=/opt/postgres/9.0.4 --with-readline --with-perl
> --with-libxml --with-openssl --with-ossp-uuid
> LDFLAGS=-L/opt/postgres/gis/lib
> --with-includes=/opt/postgres/software/include/readline/
> 
> *I bypassed the uuid error.  Now getting this  .. I have installed
> readline. I have this under /opt/postgres/software/lib, include ,share *

Did you install readline-dev also?

> 
> *checking readline/readline.h usability... no*
> *checking readline/readline.h presence... no*
> *checking for readline/readline.h... no*
> *checking readline.h usability... no*
> *checking readline.h presence... no*
> *checking for readline.h... no*
> *configure: error: readline header not found*
> *If you have readline already installed, see config.log for details on the*
> *failure.  It is possible the compiler isn't looking in the proper
> directory.*
> *Use --without-readline to disable readline support.*
> 
>

-- 
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] Tweaking bytea / large object block sizes?

On Tue, Jun 14, 2011 at 6:48 AM, Hanno Schlichting  wrote:
> On Mon, Jun 13, 2011 at 3:27 AM, Merlin Moncure  wrote:
>> I would not even consider tweaking the internal block sizes until
>> you've determined there is a problem you expect you might solve by
>> doing so.
>
> It's not a problem as such, but managing data chunks of 2000 bytes +
> the hundreds of rows per object in the large_object table for 10mb
> objects seems like a lot of wasted overhead, especially if the
> underlying filesystem manages 32kb or 64kb blocks. My impression of
> those values was that they are a bit antiquated or are tuned for
> storing small variable character objects, but not anything I'd call
> "binary large objects" these days.

That very well may be the case, and 10mb is approaching the upper
limit of what is sane to store inside the database.  Still, if you're
going through the trouble to adjust the setting and recompile, I'd
definitely benchmark the changes and post your findings here.  Point
being, all else being equal, it's always better to run with stock
postgres if you can manage it.

merlin

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

Re: [GENERAL] Proposition fetch cursors

2011-06-14 Thread Radosław Smogura


On Tue, 14 Jun 2011 20:49:48 +0800, Craig Ringer wrote:

On 06/14/2011 04:30 PM, Radosław Smogura wrote:

Hello,

I have proposition about one missing feature for cursors. Actually 
there
is no support for fetching some data from cursor without moving it 
(in
context of external applications). This could be nice if driver 
could

fetch e.g. 100 rows, buffer it internally and then move cursor at
desired position in order to update data. I think this is known 
approach

in databases.


Interesting.

Do you want this only for scrollable cursors? Or do you want/need to
be able to "peek" ahead into the results of a non-scrollable cursor
too?

What's your use case?

--
Craig Ringer


Actually for "for update" cursors (so for all non-scrollable). With 
scrollable I can move backward and forward simulating fetches, but for 
update it's small pain, as in order to update n-th row (I doesn't know 
at begin that this is n-th row) I should ask n-times to get this row, 
moving forward one-by-one - adding network "overhead". With fetches I 
can ask to fetch 100 rows from current position, process it on my side, 
and then decide where to move cursor (eventually to update data).


Regards,
Radek

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

Re: [GENERAL] determine client os


On 06/14/2011 05:54 PM, Sim Zacks wrote:


I have a system settings table which defines mount points.
I have a directories table which defines the relative path (from the
mount point) for each type of document.


OK, so your clients already have all the information they need to 
assemble the paths themselves. You don't need the views. Hand clients 
the relative paths, and they can use the information from the system 
settings table to assemble the full path trivially.


Advantages of doing it this way include:

- The server doesn't need to know the client OS

- The client can read the mount point or drive letter setting
  from the database *once* on startup and cache it. You won't send
  it each time with each file path, saving network I/O. It knows its
  own OS, so it knows which settings to read.

- Queries are cheaper and simpler because there's no need for string
  assembly and views that use system settings tables.


In the table where the file reference is stored it shows
#doctype#filename.ext.
I have a view which puts together the full windows and linux paths for
each document type. In the query that retrieves the file reference it
does a replace for the specified doctype.


So you store different document types in different mount points / drive 
letters? Is that why you're doing this substitution?


If so: again, the client can do this substitution. It knows its own OS 
and can read the settings table once.


(If you expect settings to change a lot you can always have clients 
LISTEN for NOTIFY events on change).



I don't see any practical way of being so flexible that the user can
change drive letters and/or mount points and still expect file paths to
work.


If you want all-server-side configuration, then yep, you're pretty much 
stuck with fixed paths.


Many packages I've used have a client-side config file that can be used 
to control path and database access settings, so clients with special 
needs can change paths etc. If all clients are configured the same, it's 
typical to put the config file on a shared volume via a UNC path. The 
app is invoked with a shortcut / wrapper script / environment variable / 
registry setting that specifies the config file path.


You presumably have some minimal client configuration mechanism already 
so the client can discover which server to talk to. Same principle.


--
Craig Ringer

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

Re: [GENERAL] configure error... please help 9.0.4

2011-06-14 Thread akp geek

Thanks all for the responses.



$./configure --prefix=/opt/postgres/9.0.4 --with-readline --with-perl
--with-libxml --with-openssl --with-ossp-uuid
LDFLAGS=-L/opt/postgres/gis/lib
--with-includes=/opt/postgres/software/include/readline/

*I bypassed the uuid error.  Now getting this  .. I have installed readline.
I have this under /opt/postgres/software/lib, include ,share *

*checking readline/readline.h usability... no*
*checking readline/readline.h presence... no*
*checking for readline/readline.h... no*
*checking readline.h usability... no*
*checking readline.h presence... no*
*checking for readline.h... no*
*configure: error: readline header not found*
*If you have readline already installed, see config.log for details on the*
*failure.  It is possible the compiler isn't looking in the proper
directory.*
*Use --without-readline to disable readline support.*


Following is my .profile  on solaris

I have

1. uuid  , uuid-config  in the /opt/postgres/gis/bin
2. /opt/postgres/gis/lib
 -rw-r--r--   1 postgres dba50276 Jun 14 12:01 libuuid.a
-rw-r--r--   1 postgres dba  932 Jun 14 12:01 libuuid.la
lrwxrwxrwx   1 postgres dba   18 Jun 14 12:01 libuuid.so ->
libuuid.so.16.0.22
lrwxrwxrwx   1 postgres dba   18 Jun 14 12:01 libuuid.so.16 ->
libuuid.so.16.0.22
-rwxr-xr-x   1 postgres dba49376 Jun 14 12:01 libuuid.so.16.0.22


LD_LIBRARY_PATH=/opt/postgres/9.0.4/lib:/opt/postgres/gis/lib:/opt/postgres/perl/lib:/opt/postgres/perl/lib/perl5/5.12.3/sun4-solaris/CORE:/opt/postgres/software/lib:/opt/postgres/software/libxml2/lib:/opt/postgres/software/flex/lib:/usr/local/lib:/lib:/platform/SUNW,Sun-Fire-V210/lib:/usr/ucblib:/opt/SUNWspro/SC4.0/lib:/lib:/usr/sfw/lib:/usr/lib:/opt/
postgres/libiconv-1.13.1/lib/.libs

export LD_LIBRARY_PATH


PATH=/opt/postgres/9.0.4/bin:/opt/postgres/perl/bin:/opt/postgres/gis/bin:/bin:/sbin:/usr/5bin:/usr/sbin:/usr/ucb:/lib:/usr/ccs/bin:/opt/SUNWspro/SC4.0/bin:/usr/4lib:/usr/ucblib:/usr/ccs/lib:/usr/etc:/opt/postgres/software/libxml2/bin:/opt/postgres/gis/include:/opt/postgres/gis/share:/opt/postgres/software/include/readline:/opt/postgres/software/flex/bin:/opt/postgres/libxml2/include/libxml2/libxml:/usr/sfw/bin:/usr/local/bin


On Mon, Jun 13, 2011 at 7:32 PM, Craig Ringer
wrote:

> On 06/14/2011 05:15 AM, akp geek wrote:
>
>> Dear all -
>>
>>While setting up streaming I ran into issue with libpq.
>> To resolve it I need to recompile the source. I am issuing the following
>> command . I tried this before with 9.0.2 it worked. Now when issued the
>> same command I was getting the errors.
>>
>
>  configure: error: library 'ossp-uuid' or 'uuid' is required for OSSP-UUID
>>
>
> Examine config.log and see why the ossp-uuid testcase failed.
>
> Do you have ossp-uuid installed? Is it on your LIBRARY_PATH and
> INCLUDE_PATH ?
>
> --
> Craig Ringer
>

Re: [GENERAL] how to install plpython?


On 06/14/2011 05:17 PM, AI Rumman wrote:

Okay. Thanks for the guidance.
Could you please tell where I can get the postgresql-python lib files?


Did you *read* the guidance in question?

You didn't even bother to answer the one simple question I asked. How do 
you expect help if you don't supply enough information to allow anyone 
to answer your question?


You didn't even mention what operating system you're on!

Please try *reading*
  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
then try again.

[Yes, this email is a bit grumpy]

--
Craig Ringer

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

Re: [GENERAL] Proposition fetch cursors


On 06/14/2011 04:30 PM, Radosław Smogura wrote:

Hello,

I have proposition about one missing feature for cursors. Actually there
is no support for fetching some data from cursor without moving it (in
context of external applications). This could be nice if driver could
fetch e.g. 100 rows, buffer it internally and then move cursor at
desired position in order to update data. I think this is known approach
in databases.


Interesting.

Do you want this only for scrollable cursors? Or do you want/need to be 
able to "peek" ahead into the results of a non-scrollable cursor too?


What's your use case?

--
Craig Ringer

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

Re: [GENERAL] duplicate key violate error

2011-06-14 Thread Albe Laurenz

AI Rumman wrote:
> I got duplicate key violate error in the db log for the following
query:
> INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS (
SELECT 1 FROM tab1
> WHERE id='1611576' AND id2='1187865'))
>
> The error occured during production time.
> But when I manually executed the query, it inserted one row with
success and next time it inserted 0
> rows.
>
> \d tab1
> id int
> id2 int
> primary key (id,id2)
>
> So, any idea why the error occurred at production time.

Concurrency?

Session 1:

CREATE TABLE tab1 (
  id integer NOT NULL,
  id2 integer NOT NULL,
  PRIMARY KEY (id, id2)
);

START TRANSACTION;

INSERT INTO tab1
  (SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
  );

Session 2:

START TRANSACTION;

INSERT INTO tab1
  (SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
  );

Session 1:

COMMIT;

Session 2:

ERROR:  duplicate key value violates unique constraint "tab1_pkey"


Yours,
Laurenz Albe

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

Re: [GENERAL] Tweaking bytea / large object block sizes?

2011-06-14 Thread Hanno Schlichting

On Mon, Jun 13, 2011 at 3:27 AM, Merlin Moncure  wrote:
> I would not even consider tweaking the internal block sizes until
> you've determined there is a problem you expect you might solve by
> doing so.

It's not a problem as such, but managing data chunks of 2000 bytes +
the hundreds of rows per object in the large_object table for 10mb
objects seems like a lot of wasted overhead, especially if the
underlying filesystem manages 32kb or 64kb blocks. My impression of
those values was that they are a bit antiquated or are tuned for
storing small variable character objects, but not anything I'd call
"binary large objects" these days.

> The single most important factor affecting blob performance
> in postgres is how you send and receive the data -- you absolutely
> want to use the binary protocol mode (especially for postgres versions
> that don't support hex mode).  The next thing to look at is using
> bytea/large object -- large objects are a bit faster and have a higher
> theoretical limit on size but byea is a standard type and this offers
> a lot of conveniences -- I'd say stick with bytea unless you've
> determined there is a reason not to.  That said, if you are not
> writing C some client side drivers might only allow binary
> transmission through the lo interface so that's something to think
> about.

Thanks, I got as much from the docs and the blogosphere.

We are going to use the large object interface. That seems to be the
least we can do - especially to avoid some encoding overhead. We are
storing bytes after all and not ascii characters so there should be no
encoding at all. We aren't using SQL as the query interface as such
but the Python bindings (http://www.initd.org/psycopg/) so we can take
full advantage of the underlying large object API's and do pretty
direct lo_import / lo_export calls. We are targeting at least Postgres
9.0, potentially going for 9.1 soon after it hits final.

Once we get further in the project, we'll of course do some intensive
benchmarking for the various options with our specific data and
configuration. I'm just trying to understand what bits and bytes are
actually stored and transferred behind all those API's.

Hanno

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

Re: [GENERAL] PGP encrypt/decrypt - Prereqistes

2011-06-14 Thread Vick Khera

On Mon, Jun 13, 2011 at 11:51 PM, Vikram A  wrote:
> I would like to crypt certain sensitive information in my applications such
> as Student register number, their marks, results etc. For this reason i done
> a study for doing encryption. Where I found that this PGP will help
> the encryption/decryption.
> Any other methods/techniques are there for encryption? I
> prefer encryption with "key"; key also should not be known to Developers.
> Please suggest any such methods.
> Thank you in advance.

What exactly is the threat against which you are defending? Without a
clearly defined goal, adding encryption for encryption's sake is just
pointless.  The solution you choose cannot be determined without
defining the threat first.

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

Re: [GENERAL] Per-query local timezone


Karsten Hilbert wrote:

On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote:


Is it possible to incorporate SET TIMEZONE into a query, so that
to_char(...'TZ') etc. is appropriately localised?


You seem to want "AT TIME ZONE".


Thanks for that. How can I do /this/

select to_char(now() at time zone 'GMT0BST', 'TZ');

It appears to return '', while if I used a separate SET TIMEZONE I'd 
expect 'BST'.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

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

Re: [GENERAL] Per-query local timezone

2011-06-14 Thread John R Pierce


On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote:


The development environment I'm working with uses short-lifetime 
sessions, and it's proving difficult to get a set command and a query 
associated with the same handle. 


this environment doesn't support even a transaction?



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Per-query local timezone

2011-06-14 Thread Karsten Hilbert

On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote:

> Is it possible to incorporate SET TIMEZONE into a query, so that
> to_char(...'TZ') etc. is appropriately localised?

You seem to want "AT TIME ZONE".

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

[GENERAL] Per-query local timezone

Is it possible to incorporate SET TIMEZONE into a query, so that 
to_char(...'TZ') etc. is appropriately localised?


The development environment I'm working with uses short-lifetime 
sessions, and it's proving difficult to get a set command and a query 
associated with the same handle.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

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

Re: [GENERAL] determine client os

2011-06-14 Thread Sim Zacks


On 06/14/2011 10:29 AM, Craig Ringer wrote:


On 14/06/11 14:35, Sim Zacks wrote:


It is much simpler then that. My data includes file references.
One table has the filename with a path placeholder and another table
contains the windows and linux versions of the full path. This is for an
intranet and we _always_ have the same drive letters (windows) and mount
paths (linux) for every client. When the client requests data that
includes a file reference, I want to send the relevant path.

Just send the client the relative path component under the mount point /
drive letter / whatever.

If your storage is mapped to "/net/myapp/files" on your Linux/unix
boxes, and "Z:\" on your Windows boxes, both these paths:

/mnt/myapp/files/project1/file.bin
Z:/project1/file.bin

... share the same relative path component "project1/file.bin". Just
send that to the client and let it concatenate the fixed prefix path to
the storage root. That way you aren't hard-coding drive letters and
mount points, and you only have to store paths once in the database. The
client knows what OS it is and it knows where the storage root is
mounted/mapped; the database server doesn't need to know.

Yes, I know I showed the windows path with forward slash separators. As
far as I can tell, these days doesn't care about this - it's quite happy
with forward slash separators. If you find it to be a problem you can
always have your Windows clients flip the separators.

Trust me, your users and anyone else working on the codebase later will
hate you if you try to do it the way you're proposing. I speak from
horrid experience working with a (closed-source, legacy) product that
did just what you want to do. It's a bad idea. If nothing else, what
will you do when you have to add a phone client that has to access the
files over HTTP or WebDAV requests? It's easy if you store just the
relative path, but a nightmare if your DB must store full paths. Not to
mention all the wasted storage space your proposed method requires.

--
Craig Ringer
My structure is rather flexible and not bloated and we obviously don't 
store the entire file path per record in the database.

I have a system settings table which defines mount points.
I have a directories table which defines the relative path (from the 
mount point) for each type of document.
In the table where the file reference is stored it shows 
#doctype#filename.ext.
I have a view which puts together the full windows and linux paths for 
each document type. In the query that retrieves the file reference it 
does a replace for the specified doctype.


If in the future we decide to add a web functionality, it can easily 
work with the same setup, using the protocol and base as the "mount 
point" and the path will still work correctly. Then the client just has 
to know how to access the link type.


If I have a problem of users changing drive letters and/or mount points 
then I'll have a more serious issue. For the plannable future, we have a 
set of network standards, such as drive letter/mount path  for corporate 
file server. We have had instances where a user changed the network 
point complained that something didn't work. We are very inflexible 
regarding that point. The answer was that if he changes the network 
paths then the system will not work.


I don't see any practical way of being so flexible that the user can 
change drive letters and/or mount points and still expect file paths to 
work. UNCs are nice, but only work for Windows and in my experience 
(from a few years back) are much slower then mapped drives. Then we 
would need to be configured per machine instead of on the server and 
then if the user changes something on the machine again it will have to 
be reconfigured. Of all the options, I like the "This is the drive 
letter and mount paths that must be used if you want this application to 
work."



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

Re: [GENERAL] how to install plpython?

2011-06-14 Thread Sim Zacks

What OS/distribution are you using?
If you compiled postgresql your self did you include python support?

Sim

On 06/14/2011 12:17 PM, AI Rumman wrote:

Okay. Thanks for the guidance.
Could you please tell where I can get the postgresql-python lib files?

On Tue, Jun 14, 2011 at 2:01 PM, Craig Ringer 
mailto:cr...@postnewspapers.com.au>> wrote:

On 14/06/11 15:33, AI Rumman wrote:
> Hi guys,
>
> I need to install plpython language in my db.

How did you install Pg?

See: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Craig Ringer

Re: [GENERAL] how to install plpython?

2011-06-14 Thread AI Rumman

Okay. Thanks for the guidance.
Could you please tell where I can get the postgresql-python lib files?

On Tue, Jun 14, 2011 at 2:01 PM, Craig Ringer
wrote:

> On 14/06/11 15:33, AI Rumman wrote:
> > Hi guys,
> >
> > I need to install plpython language in my db.
>
> How did you install Pg?
>
> See: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> --
> Craig Ringer
>

Re: [GENERAL] HOW TO install or use pageinspect

2011-06-14 Thread F. BROUARD / SQLpro


Hi Gleu,

OK I run :

CREATE EXTENSION pageinspect

And it works.

Thanks

Le 14/06/2011 10:32, Guillaume Lelarge a écrit :


Hi,

On Tue, 2011-06-14 at 10:06 +0200, F. BROUARD / SQLpro wrote:

[...]
I have a 9.1 PG Server on Windows XP
The pageinspect.dll is in C:\Program Files\PostgreSQL\9.1\lib
I am postgresql user in the database (names DB_TEST).
But I cannot use the functions like
SELECT * FROM heap_page_items(get_raw_page('aTable', 0))
They does not appear in the postgreSQL catalog in the function list...
What am I missing ?



Did you install the extension first? with "CREATE EXTENSION
pageinstall;" as you are on 9.1.





--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.com *


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

Re: [GENERAL] HOW TO install or use pageinspect

2011-06-14 Thread Guillaume Lelarge

Hi,

On Tue, 2011-06-14 at 10:06 +0200, F. BROUARD / SQLpro wrote:
> [...]
> I have a 9.1 PG Server on Windows XP
> The pageinspect.dll is in C:\Program Files\PostgreSQL\9.1\lib
> I am postgresql user in the database (names DB_TEST).
> But I cannot use the functions like
> SELECT * FROM heap_page_items(get_raw_page('aTable', 0))
> They does not appear in the postgreSQL catalog in the function list...
> What am I missing ?
> 

Did you install the extension first? with "CREATE EXTENSION
pageinstall;" as you are on 9.1.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


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

[GENERAL] duplicate key violate error

2011-06-14 Thread AI Rumman

Hi,

I got duplicate key violate error in the db log for the following query:
INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS (
SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865'))

The error occured during production time.
But when I manually executed the query, it inserted one row with success and
next time it inserted 0 rows.

\d tab1
id int
id2 int
primary key (id,id2)

So, any idea why the error occurred at production time.

[GENERAL] Proposition fetch cursors

2011-06-14 Thread Radosław Smogura


Hello,

I have proposition about one missing feature for cursors. Actually 
there is no support for fetching some data from cursor without moving it 
(in context of external applications). This could be nice if driver 
could fetch e.g. 100 rows, buffer it internally and then move cursor at 
desired position in order to update data. I think this is known approach 
in databases.


Regards,
Radek

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

[GENERAL] HOW TO install or use pageinspect

2011-06-14 Thread F. BROUARD / SQLpro


Hi there,

I have a 9.1 PG Server on Windows XP
The pageinspect.dll is in C:\Program Files\PostgreSQL\9.1\lib
I am postgresql user in the database (names DB_TEST).
But I cannot use the functions like
SELECT * FROM heap_page_items(get_raw_page('aTable', 0))
They does not appear in the postgreSQL catalog in the function list...
What am I missing ?

Thanks

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.com *


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

Re: [GENERAL] how to install plpython?

On 14/06/11 15:33, AI Rumman wrote:
> Hi guys,
> 
> I need to install plpython language in my db.

How did you install Pg?

See: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Craig Ringer

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

Re: [GENERAL] determine client os

2011-06-14 Thread Karsten Hilbert

On Tue, Jun 14, 2011 at 09:35:52AM +0300, Sim Zacks wrote:

> >Sim Zacks  writes:
> >>All the suggestions given are for the server OS :-(
> >>My purpose is to be able to return a correct file path to the client
> >>without it specifying the OS.
> >File path?  Seems to me that even if you knew the client OS, that'd
> >provide next to no information about the installation pathnames of the
> >client software.  Maybe you need to be a bit clearer about what you're
> >trying to accomplish.
> >
> > regards, tom lane
> It is much simpler then that. My data includes file references.
> One table has the filename with a path placeholder and another table
> contains the windows and linux versions of the full path. This is for
> an intranet and we _always_ have the same drive letters (windows) and
> mount paths (linux) for every client. When the client requests data
> that includes a file reference, I want to send the relevant path.

Send both and have the client select the one it needs.

If you don't want to need to know on the client side just
try both. One will work. If both don't there's a problem
somewhere.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

2011-06-14 Thread Thom Brown

On 14 June 2011 06:39, Stefan Keller  wrote:
> Hi
>
> I observed some strange behaviour when adding a primary key with ALTER TABLE:
>
> Given CREATE TABLE mytable1 (id serial, name text);
> I filled it with data then did a
>  CREATE TABLE mytable2 AS SELECT * FROM mytable1;
>  ALTER TABLE mytable2 ADD PRIMARY KEY(id);
>
> The last command reports - as usual - that implicitly an index on id
> ("mytable_pkey") was created - but it did not! It adds only a primary
> key constraint on id. Can anybody explain this?
>
> Yours, S.
>
> P.S. I have installed "PostgreSQL 9.1alpha1, compiled by Visual C++
> build 1500, 32-bit".

Shouldn't you be looking for mytable2_pkey?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[GENERAL] thoughts on interactive query

2011-06-14 Thread Sim Zacks

I am playing around with making interactive queries and was wondering if 
anyone had any comments.


If your comment is "That is a stupid idea", please try to qualify that 
with something constructive as well.



The idea is that sometimes during a process, user input is required. The 
way we have been doing this is to  return an error code and then the GUI 
asks the user the question and restarts the query with the answer passed 
as a parameter.



The problem with this is that it is sometimes a long, complicated 
transaction and ending it in the middle just to ask the user "yes or no" 
and then running the entire transaction again seems awfully inefficient.



What I have tried successfully is the following plpython function:


create or replace function python_prompt(v_question text) returns bool as
$$
import socket
ipaddr=plpy.execute("select inet_client_addr()")[0]["inet_client_addr"]
HOST, PORT = str(ipaddr), 
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(30)
sock.connect((HOST, PORT))
sock.send(v_question + "\n")
ans=sock.recv(1024) # Processing waits here for either an answer or the 
timeout to expire

sock.close()
if ans=="yes":
return 1
else:
return 0
$$ language 'plpythonu';


I added a socket server to my application, so each client listens on the 
same port.


If the query requires user input in the middle of the function, it can 
then ask and wait for the answer. If it doesn't receive an answer within 
the timeout period (30 seconds in this case) it dies with a timeout error.



A use case for this is:


create or replace function myprocess() returns int as

$$

begin

--long process

if not python_prompt('The final computed numbers fall out of normal 
range. To continue with this process you must manually override. Do you 
want to override?') then


raise exception 'Numbers out of normal range';

end if;

return 0;

end;

$$ language 'plpgsql';


During the wait time, I didn't see any CPU or memory usage , so setting 
a high timeout will use a connection but won't grind the server to a halt.



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

[GENERAL] how to install plpython?

2011-06-14 Thread AI Rumman

Hi guys,

I need to install plpython language in my db.

testdb=# create language plpythonu;
ERROR:  could not access file "$libdir/plpython": No such file or directory

-bash-3.2$ pwd
/usr/pgsql-9.0/lib
-bash-3.2$ ls pl*
plpgsql.so

testdb=# select * from pg_pltemplate;
  tmplname  | tmpltrusted | tmpldbacreate |  tmplhandler   |
tmplinline|   tmplvalidator   |tmpllibrary| tmplacl
+-+---++--+---+---+-
 plpgsql| t   | t | plpgsql_call_handler   |
plpgsql_inline_handler   | plpgsql_validator | $libdir/plpgsql   |
 pltcl  | t   | t | pltcl_call_handler
|  |   | $libdir/pltcl |
 pltclu | f   | f | pltclu_call_handler
|  |   | $libdir/pltcl |
 plperl | t   | t | plperl_call_handler|
plperl_inline_handler| plperl_validator  | $libdir/plperl|
 plperlu| f   | f | plperl_call_handler|
plperl_inline_handler| plperl_validator  | $libdir/plperl|
 plpythonu  | f   | f | plpython_call_handler  |
plpython_inline_handler  |   | $libdir/plpython  |
 plpython2u | f   | f | plpython_call_handler  |
plpython_inline_handler  |   | $libdir/plpython2 |
 plpython3u | f   | f | plpython3_call_handler |
plpython3_inline_handler |   | $libdir/plpython3 |
(8 rows)


Where may I get plpython.so?

Re: [GENERAL] determine client os

On 14/06/11 14:35, Sim Zacks wrote:

> It is much simpler then that. My data includes file references.
> One table has the filename with a path placeholder and another table
> contains the windows and linux versions of the full path. This is for an
> intranet and we _always_ have the same drive letters (windows) and mount
> paths (linux) for every client. When the client requests data that
> includes a file reference, I want to send the relevant path.

Just send the client the relative path component under the mount point /
drive letter / whatever.

If your storage is mapped to "/net/myapp/files" on your Linux/unix
boxes, and "Z:\" on your Windows boxes, both these paths:

/mnt/myapp/files/project1/file.bin
Z:/project1/file.bin

... share the same relative path component "project1/file.bin". Just
send that to the client and let it concatenate the fixed prefix path to
the storage root. That way you aren't hard-coding drive letters and
mount points, and you only have to store paths once in the database. The
client knows what OS it is and it knows where the storage root is
mounted/mapped; the database server doesn't need to know.

Yes, I know I showed the windows path with forward slash separators. As
far as I can tell, these days doesn't care about this - it's quite happy
with forward slash separators. If you find it to be a problem you can
always have your Windows clients flip the separators.

Trust me, your users and anyone else working on the codebase later will
hate you if you try to do it the way you're proposing. I speak from
horrid experience working with a (closed-source, legacy) product that
did just what you want to do. It's a bad idea. If nothing else, what
will you do when you have to add a phone client that has to access the
files over HTTP or WebDAV requests? It's easy if you store just the
relative path, but a nightmare if your DB must store full paths. Not to
mention all the wasted storage space your proposed method requires.

--
Craig Ringer

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

Re: [GENERAL] PGP encrypt/decrypt - Prereqistes