[GENERAL] Delete trigger

2015-09-18 Thread Leif Jensen
   Hi,

   I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:

CREATE TABLE devicegroup (
groupid integer NOT NULL,
ctrlid integer NOT NULL,
userid integer NOT NULL
);
ALTER TABLE ONLY devicegroup
ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);

   I want to make sure that records are only deleted when all 3 fields are 
specified, so I tried make a trigger:

CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
  EXECUTE PROCEDURE deleteUserDev();

which could check for NOT NULL on the 3 fields before actual doing the delete. 
Unfortunately this is not possible to do FOR EACH STATEMENT, and FOR EACH ROW 
would not give me the chance to check for NOT NULL.

   Any ideas ?

 Leif


-- 
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] Delete trigger

2015-09-18 Thread Leif Jensen
   Hello Laurenz,

   Thank you for you suggestion. I really want to aviod that someone 
'accidentally' deletes too much by typing (programming) a not full qualified 
DELETE ... statement. In your case one would have to always use the delete 
function, but no restrictions on using the DELETE statement.

 Leif


- Original Message -
> Leif Jensen wrote:
> >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete
> >anything. I only want to
> > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND
> > userid=z". I don't wanna let
> > anyone delete more than 1 row at a time.
> 
> I can't think of a way to do that with a trigger.
> 
> I'd write a
>   FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid
>   integer)
> RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
> that enables the user to delete a row and checks that all arguments
> are NOT NULL.  The user doesn't get privileges to DELETE from the table
> directly.
> 
> 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] Delete trigger

2015-09-18 Thread Leif Jensen
   Hi Charles,

   If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete 
anything. I only want to delete if I do "DELETE FROM devicegroup WHERE 
groupid=x AND ctrlid=y AND userid=z". I don't wanna let anyone delete more than 
1 row at a time.

 Leif


- Original Message -
> Hello
> 
> Not sure I get it right, but all three fields are not nullable. So they will
> always have a value, which is what I understand of "are specified".
> What do you need the trigger for in that case?
> 
> Bye
> Charles
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen
> > Sent: Freitag, 18. September 2015 10:23
> > To: pgsql-general <pgsql-general@postgresql.org>
> > Subject: [GENERAL] Delete trigger
> > 
> >Hi,
> > 
> >I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
> > 
> > CREATE TABLE devicegroup (
> > groupid integer NOT NULL,
> > ctrlid integer NOT NULL,
> > userid integer NOT NULL
> > );
> > ALTER TABLE ONLY devicegroup
> > ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
> > 
> >I want to make sure that records are only deleted when all 3 fields are
> >specified, so I tried make a trigger:
> > 
> > CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH
> > STATEMENT
> >   EXECUTE PROCEDURE deleteUserDev();
> > 
> > which could check for NOT NULL on the 3 fields before actual doing the
> > delete. Unfortunately this is not possible to
> > do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to
> > check for NOT NULL.
> > 
> >Any ideas ?
> > 
> >  Leif
> > 
> > 
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 
> 


-- 
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] Delete trigger

2015-09-18 Thread Leif Jensen
   Hi Ioana and David.

   Thank you. Yes, I can see the problem. I will look into your suggestions.

 Leif


- Original Message -
> On Friday, September 18, 2015, Leif Jensen <l...@crysberg.dk> wrote:
> 
> >Hello Laurenz,
> >
> >Thank you for you suggestion. I really want to aviod that someone
> > 'accidentally' deletes too much by typing (programming) a not full
> > qualified DELETE ... statement. In your case one would have to always use
> > the delete function, but no restrictions on using the DELETE statement.
> >
> >
> There is no way you can prevent a superuser from shooting themselves in the
> foot.  For anyone else you can enforce use of the function to perform the
> delete.
> 
> You could make a field called ok-to-delete and add a partial unique index
> on it so that only a single record can be marked ok to delete at a time and
> then have your trigger abort if it tries to delete a field without the ok
> to delete field set to true.
> 
> 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


[GENERAL] cascading replication and replication slots.

2015-06-23 Thread Leif Gunnar Erlandsen
Is it possible to use a replication_slot for a downstream-server when setting 
up cascading replication on 9.4





Leif G.




Re: [GENERAL] cascading replication and replication_slots

2015-06-23 Thread Leif Gunnar Erlandsen





Fra: Michael Paquier michael.paqu...@gmail.com
Sendt: 23. juni 2015 13:32

On Tue, Jun 23, 2015 at 8:18 PM, Leif Gunnar Erlandsen
leif.gunnar.erland...@basefarm.com wrote:
 Is it possible to use a replication_slot for a downstream-server when
 setting up cascading replication on 9.4

Yes. Just be careful that replication slot data is not included in a
base backup.
--
Michael

Thank you, got it to work now.
Created the replication slot on the cascading server after I had created the 
base backup.

Leif G.


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


[GENERAL] cascading replication and replication_slots

2015-06-23 Thread Leif Gunnar Erlandsen
Is it possible to use a replication_slot for a downstream-server when setting 
up cascading replication on 9.4




Leif G.






Vennlig hilsen/ Best regards
--


LEIF GUNNAR ERLANDSEN
Senior Database Consultant


BASEFARM | Nydalen Allé 37a | 0484 Oslo | Norway
Phone: +47 4000 4100 | Mobile: +47 906 24 949
leif.gunnar.erland...@basefarm.commailto:leif.gunnar.erland...@basefarm.com | 
www.basefarm.comhttp://www.basefarm.com

?

technical excellence - caring for your business



Re: [GENERAL] Server process crash - Segmentation fault

2014-05-09 Thread Leif Jensen
   Hello Tom, Adrian

   Thank you for your help and the patch. Things works nicely for me now :-).

 Leif


- Original Message -
 Adrian Klaver adrian.kla...@aklaver.com writes:
  On 05/08/2014 07:19 AM, Tom Lane wrote:
  9.3 patch is here:
  http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf
 
  Alright, so I obviously linked to the wrong patch because I pointed
  at
  HEAD and not REL9_3_STABLE.
 
 I think those patches are the same, actually, but I was trying to be
 careful.
 
  What I am trying to figure out is what is
  the distinction between commit and commitdiff?
 
 The commitdiff link shows you the actual diffs in the patch, the
 other
 one doesn't.
 
 In practice, Leif's going to want to hit the patch link anyway to
 get a
 clean downloadable patch; so likely what we should have pointed him at
 is
 http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=fc58c39d468587467c7c55b349c28167794eadaf
 I'm just in the habit of looking at the commitdiff versions of the
 web
 pages as being the best readability/information tradeoff for casual
 examination of a patch.
 
 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] Server process crash - Segmentation fault

2014-05-08 Thread Leif Jensen
   Hi Tom,

   I already compiled postgreSQL myself and now using 9.3.4, so I would very 
much like a patch. Where can I find that ?

 Leif


- Original Message -
 Leif Jensen l...@crysberg.dk writes:
 Could it be related to the OFFSET part of the statement ? I have
 another query on the same table without OFFSET, which seems to
 work fine.
 
 Yeah, the specific code path here involves executing a stable (or
 possibly
 immutable) SQL function in a LIMIT or OFFSET clause. I was able to
 reproduce the crash like so:
 
 create function foo(int) returns int as 'select $1 limit 1'
 language sql stable;
 
 begin;
 
 declare c cursor for select * from int8_tbl limit foo(3);
 
 select * from c;
 
 move backward all in c;
 
 select * from c;
 
 commit;
 
 You might be able to dodge the problem if you can make the SQL
 function
 inline-able (the LIMIT 1 in my example is just to prevent that from
 happening). A less appealing alternative is to mark the function
 VOLATILE, which I think would also prevent this crash, but might have
 negative performance consequences.
 
 If you don't mind building your own PG then you could grab the actual
 fix
 from our git repo; I should have something committed before long.
 
 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] Server process crash - Segmentation fault

2014-05-07 Thread Leif Jensen
   Hello Adrian,

   Thank you for your answer. I can post part of the code that makes these 
calls, but I'm not sure how much it would help. It is rather large function 
that makes these calls, and it is called all over the program. The part of the 
log posted is only a small excerpt of the use of the 
ApplDBConn_22854_f6adeb70_query, which has been used many many times before the 
log shown (167 in all to be exact ;-) ).

 Leif


- Original Message -
 On 05/06/2014 07:08 AM, Leif Jensen wrote:
  Hello.
 
  I was running PostgreSQL 9.1.4 when I got a server process crash
  (Segmentation fault) as the postgres log shown below. I tried
  upgrade to newest version 9.3.4, but this gives exactly the same
  problem.
 
  It is an (ecpg based) C-program that does tons of these scroll
  cursor exercises. Until recently this worked too but changes to
  totally different part of the program made this happen. (I have
  made way too many changes to this other part to be able to roll
  back the code :-( ). The system generates data all the time for
  this lookup, but I can grab the SQL from the postgres log and
  run it through psql and get the result I expect, so I don't see
  how it can be data related.
 
  Please help,
 
Leif
 
  .
  .
  .
  22864 2014-05-06 15:37:35.350 CEST LOG: statement: close execcurs
  22864 2014-05-06 15:37:35.350 CEST LOG: statement: deallocate
  ApplDBConn_22854_f6adeb70_query
  22864 2014-05-06 15:37:35.352 CEST DEBUG: parse
  ApplDBConn_22854_f6adeb70_query: SELECT data_type FROM
  information_schema.columns WHERE table_name =
  'l2_hb_water_hours_sum' AND column_name = '';
  22864 2014-05-06 15:37:35.353 CEST LOG: statement: declare execcurs
  scroll cursor for SELECT data_type FROM information_schema.columns
  WHERE table_name = 'l2_hb_water_hours_sum' AND column_name = '
  ';
  22864 2014-05-06 15:37:35.356 CEST LOG: statement: fetch first in
  execcurs
  22864 2014-05-06 15:37:35.358 CEST LOG: statement: close execcurs
  22864 2014-05-06 15:37:35.358 CEST LOG: statement: deallocate
  ApplDBConn_22854_f6adeb70_query
  22864 2014-05-06 15:37:35.359 CEST LOG: statement: commit
  22864 2014-05-06 15:37:35.359 CEST LOG: statement: start transaction
  read only
  22864 2014-05-06 15:37:35.360 CEST DEBUG: parse
  ApplDBConn_22854_f6adeb70_query: SELECT montime, year, month, day,
  hh, gal_hour, exp_hour, unsched_hour FROM l2_hb_water_hours_sum
  WHERE l2_hb_water_
  hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET (SELECT CASE
  WHEN count(*)  2000 THEN count(*) -2000 ELSE 0 END FROM
  l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 86 );
  22864 2014-05-06 15:37:35.365 CEST LOG: statement: declare execcurs
  scroll cursor for SELECT montime, year, month, day, hh, gal_hour,
  exp_hour, unsched_hour FROM l2_hb_water_hours_sum WHERE l2_hb
  _water_hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET
  (SELECT CASE WHEN count(*)  2000 THEN count(*) -2000 ELSE 0 END
  FROM l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 8
  6 );
 
 The code that generates the above would be helpful. The thing that
 catches my eye is that the first time you use
 ApplDBConn_22854_f6adeb70_query the parse and cursor queries are the
 same and all is good. The second time they are not and you get a
 failure. Without seeing what is going in in your code it is hard to
 tell
 if this significant or not.
 
  22864 2014-05-06 15:37:35.432 CEST LOG: statement: fetch first in
  execcurs
  21702 2014-05-06 15:37:35.440 CEST DEBUG: server process (PID 22864)
  was terminated by signal 11: Segmentation fault
  21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was
  running: fetch first in execcurs
  21702 2014-05-06 15:37:35.440 CEST LOG: server process (PID 22864)
  was terminated by signal 11: Segmentation fault
  21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was
  running: fetch first in execcurs
  21702 2014-05-06 15:37:35.440 CEST LOG: terminating any other active
  server processes
 
 
 --
 Adrian Klaver
 adrian.kla...@aklaver.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] Server process crash - Segmentation fault

2014-05-07 Thread Leif Jensen
   Could it be related to the OFFSET part of the statement ? I have another 
query on the same table without OFFSET, which seems to work fine.

 Leif


- Original Message -
 Leif Jensen l...@crysberg.dk writes:
 Here is a gdb dump of the backtrace at the server process crash.
 I have also included the code that generates these calls. As
 mentioned below this specific connection has been used many times
 before the crash. Also, we are aware of the thread caveat that
 only using a connection from one thread at a time. Therefore the
 strange connection name that includes both the process id and
 the thread id. This is for the code to make sure that a
 connection is only used in the thread it is meant to.
 
 Hm. The crash looks like it must be because ActiveSnapshot is null
 (not set). Since we're doing a FETCH, the active snapshot ought to
 be the one saved for the cursor query by DECLARE CURSOR. It looks
 like the problem is that pquery.c only bothers to install that as the
 active snapshot while calling ExecutorRun, but in this stack trace
 we're in ExecutorRewind.
 
 I wonder if it's a bad idea for ExecReScanLimit to be executing
 user-defined expressions? But it's been like that for awhile,
 and I think we might have a hard time preserving the bounded-sort
 optimization if we didn't do that.
 
 Anyway the simple fix would be to ensure we install the query
 snapshot as active before calling ExecutorRewind.
 
 One interesting question is why this issue hasn't been seen before;
 it seems like it'd not be that hard to hit.
 
 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] Server process crash - Segmentation fault

2014-05-06 Thread Leif Jensen
   Hello.

   I was running PostgreSQL 9.1.4 when I got a server process crash 
(Segmentation fault) as the postgres log shown below. I tried upgrade to newest 
version 9.3.4, but this gives exactly the same problem.

   It is an (ecpg based) C-program that does tons of these scroll cursor 
exercises. Until recently this worked too but changes to totally different part 
of the program made this happen. (I have made way too many changes to this 
other part to be able to roll back the code :-( ). The system generates data 
all the time for this lookup, but I can grab the SQL from the postgres log and 
run it through psql and get the result I expect, so I don't see how it can be 
data related.

   Please help,

 Leif

.
.
.
22864 2014-05-06 15:37:35.350 CEST LOG:  statement: close execcurs
22864 2014-05-06 15:37:35.350 CEST LOG:  statement: deallocate 
ApplDBConn_22854_f6adeb70_query
22864 2014-05-06 15:37:35.352 CEST DEBUG:  parse 
ApplDBConn_22854_f6adeb70_query: SELECT data_type FROM 
information_schema.columns WHERE table_name = 'l2_hb_water_hours_sum' AND 
column_name = '';
22864 2014-05-06 15:37:35.353 CEST LOG:  statement: declare execcurs  scroll 
cursor  for SELECT data_type FROM information_schema.columns WHERE table_name = 
'l2_hb_water_hours_sum' AND column_name = '
';
22864 2014-05-06 15:37:35.356 CEST LOG:  statement: fetch first in execcurs
22864 2014-05-06 15:37:35.358 CEST LOG:  statement: close execcurs
22864 2014-05-06 15:37:35.358 CEST LOG:  statement: deallocate 
ApplDBConn_22854_f6adeb70_query
22864 2014-05-06 15:37:35.359 CEST LOG:  statement: commit
22864 2014-05-06 15:37:35.359 CEST LOG:  statement: start transaction read only
22864 2014-05-06 15:37:35.360 CEST DEBUG:  parse 
ApplDBConn_22854_f6adeb70_query: SELECT montime, year, month, day, hh, 
gal_hour, exp_hour, unsched_hour FROM l2_hb_water_hours_sum  WHERE  l2_hb_water_
hours_sum.ctrlid =  86  ORDER BY year,month,day,hh OFFSET (SELECT CASE WHEN 
count(*)  2000 THEN count(*) -2000 ELSE 0 END FROM l2_hb_water_hours_sum
WHERE  l2_hb_water_hours_sum.ctrlid =  86 );
22864 2014-05-06 15:37:35.365 CEST LOG:  statement: declare execcurs  scroll 
cursor  for SELECT montime, year, month, day, hh, gal_hour, exp_hour, 
unsched_hour FROM l2_hb_water_hours_sum  WHERE  l2_hb
_water_hours_sum.ctrlid =  86  ORDER BY year,month,day,hh OFFSET (SELECT CASE 
WHEN count(*)  2000 THEN count(*) -2000 ELSE 0 END FROM l2_hb_water_hours_sum  
  WHERE  l2_hb_water_hours_sum.ctrlid =  8
6 );
22864 2014-05-06 15:37:35.432 CEST LOG:  statement: fetch first in execcurs
21702 2014-05-06 15:37:35.440 CEST DEBUG:  server process (PID 22864) was 
terminated by signal 11: Segmentation fault
21702 2014-05-06 15:37:35.440 CEST DETAIL:  Failed process was running: fetch 
first in execcurs
21702 2014-05-06 15:37:35.440 CEST LOG:  server process (PID 22864) was 
terminated by signal 11: Segmentation fault
21702 2014-05-06 15:37:35.440 CEST DETAIL:  Failed process was running: fetch 
first in execcurs
21702 2014-05-06 15:37:35.440 CEST LOG:  terminating any other active server 
processes
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22896
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22893
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22882
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22751
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22749
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 22748
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 21705
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 21704
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 21706
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 21707
22751 2014-05-06 15:37:35.440 CEST WARNING:  terminating connection because of 
crash of another server process
22751 2014-05-06 15:37:35.440 CEST DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because another 
server process exited abnormally and possibl
y corrupted shared memory.
22751 2014-05-06 15:37:35.440 CEST HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
21702 2014-05-06 15:37:35.440 CEST DEBUG:  sending SIGQUIT to process 21708


-- 
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] coalesce function

2013-06-20 Thread Leif Biberg Kristensen
Torsdag 20. juni 2013 21.45.02 skrev itishree sukla:
 Hi All,
 
 I am using coalesce(firstname,lastname), to get the result if first name is
 'NULL' it will give me lastname or either way. I am having data like
 instead of NULL,  blank null ( i mean something like '' ) for which
 coalesce is not working, is there any workaround or other function
 available in postgresql, please do let me know.

CASE WHEN firstname NOT IN (NULL, '') THEN firstname ELSE lastname END;

regards, Leif


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


[GENERAL] ECPG SET CONNECTION

2013-05-31 Thread Leif Jensen
   Hi guys.

   In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION 
connection name; it is stated that This is not thread-aware.

   When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it 
looks very much like it is thread-aware if translated with the 
--enable-thread-safety option.

   What should I believe ?

 Leif


-- 
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] Slow query and using wrong index, how to fix? Probably naive question..

2013-05-22 Thread Leif Gunnar Erlandsen
You might want to try with UNION and then sort the result of this query.

The index history_lookup_lookupid_creator_index wont be used when you are 
having an OR in your WHERE statement.

 select history.id, history.created, creator, contact, history.type, lookup, 
lookupid, value 
from history  
where (lookup = 'phone' and lookupid = '672') 
union
 select history.id, history.created, creator, contact, history.type, lookup, 
lookupid, value 
from history  
where  creator = '790'

Leif Gunnar Erlandsen




Fra: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
p#229; vegne av Antonio Goméz Soto [antonio.gomez.s...@gmail.com]
Sendt: 22. mai 2013 10:50
Til: pgsql-general@postgresql.org
Emne: [GENERAL] Slow query and using wrong index, how to fix? Probably naive 
question..

Hi,

I am using postgresql 8.1 (CentOS5). I have the following table:

system # \d history
   Table public.history
  Column  |   Type   |  Modifiers
--+--+--
 id   | integer  | not null default 
nextval('history_id_seq'::regclass)
 created  | timestamp with time zone |
 creator  | integer  | not null default 1
 contact  | integer  | not null default 1
 type | character varying| not null default ''::character varying
 lookup   | text |
 lookupid | integer  | not null default 1
 value| text |
Indexes:
history_pkey PRIMARY KEY, btree (id)
history_created_index btree (created)
history_creator_index btree (creator)
history_lookup_lookupid_creator_index btree (lookup, lookupid, creator)
history_lookup_lookupid_index btree (lookup, lookupid)
Foreign-key constraints:
history_contact_constraint FOREIGN KEY (contact) REFERENCES contact(id)
history_creator_constraint FOREIGN KEY (creator) REFERENCES contact(id)

system # explain select history.id, history.created, creator, contact, 
history.type, lookup, lookupid, value from history  where (lookup = 'phone' and 
lookupid = '672') or creator = '790' order by history.creator desc limit 1000;
   QUERY PLAN

 Limit  (cost=0.00..132041.59 rows=1000 width=58)
   -  Index Scan Backward using history_creator_index on history  
(cost=0.00..11746815.97 rows=88963 width=58)
 Filter: (((lookup = 'phone'::text) AND (lookupid = 672)) OR (creator = 
790))
(3 rows)

This table contains 2 million rows, the query takes 800 seconds on SSD HD.

I think - probably naive - the query should use the 
history_lookup_lookupid_creator_index.

Why doesn't it, and how can I speed up the query?

Thanks,
Antonio.




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


-- 
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] Storing Special Characters

2013-05-14 Thread Leif Biberg Kristensen
Tirsdag 14. mai 2013 18.05.05 skrev Rebecca Clarke:
 Hi there.
 
 This may be the wrong forum to inquire in, but I'd be grateful if I could
 directed in the right direction if that is the case.
 
 I am currently using Postgresql 9.1.
 
 I have a table in which I want to store shop names. Some of the shop names
 contain 'é' and '£'.
 
 The query below works when I run it through pg_admin:
 
 insert into retail(storename) values ('£'::character varying)
 
 However, when I run the same query through pg_query in PHP, I get:
 
 PHP Warning:  pg_query(): Query failed: ERROR:  invalid byte sequence for
 encoding UTF8: 0xa3

It's possibly a client encoding problem. See the PHP documentation on

http://php.net/manual/en/function.pg-set-client-encoding.php

regards, Leif


-- 
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] Money casting too liberal?

2013-03-31 Thread Leif Biberg Kristensen
 Søndag 31. mars 2013 18.45.10 skrev ajmcello :
 unsubscribe
 
 On Sun, Mar 31, 2013 at 3:31 AM, Gavan Schneider pg-...@snkmail.com wrote:
  On 30/3/13 at 11:09 PM, D'Arcy J.M. Cain wrote:
   I am formulating Cain's Law.  Something like If a discussion lasts
   
  long enough, someone will mention Godwin's Law.
  
  +1
  
  More formally:
  As an online discussion grows longer, the probability of Godwin's Law
  being mentioned approaches one.

First corollary to Cain's Law:

As an online discussion grows longer, the probability of someone trying to 
unsubscribe by posting a reply to the discussion approaches one.

regards, Leif


-- 
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] Can't Drop Role

2013-03-08 Thread Leif Gunnar Erlandsen
You should alter owner of the functions.

Try alter function function_name owner to new_owner;


Leif Gunnar Erlandsen



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


[GENERAL] Rules on views - Changes from 8.4 to 9.1 ?

2013-01-28 Thread Leif Jensen
   Hello.

   We have a system that has been running using PostgreSQL 8.4. We have now 
upgraded to PostgreSQL 9.1. The system has several rules on views and now most 
of these does not seem to work anymore. It is rather simple rules with mainly 
only 1 replacement sql (update for 'on update' and insert for 'on insert').

   Have something basic things changed on the rule system and if so, where do I 
find information about it ?

   Please help,

 Leif


-- 
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] Update rule on a view - what am I doing wrong

2013-01-22 Thread Leif Jensen
   Hi Jasen.

   Thank you for your response (also thank you to Tom).

   I have now tried your suggestion, but I'm not sure how you have implemented 
the plpgsql function. When I create the function: CREATE update_rule_func( old 
record, new record ) AS ...  I am told, that I cannot use record for the 
parameter type. Could you please expand a little on your example ?

 Leif


- Jasen Betts ja...@xnet.co.nz wrote:

 On 2013-01-18, Leif Jensen l...@crysberg.dk wrote:
 
 I have been fighting a problem with an update rule on a view. I
  have a view that combines two tables where the 'sub' table (scont)
 can
  have several rows per row in the 'top' table (icont). The view
  combines these to show only one record per row in the top table. To
 be
  able to update on this view I have created a rule 'on update'. The
  rule needs to have both UPDATE, DELETE, and INSERT commands. Is
 this
  not possible or am I doing something else wrong ?   
 
 when I hit that issue in 8.4 i used a plpgsql function
 
   ... do instead select update_rule_func(old,new);
   
 -- 
 ⚂⚃ 100% natural
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Update rule on a view - what am I doing wrong

2013-01-22 Thread Leif Jensen
   Hi Marc,

Thanks a lot. That works fine. The names 'NEW' and 'OLD' works fine.

 Leif


- Marc Schablewski m...@clickware.de wrote:

 Hi Leif,
 
 Am 22.01.2013 14:34, schrieb Leif Jensen:
 
 
 CREATE update_rule_func( old record, new record ) AS ...  I am told,
 that I cannot use record for the parameter type. Could you please You
 should use your view instead of 'record' as parameter type, i.e.
 CREATE update_rule_func( old V_YOUR_VIEW, new V_YOUR_VIEW ) AS ... .
 Also, I'm not sure if 'new' and 'old' are reserved keywords in
 PostgreSQL, so you might want to choose different names for your
 parameters if you still have trouble with that function.
 
 Marc


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


[GENERAL] Update rule on a view - what am I doing wrong

2013-01-18 Thread Leif Jensen
   Hello,

   I have been fighting a problem with an update rule on a view. I have a view 
that combines two tables where the 'sub' table (scont) can have several rows 
per row in the 'top' table (icont). The view combines these to show only one 
record per row in the top table. To be able to update on this view I have 
created a rule 'on update'. The rule needs to have both UPDATE, DELETE, and 
INSERT commands. Is this not possible or am I doing something else wrong ?

   In the included sql script I have tried to show the problem. When the final 
update statement is executed, I get a 'duplicate key violation' on a record 
that has never been there.

   I am using PostgreSQL version 9.1.7 (running on Linux/Ubuntu 12.04). What am 
I doing wrong ?

   Please help,

 Leif
DROP VIEW  hcont;
DROP TABLE icont;
CREATE TABLE icont (
  cid  INTEGER,
  sid  INTEGER,
  rt   INTEGER
);
ALTER TABLE icont ADD PRIMARY KEY ( cid, sid );

DROP TABLE scont;
CREATE TABLE scont (
  cid  INTEGER,
  sid  INTEGER,
  vno  INTEGER,
  val  INTEGER
);
ALTER TABLE scont ADD PRIMARY KEY ( cid, sid, vno );

CREATE VIEW hcont AS
  SELECT ic.cid AS oid, ic.cid, ic.sid, ic.rt,
 s1.val AS value1,
	 s2.val AS value2
FROM icont ic
 LEFT OUTER JOIN scont s1 ON s1.cid = ic.cid AND s1.sid = ic.sid and s1.vno = 1
 LEFT OUTER JOIN scont s2 ON s2.cid = ic.cid AND s2.sid = ic.sid and s2.vno = 2
  ;

CREATE OR REPLACE RULE hcont_udpate AS ON UPDATE TO hcont DO INSTEAD (
  UPDATE icont SET rt = NEW.rt
WHERE cid = NEW.cid AND sid = NEW.sid;
  DELETE FROM scont
WHERE cid = NEW.cid AND sid = NEW.sid;
  INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 1, NEW.value1 );
  INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 2, NEW.value2 );
);

-- Populate the tables
INSERT INTO icont VALUES ( 1, 1, 20 );
INSERT INTO icont VALUES ( 1, 2, 40 );
INSERT INTO icont VALUES ( 1, 3, 60 );
INSERT INTO scont VALUES ( 1, 1, 1, 3 );
INSERT INTO scont VALUES ( 1, 1, 2, 5 );

SELECT * from scont;

UPDATE hcont SET cid = 1, sid = 2, rt = 80, value1 = 7, value2 = 9 WHERE cid = 1;


-- 
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] Using composite types within PLPGSQL Function

2013-01-08 Thread Leif Biberg Kristensen
 Tirsdag 8. januar 2013 16.10.03 skrev Graeme Hinchliffe :
 My example code is :
 
 CREATE TYPE testtype AS (
 a INTEGER,
 b INTEGER
 );
 
 CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$
 DECLARE
   x testtype;
 BEGIN
 (x).a:=1;
 RETURN 1;
 END
 $$ LANGUAGE plpgsql;
 
 This throws up syntax errors for the (x).a:=1; line.. I have also tried
 SELECT INTO (x).a 1;
 
 Any help much appreciated, version of PostgreSQL is 8.4 under Debian.

It should work with

x.a := 1;

without the parentheses. See http://solumslekt.org/blog/?p=91 for an example 
of composite types and functions.

regards, Leif


-- 
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] Where is 'createdb'?

2012-11-01 Thread Leif Biberg Kristensen
 Torsdag 1. november 2012 16.32.42 skrev Kevin Burton :
 This is probably a question for the authors of a book I have been reading
 but it may be faster to get an answer here.
 
 
 
 I was trying to follow along in a book 'Seven Databases in Seven Weeks' and
 chapter 2 deals with PostgreSQL. One of the first things it does is issue a
 command 'createdb book'. The text before this command says, Once you have
 Postgres installed, create a schema called book using the following
 command: $ createdb book' But when I tried to issue this command (at a
 Linux command prompt) I get 'createdb command not found'. Are the authors
 out of date? What is the current command?

leif@balapapa ~ $ which createdb
/usr/bin/createdb


regards, Leif


-- 
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] Great site for comparing databases (or anything else)

2012-10-27 Thread Leif Biberg Kristensen
 Søndag 28. oktober 2012 01.17.45 skrev Gavin Flower :
 Also note that for features that are obviously complicated or advanced,
 Postgres tends to a lot better than MySQL.

It's like comparing BASIC to C. BASIC has a low threshold, but you will very 
quickly bump your head against the wall.

MySQL, the BASIC of db engines?

regards, Leif


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


Re: [GENERAL] database corruption questions

2012-10-13 Thread Leif Biberg Kristensen
 Lørdag 13. oktober 2012 23.53.03 skrev Heine Ferreira :
 Hi
 
 Are there any best practices for avoiding database
 corruption?

In my experience, database corruption always comes down to flaky disk drives. 
Keep your disks new and shiny eg. less than 3 years, and go for some kind of 
redundancy in a RAID configuration.

regards, Leif


-- 
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] insert ... returning in plpgsql

2012-10-02 Thread Leif Biberg Kristensen
 Tirsdag 2. oktober 2012 15.01.08 skrev Willy-Bas Loos :
 Hi,
 (postgres 9.1)
 I was doing something like this in a plpgsql function, but i got a Syntax
 Error.
 
 t_var:=(insert into table1(field2) values ('x') returning field1);
 
 Is there no support for using RETURNING in insert, update, delete queries
 to fill a variable in plpgsql?

Try

insert into table1(field2) values ('x') returning field1 into t_var

regards, Leif


-- 
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] problem with recreating database with export

2012-09-27 Thread Leif Biberg Kristensen
 Torsdag 27. september 2012 16.55.15 skrev Dennis Gearon :
 note to future
 To anyone reading this in the future, if you have problems importing a
 plain text database export, it is usually impossible to do:
 psql -d some_dbase -f the_backup.sql. I don't know why. What works is doing
 'cd ./the_files_directory', going INTO psql command line, then issuing '\i
 the_backup.sql', and it's really fast. 8 seconds for 128 mbyte file.
 
 PS,do this as user 'postgres' on the system.
 /note to future

FWIW, here are a few relevant lines from my reload.sh script, which I have 
been using since version 7.4, and which is working perfectly:

dropdb $DB
createdb --encoding=UNICODE $DB
psql -U postgres -d $DB -f $INFILE  restore.log 21

Note that I'm running this as a regular postgres user, whose only privilege is 
to create new databases.

regards, Leif


-- 
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] Need to run a job in PgAdmin-III

2012-09-24 Thread Leif Biberg Kristensen
 Mandag 24. september 2012 11.06.32 skrev pavithra :
 I have a scheduled a sql query as select sysdate from dual and i have
 given as Data Export.

That's an Oraclism. Have you actually tested the query in psql?

postgres= select sysdate from dual;
ERROR:  relation dual does not exist
LINE 1: select sysdate from dual;
^
postgres= 

Maybe this is what you want?

postgres= select current_date;
date

 2012-09-24
(1 row)

postgres= 

regards, Leif


-- 
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] Using psql -f to load a UTF8 file

2012-09-20 Thread Leif Biberg Kristensen
 Torsdag 20. september 2012 19.27.22 skrev Alan Millington :
 Thank you for the link. I am using Notepad, which inserts the byte order
 mark. Following the links a bit further, I gather that the version of
 Notepad that I am using may not identify a UTF8 file correctly if the byte
 order mark is omitted. Also, as I mentioned, Python makes use of it. (From
 the Python documentation on Encoding declarations: If the first bytes of
 the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
 encoding is UTF-8 (this is supported, among others, by Microsoft’s
 Notepad).) 
 The conclusion seems to be that I must use one editor for Python, and
 another for Postgres. 

It's been a long time since I last wrote a Python script, but I've always used 
the explicit encoding directive:

#! /usr/bin/env python
# -*- encoding: utf-8 -*-

See http://docs.python.org/release/2.5.1/ref/encodings.html which also 
mentions the BOM method as an alternative.

regards, Leif


-- 
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] Using psql -f to load a UTF8 file

2012-09-20 Thread Leif Biberg Kristensen
 Torsdag 20. september 2012 16.56.16 skrev Alan Millington :
 psql. But how am I supposed to remove the byte order mark from a UTF8
 file? I thought that the whole point of the byte order mark was to tell
 programs what the file encoding is. Other programs, such as Python, rely
 on this.

http://en.wikipedia.org/wiki/Byte_order_mark

While the Byte Order Mark is important for UTF-16, it's totally irrelevant to 
the UTF-8 encoding. Still you'll find several editors that automatically input 
BOMs in every text file. There is usually a setting Insert Byte Order Mark 
somewhere in the configuration, and it may be on by default.

regards, Leif


-- 
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] Trying to execute several queries involving temp tables in a PHP script

2012-06-13 Thread Leif Biberg Kristensen
 Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber :

 Any ideas please on how to handle this situation
 in PHP scripts, do I really have to encapsulate
 my calls into a pl/PgSQL function?

I believe that Misa Simic's idea that you can do it all in a single query 
without temp tables is correct. But anyway, it's always a good idea to 
encapsulate multiple interdependent queries in a single pl/pgsql function. I 
tend to keep my PHP code as simple as possible, and do most of the work inside 
the database.

regards, Leif
http://code.google.com/p/yggdrasil-genealogy/

-- 
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] Procedural Languages

2012-05-31 Thread Leif Biberg Kristensen
 Torsdag 31. mai 2012 17.07.19 skrev Merlin Moncure :
 pl/pgsql is unique in that it has 'first class queries' -- sql is
 intermixed freely with procedural code and it uses the same type
 system and error handling mechanisms  (although the syntax is
 different).   this directly translates into direct, impactful coding
 as long as you are not trying to do things that are awkward for the
 language like heavy computation or string processing.

I'm using plpgsql for string processing all the time, mostly with regexes, and 
don't find it particularly awkward. That may of course be an example of the If 
all you've got is a hammer, all problems look like nails syndrome. But I've 
never felt a need for installing another pl language.

regards, Leif

-- 
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] what Linux to run

2012-03-03 Thread Leif Biberg Kristensen
 Lørdag 3. mars 2012 01.43.29 skrev Gavin Flower :

 I think if you are going to select a member of the Debian family, I
 would strongly recommend Debian itself. I have the impression that the
 Debian community is more serious about quality than Canonical (the
 company behind Ubuntu).

I haven't run Debian for ten years, when I had a headless old PC running with 
a LAMP stack. Since I discovered Gentoo, that has been my preferred distro. 
However, I'm currently in the process of setting up a dedicated Web server 
with Debian as it may one day be another person's responsibility to admin this 
box, and I would consider it cruel to leave a Gentoo box to anyone but the 
most devoted Linux fans.

My current gripe is this: The «stable» version of Postgres on Debian is 8.4. 
In order to install 9.1, I added this line to /etc/apt/sources.list:

deb http://ftp.debian.org/debian unstable main contrib non-free

Then I did an apt-get update and 

apt-get install postgresql-9.1 postgresql-client-9.1

Finally I commented out the added line of /etc/apt/sources.list.

This seems a rather roundabout way, is there a better one?

regards, Leif

-- 
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] what Linux to run

2012-03-03 Thread Leif Biberg Kristensen
 Lørdag 3. mars 2012 12.34.27 skrev Raymond O'Donnell :

 You can get Postgres 9.1 from backports.debian.org:
 
 deb http://backports.debian.org/debian-backports squeeze-backports main

Ah, sweet, thank you!

regards, Leif

-- 
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] Philosophical question

2011-12-14 Thread Leif Biberg Kristensen
 Onsdag 14. desember 2011 22.21.04 skrev Chris Angelico :
 The biggest problem with PHP, imho, is actually that it's so easy to
 use. Anyone can get a WYSIWYG editor, save as HTML, and have a web
 page... and then all you need to do is rename it to .php and put
 some special tags in it, and look! You have a dynamic web page and
 it's so awesome! At least, it is until you try to go further, and you
 start adding mess on top of mess on top of mess.

In my opinion, that's a pretty elitistic view. Certainly, that's one way of 
writing PHP, but it isn't the only one. Quite a few of us have started with 
something like what you've outlined here, but have long ago moved on to more 
maintainable coding practices.

The good thing about PHP is the low threshold, and you can start using it 
doing exactly what you outlined in your first paragraph. But somebody coming to 
PHP from any old procedural language, will soon find that PHP lends itself well 
to building function upon function, until you can really write the code you 
need to express anything you want.
 
 There are a few more fundamental issues with the language, but mainly,
 it gets the blame for myriad bad PHP programmers.

Yes there's a lot of bad programmers out there. Most of them code in Java or 
Visual Basic.

 I prefer Pike. It's designed for writing servers, performance is
 pretty decent, it's a high level language, and it has great database
 support (including Postgres-specific features, some of which are quite
 handy).

I'd like to look at this Pike. I don't think that my Web host supports it, but 
it might still be a fun experience.

regards, Leif

The Yggdrasil project:
http://code.google.com/p/yggdrasil-genealogy/

-- 
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] I/O error on data file, can't run backup

2011-10-06 Thread Leif Biberg Kristensen
On Thursday 6. October 2011 07.07.11 Craig Ringer wrote:
 On 10/06/2011 03:06 AM, Leif Biberg Kristensen wrote:
  I seemingly fixed the problem by stopping postgres and doing:
  
  balapapa 612249 # mv 11658 11658.old
  balapapa 612249 # mv 11658.old 11658
  
  And the backup magically works.
 
 Woo! That's ... interesting.
 
 I'd be inclined to suspect filesystem corruption, a file system bug /
 kernel bug (not very likely if you're on ext3), flakey RAM, etc rather
 than a failing disk ... though a failing disk _could_ still be the culprit.
 
 Use smartmontools to do a self-test; if 'smartctl -d ata -t long
 /dev/sdx' (where 'x' is the drive node) is reported by 'smartctl -d ata
 -a /dev/sdx' as having passed, there are no pending or uncorrectable
 sectors, and the disk status is reported as 'HEALTHY' your disk is quite
 likely OK. Note that a 'PASSED' or 'HEALTHY' report by its self doesn't
 mean much, disk firmwares often return HEALTHY even when the disk can't
 even read sector 0.
 
 I strongly recommend making a full backup, both a pg_dump *and* a
 file-system level copy of the datadir. Personally I'd then do a test
 restore of the pg_dump backup on a separate Pg instance and if it looked
 OK I'd re-initdb then reload from the dump.

Craig,
Thank you very much for the tip on smartmontools, which I didn't know about. 
There indeed appears to be some problems with this disk:

8---

balapapa ~ # smartctl -d ata -a /dev/sdb -s on
smartctl 5.40 2010-10-16 r3189 [x86_64-pc-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF INFORMATION SECTION ===
Model Family: Seagate Barracuda 7200.11 family
Device Model: ST31000340AS
Serial Number:9QJ1ZMHY
Firmware Version: SD15
User Capacity:1 000 204 886 016 bytes
Device is:In smartctl database [for details use: -P show]
ATA Version is:   8
ATA Standard is:  ATA-8-ACS revision 4
Local Time is:Thu Oct  6 07:46:19 2011 CEST

== WARNING: There are known problems with these drives,
AND THIS FIRMWARE VERSION IS AFFECTED,
see the following Seagate web pages:
http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207931
http://seagate.custkb.com/seagate/crm/selfservice/search.jsp?DocId=207951

SMART support is: Available - device has SMART capability.
SMART support is: Disabled

=== START OF ENABLE/DISABLE COMMANDS SECTION ===
SMART Enabled.

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status:  (0x82) Offline data collection activity
was completed without error.
Auto Offline Data Collection: Enabled.
Self-test execution status:  (  25) The self-test routine was aborted by
the host.
Total time to complete Offline 
data collection: ( 650) seconds.
Offline data collection
capabilities:(0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off 
support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities:(0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability:(0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine 
recommended polling time:(   1) minutes.
Extended self-test routine
recommended polling time:( 236) minutes.
Conveyance self-test routine
recommended polling time:(   2) minutes.
SCT capabilities:  (0x103b) SCT Status supported.
SCT Error Recovery Control supported.
SCT Feature Control supported.
SCT Data Table supported.

SMART Attributes Data Structure revision number: 10
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME  FLAG VALUE WORST THRESH TYPE  UPDATED  
WHEN_FAILED RAW_VALUE
  1 Raw_Read_Error_Rate 0x000f   114   099   006Pre-fail  Always   
-   61796058
  3 Spin_Up_Time0x0003   094   092   000Pre-fail  Always   
-   0
  4 Start_Stop_Count0x0032   100   100   020Old_age   Always   
-   46
  5 Reallocated_Sector_Ct   0x0033   100   100   036Pre-fail  Always   
-   1
  7 Seek_Error_Rate

[GENERAL] I/O error on data file, can't run backup

2011-10-05 Thread Leif Biberg Kristensen
Running postgresql 9.0.5 on 

balapapa ~ # uname -a
Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64 
Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux

I'm trying to run pg_dump on my database, and get an error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  could not read block 1 in file 
base/612249/11658: Inn/ut-feil
pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT 
rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM 
pg_opfamily

I have tried to stop postgresql and take a filesystem backup of the data 
directory with a cp -ax, but it crashes on the same file. I've looked at the 
directory with ls -l, and the file looks pretty normal to me. I've also 
rebooted from a live CD and run fsck on my /var partition, and it doesn't find 
any problem.

The database is still working perfectly.

The backup script overwrote my previous backup with a 40 byte file (yes silly 
me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a 
recent backup anymore.

Is this fixable?

regards, Leif

-- 
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] I/O error on data file, can't run backup

2011-10-05 Thread Leif Biberg Kristensen
On Wednesday 5. October 2011 20.42.00 Tom Lane wrote:
 Postgres can't magically resurrect data that your drive lost, if that's
 what you were hoping for.  However, you might be in luck, because that
 file is probably just an index and not original data.  Try this:
 
   select relname from pg_class where relfilenode = 11658;
 
 On my 9.0 installation I get pg_opclass_am_name_nsp_index.  If you get
 the same (or any other index for that matter) just reindex that index
 and you'll be all right ... or at least, you will be if that's the only
 file your drive has lost.

Tom,
this is what I get:

postgres@balapapa ~ $ psql pgslekt
psql (9.0.5)
Type help for help.

pgslekt=# select relname from pg_class where relfilenode = 11658;
   relname   
-
 pg_opfamily
(1 row)

regards, Leif

-- 
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] I/O error on data file, can't run backup

2011-10-05 Thread Leif Biberg Kristensen
I seemingly fixed the problem by stopping postgres and doing:

balapapa 612249 # mv 11658 11658.old
balapapa 612249 # mv 11658.old 11658

And the backup magically works.

I'm gonna move the data to another disk right now.

regards, Leif

-- 
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] I/O error on data file, can't run backup

2011-10-05 Thread Leif Biberg Kristensen
On Wednesday 5. October 2011 22.41.49 Tom Lane wrote:
 Leif Biberg Kristensen l...@solumslekt.org writes:

  I'm gonna move the data to another disk right now.
 
 Good plan.

Couple of things I forgot to mention, in case it matters:

The disk is a 1 TB Seagate Barracuda S-ATA, and it has been in use for about a 
year. I've been using this brand since way back around 1998 without any 
problems, but have never used any disk more than 3 years. The file system is 
ext3.

I had a hang on the machine a few hours earlier that required a power-off 
reboot. That has been a problem with this rig since I built it about a year 
ago, it's probably a funky connection somewhere. This may be the direct cause 
of the I/O error, which also may mean that the disk is not to blame.

I'm so used to postgres and everything else coming up without a hiccup after a 
power-off that I don't usually pay much attention to it. But I'm certainly 
going to rework my backup strategy, and keep several generations.

regards, Leif

-- 
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] I/O error on data file, can't run backup

2011-10-05 Thread Leif Biberg Kristensen
On Thursday 6. October 2011 00.17.38 Steve Crawford wrote:
 I'm thinking perhaps a funky memory problem - you are having odd crashes
 after all.

I've been thinking about the memory myself, but it passes memtest86plus with 
flying colors. Or at least it did the last time I checked which is a few months 
ago.

The problems got a lot better after I replaced a monster Radeon XFX video card 
with a very basic fanless NVidia card (with the added bonus that I can now 
actually watch Flash videos in full screen), which may point to overheating 
issues.

In other news: I discovered that injecting `date +%u` into the backup file name 
at an appropriate place will number it by weekday, which is great for keeping 
daily backups for a week.

regards, Leif.

-- 
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 find freak UTF-8 character?

2011-10-02 Thread Leif Biberg Kristensen
On Sunday 2. October 2011 15.53.50 pasman pasmański wrote:
 Its simple to remove strange chars  with regex_replace.

True, but first you have to know how to represent a «strange char» in 
Postgresql :P

It isn't all that obvious, and it's difficult to search for the solution. I 
tried a lot of different search terms in Google, and none of them turned up 
anything near what I needed.

regards, Leif

-- 
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 find freak UTF-8 character?

2011-10-02 Thread Leif Biberg Kristensen
On Sunday 2. October 2011 16.34.27 Cédric Villemain wrote:
 you may have miss this one :
 http://tapoueh.org/blog/2010/02/23-getting-out-of-sql_ascii-part-2.html

That's an, uh, interesting article, but as far as I can see, it doesn't tell 
anything about how to find a perfectly legal three-byte UTF-8 character that 
doesn't have a counterpart in LATIN1, given that all I know about it is its 
hexadecimal value.

I know how to do it now, and I consider the problem solved. Hopefully, this 
thread may help others who stumbles upon the same issue.

regards, Leif

-- 
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 find freak UTF-8 character?

2011-10-02 Thread Leif Biberg Kristensen
On Sunday 2. October 2011 17.54.52 Raymond O'Donnell wrote:
 I may have missed it upthread, but if you haven't already would you
 consider writing up your solution for the benefit of the archives?

I did, in my own first reply to the original message:

SELECT * FROM foo WHERE bar LIKE E'%\xe2\x80\x8e%';

The trick is obviously to escape each byte in the sequence.

Maybe I'll write a blog post about it. It appears to be weakly documented, or 
at least very hard to find. Or maybe it's just me being dense.

regards, Leif.

-- 
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 find freak UTF-8 character?

2011-10-01 Thread Leif Biberg Kristensen
I've somehow introduced a spurious UTF-8 character in my database. When I try 
to export to an application that requires LATIN1 encoding, my export script 
bombs out with this message:

psycopg2.DataError: character 0xe2808e of encoding UTF8 has no equivalent in 
LATIN1

I figure that it should be easy to find the offending character in the database 
by doing a SELECT * FROM foo WHERE bar like '%\0xe2808e%' or something like 
that, but I can't find the correct syntax, I can't find a relevant section in 
the manual, and I can't figure out how to google this problem.

regards, Leif



-- 
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 find freak UTF-8 character?

2011-10-01 Thread Leif Biberg Kristensen
On Saturday 1. October 2011 07.55.01 Leif Biberg Kristensen wrote:
 I've somehow introduced a spurious UTF-8 character in my database. When I
 try to export to an application that requires LATIN1 encoding, my export
 script bombs out with this message:
 
 psycopg2.DataError: character 0xe2808e of encoding UTF8 has no equivalent
 in LATIN1

I finally figured it out, with a little help from maatb's unicode database 
(http://vazor.com/unicode/c200E.html):

SELECT * FROM foo WHERE bar LIKE E'%\xe2\x80\x8e%';

regards, Leif


-- 
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 find freak UTF-8 character?

2011-10-01 Thread Leif Biberg Kristensen
On Saturday 1. October 2011 21.29.45 Andrew Sullivan wrote:
 I see you found it, but note that it's _not_ a spurious UTF-8
 character: it's a right-to-left mark, ans is a perfectly ok UTF-8 code
 point.

Andrew,
thank you for your reply. Yes I know that this is a perfectly legal UTF-8 
character. It crept into my database as a result of a copy-and-paste job from 
a web site. The point is that it doesn't have a counterpart in ISO-8859-1 to 
which I regularly have to export the data.

The offending character came from this URL: 
http://www.soge.kviteseid.no/individual.php?pid=I2914ged=Kviteseid.GEDtab=0

and the text that I copied and pasted from the page looks like this in the 
source code:

Aslaug Steinarsdotter Fjågesundnbsp;nbsp;lrm;(I2914)lrm;

I'm going to write to the webmaster of the site and ask why that character, 
represented in the HTML as the lrm; entity, has to appear in a Norwegian web 
site which never should have to display text in anything but left-to-right 
order.
 
 If you need a subset of the UTF-8 character set, you want to make sure
 you have some sort of constraint in your application or your database
 that prevents insertion of anything at all in UTF-8.  This is a need
 people often forget when working in an internationalized setting,
 because there's a lot of crap that comes from the client side in a
 UTF-8 setting that might not come in other settings (like LATIN1).

I don't want any constraint of that sort. I'm perfectly happy with UTF-8. And 
now that I've found out how to spot problematic characters that will crash my 
export script, it's really not an issue anymore. The character didn't print 
neither in psql nor in my PHP frontend, so I just removed the problematic text 
and re-entered it by hand. Problem solved.

But thank you for the idea, I think that I will strip out at least any lrm; 
entities from text entered into the database.

By the way, is there a setting in psql that will output unprintable characters 
as question marks or something?

regards, Leif.

-- 
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] max_stack_depth error, need suggestion

2011-08-18 Thread Leif Biberg Kristensen
On Thursday 18. August 2011 12.39.31 AI Rumman wrote:
 I am using Postgresql 9.0.1 in Centos 5.
 
 Yesterday, I got the error inlog:
 
 2011-08-16 10:57:34 EDT [3868]: [1-1] user=vcrm,db=vtigercrm504 ERROR:
 stack depth limit exceeded
 2011-08-16 10:57:34 EDT [3868]: [2-1] user=vcrm,db=vtigercrm504 HINT:
 Increase the configuration parameter max_stack_depth, after ensuring the
 platform's stack depth limit is adequate.
 
 
 I found that I need to increase max_stack_depth. But doc says that it is a
 bit risky increasing it.
 
 Could any one please suggest me what the maximum safe value I may set in my
 environment?
 
 My Server RAM is 32 GB.

That error message is usually caused by an infinite recursion.

regards, Leif

-- 
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] Using Postgresql as application server

2011-08-15 Thread Leif Biberg Kristensen
On Monday 15. August 2011 16.36.23 Merlin Moncure wrote:
 Postgres is not just a database -- it's a language hosting platform if
 you want to use it as such.  Now, you can continue to do things as
 you've always done (database 'here', code 'here', web server 'here'),
 but why discourage people from trying out different things?

Somebody's probably going to do it -- for no other reason why than because you 
can.

Sometimes I'll write functions like

CREATE OR REPLACE FUNCTION dpp(INTEGER) RETURNS SETOF TEXT AS $$
SELECT 'p class=packed' || ss_link_expand(source_text) || '/p'
FROM sources
WHERE parent_id=$1
ORDER BY sort_order
$$ LANGUAGE SQL STABLE;

for dumping thext that I'll copy and paste right into a static Web page. It's 
a lot easier to do this in psql than a lot of other methods that I can think 
of.

BTW, the mentioned ss_link_expand() function will generate hyperlinks on the 
fly from a compact format stored in the database. The concept is explained 
here: http://solumslekt.org/blog/?p=151

I'm working with Postgres and PHP in tandem, and frequently write functions in 
sql or pl/pgsql that will output text directly in HTML format, mostly because 
I've found text transformation (particularly regexp_replace) in Postgres to be 
far superior to the equivalent methods of doing it in PHP.

Leif

-- 
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] announcements regarding tools

2011-07-19 Thread Leif Biberg Kristensen
On Tuesday 19. July 2011 18.44.46 Scott Ribe wrote:
 I'm not sure to whom this specifically should be addressed, but something
 that's been bugging me for a while: announcements like this morning's
 AnySQL Maestro 11.7 released, where the announcement mentions nothing
 about platform support. And it's not just the lack of that info in
 announcements; on many web sites you have to dig for a while to find info
 about platform support, and I do not enjoy finding what looks like a nice
 tool, only to have to spend 5-10 minutes to figure out that it is Windows
 only.
 
 My suggestion: all such announcements should include information about
 supported platforms. Any announcement submitted without that info should
 be rejected, and the vendor instructed to add it before re-submission.

I totally agree. In particular, Windows users seems to think that everybody 
else is using their platform, much as right-handers seem to unconsciously deny 
the existence of left-handers.

The corollary being that if the announcement doesn't explicitly say otherwise, 
you can assume with about 99% confidence that the touted product is Windoze 
only.

regards, Leif

-- 
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] An amusing MySQL weakness--not!

2011-06-27 Thread Leif Biberg Kristensen
On Sunday 26. June 2011 16.11.06 Vincent Veyron wrote:
 Le dimanche 26 juin 2011 à 00:05 -0700, Darren Duncan a écrit :
  Michael Nolan wrote:
  
  Having real BOOLEAN is just one of the reasons I like Postgres the most.
 
 Would you mind giving an example of where a boolean field would be a win
 over an integer one?
 
 I'm asking this because I frequently wonder what is best for my use; I
 normally query postgres via Perl modules, which don't care about boolean
 (the driver converts t/f to 0/1), but I like to tune my fields properly.

PHP has its own Boolean values TRUE/FALSE, but reads Postgresql Booleans as 
't'/'f'. You always have to rely on kludgy konstructs like

if ($pg_bool == 't') then
$my_bool = TRUE;
elseif ($pg_bool == 'f') then
$my_bool = FALSE;
else
$my_bool = NULL;

It's of course much easier to use integer values, but it sucks not to use a 
bool when you want a variable that can't be anything but TRUE, FALSE or NULL.

It obfuscates the code to use a var that evaluates to an integer, but in 
reality is a Boolean in disguise.

regards, Leif

-- 
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] Oracle / PostgreSQL comparison...

2011-06-24 Thread Leif Biberg Kristensen
On Friday 24. June 2011 03.14.39 Rodrigo E. De León Plicet wrote:
 Here:
 
 http://cglendenningoracle.blogspot.com/2011/06/oracle-vs-postgres-postgresq
 l.html
 
 Any comments?

I think he got a point in «Oracle as the second largest software company in 
the world» which is a killer argument from the PHB point of view. They're big 
because they're big.

regards, Leif

-- 
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] Oracle / PostgreSQL comparison...

2011-06-24 Thread Leif Biberg Kristensen
On Friday 24. June 2011 06.01.31 Greg Smith wrote:

 The idea that PostgreSQL is reverse engineered from Oracle is
 ridiculous.

Maybe he believes that SQL was invented by Oracle?

regards, Leif

-- 
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] what is the best way of storing text+image documents in postgresql

2011-06-10 Thread Leif Biberg Kristensen
On Friday 10. June 2011 09.04.18 Arash pajoohande wrote:

 Actually, my word files consists of many exam questions. for each user, a
 random subset of questions must selected and displayed.
 it seem's that it would be nice if I have each question as an easy to
 handle document section (e.g. html div) and display them without need of
 any other application.

That is a very trivial task in eg. PHP. Like John and Craig has said, just 
store the questions as plain text in the database. (Watch out for special MS 
characters.) Let the PHP script pick a random subset of predefined size and 
display it in the browser. Click on File - Print.

Sounds like less than a day's work to me.

regards, Leif

-- 
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] Passing parameters into an in-line psql invocation

2011-06-02 Thread Leif Biberg Kristensen
On Thursday 2. June 2011 18.58.23 Gauthier, Dave wrote:
 Hi:
 
 I'd like to pass a parameter into an inline psql call that itself calls an
 sql script, something like...
 
 psql mydb -c \i thesqlscript foo
 
 Wherefoo is the value I want to pass in.
 
 Just as good would be the ability to sniff out an environment variable from
 within the sql script (thesqlscript in the example above).  In perl, I
 would use $ENV{VARNAME}.  Is there something like that in Postgres SQL?
 
 V8.3.4 on Linux (upgrading to v9 very soon).
 
 Thanks for any ideas !

Personally I prefer to write a small wrapper in Perl for interaction with 
Postgres from the command line. Here's a boilerplate:

#! /usr/bin/perl

use strict;
use DBI;

my $val = shift;
if ((!$val) || !($val =~ /^\d+$/)) {
print Bad or missing parameter $val\n;
exit;
}
my $dbh = DBI-connect(dbi:Pg:dbname=mydb, '', '',
{AutoCommit = 1}) or die $DBI::errstr;
my $sth = $dbh-prepare(SELECT foo(?));
while (my $text = STDIN) {
chomp($text);
$sth-execute($val);
my $retval = $sth-fetch()-[0];
if ($retval  0) {
$retval = abs($retval);
print Duplicate of $retval, not added.\n;
}
else {
print $retval added.\n;
}
}
$sth-finish;
$dbh-disconnect;

-- 
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] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

2011-05-30 Thread Leif Jensen
   Hello Bosco,

   Thank you for your comment. Yes, it would be nice to get some more comments 
on the allocate/deallocate on a connection issue.

   I have verified that in my case deallocating a prepared statement, it 
guesses the wrong connection and returns an error. (The right one is doing 
auto-deallocation at disconnect time, though).

   However, I just noticed that allocating a descriptor with the AT 
connection clause, 
EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname;
generates an ECPGallocate_desc() call without any connection name and that this 
can screw up the ECPGget_desc() function when guessing a connection. I could 
of course use:
EXEC SQL SET CONNECTION connection name;
before the allocate, but that would need mutex's all over to make sure that 
other threads will not set the connection too.

   Any idea why the ecpg pre-compiler doesn't use the named connection for the 
ALLOCATE DESCRIPTOR statement even though it allows it ?

   Please help,

 Leif


- Bosco Rama postg...@boscorama.com wrote:

 Leif Jensen wrote:
  
  Is it really not possible to use 2 separate connection within 1
 thread
  at the same time ? or is it an error in the ecpg library ?
 
 It should be entirely possible to run multiple connections in a
 single
 thread as long as you manage the 'AT connName' clauses properly.
 
 Though, IIRC, using an 'AT connName' clause on any sort of
 'deallocate'
 statement generates an error in ecpg:
 
   ecpg -o test.c test.pgc
   test.pgc:35: ERROR: AT option not allowed in DEALLOCATE statement
 
 This happens when trying to deallocate a query or a prepared
 statement.
 I don't use descriptors but the error message indicates it's _any_
 sort
 of deallocate.
 
 So, it would appear that you can allocate on a connection but not
 deallocate from one.  :-(
 
 I'm wonder if Tom or Michael can shine some light on this one?
 
 Bosco.

-- 
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] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

2011-05-30 Thread Leif Jensen
PS.: That goes for the AT clause on the GET DESCRIPTOR statement too. The 
connection name is not included in the ECPGget_desc() call.


- Leif Jensen l...@crysberg.dk wrote:

 Hello Bosco,
 
Thank you for your comment. Yes, it would be nice to get some more
 comments on the allocate/deallocate on a connection issue.
 
I have verified that in my case deallocating a prepared statement,
 it guesses the wrong connection and returns an error. (The right one
 is doing auto-deallocation at disconnect time, though).
 
However, I just noticed that allocating a descriptor with the AT
 connection clause,
 EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname;
 generates an ECPGallocate_desc() call without any connection name and
 that this can screw up the ECPGget_desc() function when guessing a
 connection. I could of course use:
 EXEC SQL SET CONNECTION connection name;
 before the allocate, but that would need mutex's all over to make sure
 that other threads will not set the connection too.
 
Any idea why the ecpg pre-compiler doesn't use the named connection
 for the ALLOCATE DESCRIPTOR statement even though it allows it ?
 
Please help,
 
  Leif
 
 
 - Bosco Rama postg...@boscorama.com wrote:
 
  Leif Jensen wrote:
  
   Is it really not possible to use 2 separate connection within 1
  thread
   at the same time ? or is it an error in the ecpg library ?
 
  It should be entirely possible to run multiple connections in a
  single
  thread as long as you manage the 'AT connName' clauses properly.
 
  Though, IIRC, using an 'AT connName' clause on any sort of
  'deallocate'
  statement generates an error in ecpg:
 
ecpg -o test.c test.pgc
test.pgc:35: ERROR: AT option not allowed in DEALLOCATE statement
 
  This happens when trying to deallocate a query or a prepared
  statement.
  I don't use descriptors but the error message indicates it's _any_
  sort
  of deallocate.
 
  So, it would appear that you can allocate on a connection but not
  deallocate from one.  :-(
 
  I'm wonder if Tom or Michael can shine some light on this one?
 
  Bosco.

-- 
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] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

2011-05-25 Thread Leif Jensen
   Hello,

   Thank you for the suggestion, seems the way to go. I have implemented this 
using both variable descriptor and prepared statement (execquery) in my program 
and it works nicely, except in one specific situation.

   What I didn't mention previously is that we are sometimes using 2 
connections in the same thread: 1 for reading some tables (doing SELECT), and 1 
for writing other tables (doing INSERTs/UPDATEs) for each record from the 
first, after some complex operations on the data.

   In this case when I deallocate the execquery (and descriptor) I get an error 
from the ecpg lib saying: -230:26000 invalid statement name

   Debugging into the ecpglib, I see that when 'get_connection()' is called 
(from ECPGdeallocate()) with NULL as parameter, it returns the wrong connection 
and then uses this and the query name in a call to 'find_prepared_statement()' 
which of course doesn't find any because of the mismatch of name and 
connection, hence the error message.

   Is it really not possible to use 2 separate connection within 1 thread at 
the same time ? or is it an error in the ecpg library ?

   Please help,

 Leif


- Bosco Rama postg...@boscorama.com wrote:

 Leif Jensen wrote:
  
  This seems to be working most of the time, but looking at the
 generated C
  code from the ecpg compiler and the associated library functions, we
 are
  not sure whether we should put mutex locks around the 'select' part
 to
  avoid several threads are using the same execdesc at the same
 time.
  
  We have made sure that each thread uses their own and only their
 own
  database connection, but are unsure whether the ecpg library
 functions is
  able to handle multiple use of the statical name execdesc ?
 
 You are most probably trashing memory by using the same descriptor
 name in
 multiple threads.  However, given that you have already spent the
 effort to
 have the connections 'thread-dedicated' I think that rather than
 creating a
 critical path through an area that is intentionally supposed to be
 mutli-
 hreaded, I'd be inclined to use the connection name (or some
 derivation of
 it) as the name of the descriptor.  I haven't used descriptors in ecpg
 so I
 don't know if the syntax works, but you could try:
 
 exec sql char *dname = _thisDbConn;  // Or some derivation
 
 EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :dname;
 ...
 EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR
 :dname;
 ...
 EXEC SQL DEALLOCATE DESCRIPTOR :dname;
 
 
 Just a thought.
 
 Bosco.

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


[GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

2011-05-23 Thread Leif Jensen
Hello Guys,

In a multi-threaded server program using Postgresql 8.3.5 with ECPG 
interface for C, we have problems using descriptors (and possibly cursors).

We have created a common database interface module with basically 1 
function: SQLExec(). In the 'select' part of this function we (statically) 
allocate a descriptor as shown below.

This seems to be working most of the time, but looking at the generated C 
code from the ecpg compiler and the associated library functions, we are not 
sure whether we should put mutex locks around the 'select' part to avoid 
several threads are using the same execdesc at the same time.

We have made sure that each thread uses their own and only their own 
database connection, but are unsure whether the ecpg library functions is able 
to handle multiple use of the statical name execdesc ?


static int SQLExec( const char *thisDbConn, char *paramStmt )
{
  EXEC SQL BEGIN DECLARE SECTION;
  const char *_thisDbConn = thisDbConn;
  char *stmt = paramStmt;
  EXEC SQL END DECLARE SECTION;

 .
 .
  if( select ) {
 .
 .
EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR execdesc; line = __LINE__;

  EXEC SQL AT :_thisDbConn PREPARE execquery FROM :stmt; line = __LINE__;

  EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = 
__LINE__;

  EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;

while( ok ) {

  EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR execdesc;
   .
   (handle data per row, using execdesc)
   .
}
.
(deallocation of stuff)
.
  }
}

   We experience spurious crashes with SIGSEGV and tracebacks of the core dump 
usually ends within some ecpg library function, hence this question.

   Please help,

 Leif

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


[GENERAL] ECPG selecting into char arrays

2011-05-19 Thread Leif Jensen
   Hi guys,

   In version 8.2 of the ECPG documentation, section 31.6.3. Different types 
of host variables has been added and includes the following (new) note:

'Note that you have to take care of the length for yourself. If you use this 
host variable as the target variable of a query which returns a string with 
more than 49 characters, a buffer overflow occurs.'

This raises a big question about how to handle fields of type 'text' !?

I am pretty sure that in pre-8.0 versions of PostgreSQL ECPG, the arrays 
were handled in a way that made sure not to write more than the space allowed 
(why else have the array size and type size included as parameters in the ECPG 
generated output call to ECPGdo(...) ?). Is this a new feature ?

With that note in mind, how do I select from a table field of type 'text' ?

 Leif

-- 
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] wnat ot edit pg_hba.conf file from command prompt

2011-05-02 Thread Leif Biberg Kristensen
On Monday 02 May 2011 16:28:48 Sim Zacks wrote:

 sed for windows - http://gnuwin32.sourceforge.net/packages/sed.htm

Whatever happened to edlin?

regards, Leif

-- 
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] 10 missing features

2011-04-26 Thread Leif Biberg Kristensen
On Monday 25 April 2011 10:41:36 Linos wrote:
 Hi all,
   only want to link this blog post
 http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features ,
 i think he may have any good points.
 
 
 Miguel Angel.

Maybe the best point is the one between the lines: That PostgreSQL is being 
compared to Oracle on a feature-by-feature basis. I don't know if his views 
are representative for Oracle DBAs, but it's an indication of PostgreSQL being 
considered in the same league as Oracle.

The «features» perceived to be missing from PostgreSQL by the Oracle DBA is 
the icing on the cake. The cake itself (in terms of data integrity, 
performance, scalability) is obviously regarded as totally edible.

regards, Leif

-- 
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] Using column aliasses in the same query

2011-04-17 Thread Leif Biberg Kristensen
On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote:
 Hi,
 
 I am not sure if there ever was a feature request for using defined column
 aliases in the rest of a query. This would make queries with a lot of
 logic in those aliased columns a lot smaller and this easier to
 write/debug.
 
 I already know you can use the following syntax:
 
 SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and
 lots of logic here) as col2, col3 FROM table) s WHERE col2  aValue
 
 But when you need to use (calculated) values from the actual record and or
 have sub-selects in your main select that also need to use these values
 things get really hairy. I don't know if the SQL specification allows it
 but I know that RDBMS's like Sybase already support this.
 
 Any thoughts?

It's easy to define a view or an SQL function and stash the hairy logic there.

regards, Leif

-- 
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] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..

2011-04-04 Thread Leif Biberg Kristensen
On Monday 04 April 2011 21:07:38 Martin Gainty wrote:
 ..horribly documented, inefficient, user-hostile, impossible to maintain
 interpreted language.. to whom might you be alluding to
 ???

Probably something starting with P.

-- 
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] ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..

2011-04-04 Thread Leif Biberg Kristensen
On Monday 04 April 2011 21:20:51 John R Pierce wrote:
 On 04/04/11 12:07 PM, Martin Gainty wrote:
  ..horribly documented, inefficient, user-hostile, impossible to
  maintain interpreted language..
  to whom might you be alluding to
 
 I only used a few of those adjectives, and prefixed them by
 hypothetical.   to be honest, I would expect most languages commonly
 used in web service environments to be more efficient at string
 processing than pl/pgsql, and I really can't think of a counterexample
 off the top of my head.

I had to move a piece of regexp/replace logic from PHP into pl/pgsql because 
PHP couldn't handle more than abt. 50 replacements in one text unit, instead 
it just dumped the text in the bit bucket. It was probably a memory allocation 
problem. On the other hand pl/pgsql has had no problem with the logic.

Documentation here:

http://solumslekt.org/blog/?p=23

regards, Leif

-- 
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] UUID column as pimrary key?

2011-01-05 Thread Leif Biberg Kristensen
On Wednesday 5. January 2011 16.05.29 Bill Moran wrote:

 Beyond that, the namespace size for a UUID is so incomprehensibly huge
 that the chance of two randomly generated UUIDs having the same value
 is incomprehensibly unlikely ... it is, however, not a 100% guarantee.
 
I can't help thinking of the «Birthday Paradox»:

http://en.wikipedia.org/wiki/Birthday_problem

regards, Leif

-- 
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] Restore problem

2010-12-29 Thread Leif Biberg Kristensen
On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:

 Learning Vim is probably time well-spent, but until you do it's 
probably not that good a tool for fixing your problem.
 
 Although Vim is indeed a very powerful editor, it's not particularly 
easy to use. Unlike your usual editors like Notepad and friends, it's a 
command-based editor, meaning you have to execute a command before you 
can input or change data. It's an entirely different paradigm than what 
you're probably used to (I may assume wrongly here).

Back when I used Windows, my favorite editor was EditPlus 
(http://www.editplus.com/). It isn't free, but well worth the 35 bucks.

As a rather casual coder, I'm very satisfied with the simple editor 
Kwrite in KDE. It's a sheer delight compared to Notepad.

regards, Leif

-- 
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] Root user commands

2010-12-22 Thread Leif Biberg Kristensen
On Wednesday 22. December 2010 20.03.23 Bob Pawley wrote:
 Hi
 
 I am attempting to see if my Postgresql installation is running.
 
 I’ve found this -
 [root user only] ./database_service.pl status
 
 I don’t understand what is meant by root user.
 
 I also don’t know how “./database_service.pl status” is used.
 
 Bob

You don't tell where you found this information.

If you don't know what a root user is, you're probably on Windows. On 
*nix systems, root is the privileged user who can do all the things a 
normal user isn't allowed to do, like modifying system files.

The 'database_service.pl' is a Perl script. Perl is a fairly default 
installation on *nix systems, there also exist builds for Windows. I 
used the Komodo flavor way back when.

regards,
Leif

-- 
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] Copy From suggestion

2010-12-20 Thread Leif Biberg Kristensen
On Monday 20. December 2010 15.24.58 Jorge Godoy wrote:
 With OpenOffice.org that 65K limit goes away as well...
 
 I don't know why it is still like that today for MS Office...  It is 
almost
 2011 and they still think 64K is enough? :-)

Maybe there's an uncrippled «Professional» or «Enterprise» version 
costing an arm and a leg? ;)

regards,
Leif B. Kristensen

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


[GENERAL] Help on explain analyze

2010-11-26 Thread Leif Jensen
)
   Index Cond: (d.id = 
ti.ctrlid)
 -  Hash  (cost=202.61..202.61 rows=183 
width=8) (actual time=3.534..3.534 rows=343 loops=1)
   -  Seq Scan on ctrl_definitions cd  
(cost=0.00..202.61 rows=183 width=8) (actual time=0.034..3.298 rows=343 loops=1)
 Filter: ((name)::text = 
'IrrPeriodStart'::text)
 -  Index Scan using devtype_pkey on devtype dt  
(cost=0.00..0.30 rows=1 width=11) (actual time=0.053..0.055 rows=1 loops=10)
   Index Cond: (dt.id = d.devtypeid)
   -  Index Scan using pk_task_type on task_type tt  
(cost=0.00..0.30 rows=1 width=16) (actual time=0.036..0.039 rows=1 loops=10)
 Index Cond: (tt.id = t.tasktypeid)
 Filter: ((tt.handler = 'modthcswi.so'::text) OR 
(tt.handler = 'modthcswb.so'::text))
 -  Hash Join  (cost=55.33..4050.56 rows=211 width=18) (actual 
time=3.000..66.988 rows=1 loops=10)
   Hash Cond: ((z.zip)::text = btrim(cd2.string))
   -  Seq Scan on zip z  (cost=0.00..3729.23 rows=42223 width=20) 
(actual time=0.017..43.637 rows=42108 loops=10)
   -  Hash  (cost=55.31..55.31 rows=1 width=10) (actual 
time=0.138..0.138 rows=1 loops=1)
 -  Bitmap Heap Scan on ctrl_definitions cd2  
(cost=4.39..55.31 rows=1 width=10) (actual time=0.089..0.128 rows=1 loops=1)
   Recheck Cond: (ctrlid = 401)
   Filter: ((name)::text = 'ZIP'::text)
   -  Bitmap Index Scan on ctrl_def_ctrlid  
(cost=0.00..4.39 rows=19 width=0) (actual time=0.055..0.055 rows=25 loops=1)
 Index Cond: (ctrlid = 401)
   -  Index Scan using county_state_fips_inx on county cy  (cost=0.00..0.31 
rows=1 width=25) (actual time=0.083..0.146 rows=1 loops=10)
 Index Cond: ((cy.state = z.state) AND (cy.countyfips = z.countyfips))
 Filter: (date_part('year'::text, now()) = date_part('year'::text, 
cy.dl_start))
 Total runtime: 40073.738 ms
(41 rows)

  I have concentrate my effort on the (double) 'Seq Scan':

   -  Hash Join  (cost=16.65..282.84 
rows=429 width=38) (actual time=0.078..6.587 rows=429 loops=10)
 Hash Cond: (t.id = ti.taskid)
 -  Seq Scan on task t  
(cost=0.00..260.29 rows=429 width=30) (actual time=0.022..5.089 rows=429 
loops=10)
 -  Hash  (cost=11.29..11.29 
rows=429 width=12) (actual time=0.514..0.514 rows=429 loops=1)
   -  Seq Scan on task_info ti 
 (cost=0.00..11.29 rows=429 width=12) (actual time=0.020..0.302 rows=429 
loops=1)
 
   and tried various indexes on the two table 'task' and 'task_info' to help 
avoid the sequential scans, but at best it gives the same result, at worst it 
was 25% slower :-(. The tables aren't that big. I think the largest are the 
'zip' and the 'county' tables with ~42000 and ~22000 rows, respectively.

   What is it that I'm missing,

 Leif

-- 
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] Adding data from mysql to postgresql periodically

2010-11-14 Thread Leif Biberg Kristensen
On Sunday 14. November 2010 13.44.53 franrtorres77 wrote:
 
 Hi there
 
 I need to add periodically some data from a remote mysql database into our
 postgresql database. So, does anyone know how to do it having in mind that
 it must be runned every minute or so for adding new records to the
 postresql?

It should be trivial to write a Perl script that pulls the data from MySQL,
inserts them into PostgreSQL, and then goes to sleep for 60 seconds.

regards,
Leif B. Kristensen

-- 
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] Adding data from mysql to postgresql periodically

2010-11-14 Thread Leif Biberg Kristensen
On Sunday 14. November 2010 14.33.39 franrtorres77 wrote:
 
 well, I know how to query to mysql but what i dont know is how to then 
write
 that data on the fly to the postgresql

The DBD::Pg package has an excellent documentation: 
http://search.cpan.org/dist/DBD-Pg/Pg.pm

regards,
Leif B. Kristensen

-- 
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] Adding data from mysql to postgresql periodically

2010-11-14 Thread Leif Biberg Kristensen
On Sunday 14. November 2010 14.33.39 franrtorres77 wrote:
 
 well, I know how to query to mysql but what i dont know is how to then 
write
 that data on the fly to the postgresql

I'd also like to say that it's an interesting question, and a lot of 
people (including me) might want to take a stab at the solution.

If you can tell what the data looks like coming from MySQL, and the 
corresponding table structure in PostgreSQL, you may well get a much 
more detailed reply.

regards,
Leif B. Kristensen

-- 
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] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Leif Biberg Kristensen
On Monday 8. November 2010 20.06.13 Jason Long wrote:
 I currently have Postgres 9.0 install after an upgrade.  My database is
 relatively small, but complex.  The dump is about 90MB.
 
 Every night when there is no activity I do a full vacuum, a reindex, and
 then dump a nightly backup.
 
 Is this optimal with regards to performance?  autovacuum is set to the
 default.

I've got a database about the same size order (65 MB on disk, 5 MB dump.tgz) 
and I never bother with neither full vacuum nor reindexing. I run the default 
autovacuum, and if the db becomes bloated for some reason, I just do a 
dump/drop/reload cycle. It's done in a few seconds.

regards,
Leif B. Kristensen
http://solumslekt.org/

-- 
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 do you control IMMUTABLE PG PROC results?

2010-11-04 Thread Leif Biberg Kristensen
On Friday 5. November 2010 01.24.14 Carlo Stonebanks wrote:
 We have procs that would benefit from returning IMMUTABLE results. The procs 
 are dependent on external tables that rarely change, but when they DO 
 change, it would be great if we could expire the cache that the procs read 
 from so that the procs are forced to re-evaluate the results.

A function declared as IMMUTABLE can't, by definition, do database lookups. 
Then it has to be declared as STABLE.

http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html

«An IMMUTABLE function cannot modify the database and is guaranteed to return 
the same results given the same arguments forever.»

regards,
Leif B. Kristensen

-- 
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] 8.4 Data Not Compatible with 9.0.1 Upgrade?

2010-11-02 Thread Leif Biberg Kristensen
On Tuesday 2. November 2010 22.37.33 Carlos Mennens wrote:
 On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark scl...@netwolves.com wrote:
  mv /var/lib/postgres/data  /var/lib/postgres/data.old
 
 Before I move or rename '/var/lib/postgres/data', what version of
 PostgreSQL should I be at? 8.4 or 9.0?

You should be at 9.0. The important thing is that you must start the new 
installation initdb with an empty  data directory, or you'll inevitably get 
the complaints about incompatibility with the old files. It's quite logical, 
really, if you think about it.

  You will then have to do an initdb to create the basic 9.x databases.
  You can then use psql or pg_restore depending on how you dumped
  your data to restore your databases.
 
 I simply ran the following command:
 
 /usr/bin/pg_dump finance  finance.sql

That's only the first part of the upgrade process. Then you must prepare an 
empty directory to acommodate your data, do an initdb, and then populate the 
new directory from your dump file.
 
I think that the Gentoo method works great: In the /var/lib/postgres 
directory, we just create a new subirectory named /8.3, /8.4, /9.0, and so on, 
and then a new /data directory under each one. That way, you can safely remove 
the old data when you've confirmed that the new version actually works.

regards,
Leif B. Kristensen

-- 
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] Printing command string passed to EXECUTE command in plpgsql (after argument resolution)

2010-10-28 Thread Leif Biberg Kristensen
On Thursday 28. October 2010 16.25.47 Allan Kamau wrote:
 I am debugging a plpgsql function which contains a long sql query
 consisting of several parameters which is executed using EXECUTE
 command. I would like to output this command string including the
 actual values of the parameters contained within it so I can obtain
 the actual query and run it directly (manually) in psql (or other
 client such as pgAdmin3). Or at least is possible to output the
 command string as is, followed by the parameter resolution details
 such as $1='2', $2='abc' and so on.

If I understand you correctly, you can assign the SQL string to a variable x, 
and then do a RAISE NOTICE 'Query is: %', x

regards,
Leif B. Kristensen

-- 
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] Front End Application (OFF-TOPIC)

2010-09-24 Thread Leif Biberg Kristensen
On Friday 24. September 2010 19.15.39 Carlos Mennens wrote:
 I am using PostgreSQL to hold my entire I.T. inventory for my company.
 I am manually entering the data in via hand through the Linux command
 line and the 'psql' utility which is great for me but other co-workers
 in I.T. have no understanding of how to view the schema and I wanted
 to see if anyone had any recommendations for software or applications
 I could use that would display all my PostgreSQL data to them over the
 web of sorts? Something that lets them view the warranty expiration
 dates I entered in the Dell table. I don't want an administrative tool
 like 'phppgadmin' or anything but more like a utility that users can
 go in and view the database schema and data for reference only.
 
 Sorry if this is not the correct forum to ask this kind of question
 but I figured with everyone on the list using this in so many
 different varieties, I would find something.
 
 Thanks for your support!

With PHP, it's very easy to build a Web interface to a PostgreSQL database. 
Even I have managed that :D

regards,
Leif Biberg Kristensen

-- 
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] Trade Study on Oracle vs. PostgreSQL

2010-09-24 Thread Leif Biberg Kristensen
On Friday 24. September 2010 20.04.26 Lincoln Yeoh wrote:

 Then again, does Tom ever sleep? Maybe he sleeps in the afternoon? Or 
 is that when he does intensive coding?

Once there was a suggestion on this list to give Tom every other week off to 
level the competition with the other RDBMSes.

That being said, the PostgreSQL mail lists is an excellent and totally free 
support channel. Speaking for myself, I've never needed any support outside 
the mail lists. And Tom is of course a great big part of that.

regards,
Leif Biberg Kristensen

-- 
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] Trade Study on Oracle vs. PostgreSQL

2010-09-24 Thread Leif Biberg Kristensen
On Friday 24. September 2010 23.03.21 Joshua D. Drake wrote:
 DUDE! Are you trying to kill the postgresql company's business? :P

Huh? Is there a company? :P

The REALLY great thing about PostgreSQL is the free sharing. Back in 2002 when 
I discovered that all the software that I considered cool was Freenix based, I 
left the world of proprietary software and never looked back.

This is my world.

Thanks to every one of you who makes this possible.

regards,
Leif Biberg Kristensen

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


[GENERAL] ECPG threads

2010-08-23 Thread Leif Jensen
Hello guys,

   PostgreSQL 8.3.5. Compiled and installed on Slackware 10.2, kernel 2.6.15.

   We have a transaction based system that creates a new process every time a 
user connects (just fork()s, no exec()s). Each of these processes has several 
threads. Some database connections are opened before the threads are created 
(this is part of the basic design of the system), but each tread only uses one 
specific connection assigned to it, no cross connection use.

   We are using Embedded SQL in C mainly located in 1 source module, accessed 
from all threads using function calls. Each thread is of course passing its 
allocated connection name in these calls.

   However, we have had some problems of database calls locking up or crashing 
the program. In the investigation of one of these crashes we had to dig deep 
into the source of the ecpg library. In doing this we have been wondering why 
the descriptors are not connection specific when most other db accesses are. It 
seems that if the db connection is not opened within the thread itself, the 
descriptor to be used is randomly chosen among opened connections. Specifically 
we are wondering about the reason for the descriptor name to be statically 
assigned at compile time (fixed text) and not a variable that could then be 
given a connection related name.

   One strange thing about the behaviour of our program is that it seems to 
run ok on some machines but crashes reproducible on others. All these systems 
are having the same hardware (HP based PCs) with exactly the same Linux and 
PostgreSQL installation.


   To try to visualise our problem, I have included some pseudo code of our 
program flow below.

   What we would like to know is the philosophy of not having descriptors being 
connection specific and whether this will change or not.

 Leif




DB access module:
   .
   Db_Open( char *conn )
   {
 EXEC SQL   EXEC SQL BEGIN DECLARE SECTION;
 const char *dbname = _dbname;
 const char *dbuser = _dbuser;
 const char *_thisDbConn = conn;
 EXEC SQL END DECLARE SECTION;

 EXEC SQL CONNECT TO :dbname AS :_thisDbConn USER :dbuser;
 if( sqlca.sqlcode ) {
   return( SQLErr_NotOpen );
 }
 EXEC SQL AT :_thisDbConn SET AUTOCOMMIT TO OFF;
 if( sqlca.sqlcode ) {
   return( SQLErr_NotOpen );
 } 
 return( SQLErr_Ok );
   }

   Db_SQLExec( char *conn, char *stmt )
   {
 .
 EXEC SQL ALLOCATE DESCRIPTOR execdesc;
 .
 .
 EXEC SQL DEALLOCATE DESCRIPTOR execdesc;
 .
   }

Main source module:
   pthread_t thA = 0;
   pthread_t thB = 0;

   main()
   {
 .
 char *connA = connA;
 char *connB = connB;

 Db_Open( connA );
 Db_Open( connB );
 .
 .
 rval = pthread_create( thA, NULL, funcA, NULL );
 .
 rval = pthread_create( thB, NULL, funcB, NULL );
 .
 Db_Close( connA );
 Db_Close( connB );
   }

Various modules:

   funcA( ... )
   {
 .
 // Many calls like:
 Db_SQLExec( connA, some SQL statement );
 .
   }

   funcB( ... )
   {
 .
 // Many calls like:
 Db_SQLExec( connB, some SQL statement );
 .
   }

-- 
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] MySQL versus Postgres

2010-08-12 Thread Leif Biberg Kristensen
On Thursday 12. August 2010 08.29.13 Ma Sivakumar wrote:
 What does a migrating PHP/MySQL user do? If MySQL performs fast just
 out of box (I have not used MySQL), what is different there? Do MySQL
 defaults give better performance? How do they arrive at those
 defaults?
 
 Or is it a completely different system, which can not be adapted in 
PostgreSQL?

For the vast majority of use cases, ie. with db size  100 MB, PostgreSQL 
performs well enough out of the box. I guess that the same goes for MySQL.

For the relatively few high-profile, high-performance installations some manual 
tuning obviously is needed, and I guess that applies whatever the name of 
system is. That is of course one of the major reasons why such installations 
need qualified DBAs. But for the long-tail segment where most of us belong, 
this isn't a problem.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

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


[GENERAL] alter table set tablespace

2010-07-27 Thread Leif Gunnar Erlandsen
I want to move one table from one disk to another.

In order to do this I wanted to create a new tablespace on the new disks and
issue the command alter table tablename set tablespace tablespacename;

The question is, will this in any way affect the database which is in heavily
use?

The table in question is the largest one approximately 90GB.

Thanks

Leif Gunnar Erlandsen


-- 
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] MESSAGE ERROR

2010-07-17 Thread Leif Biberg Kristensen
On Saturday 17. July 2010 15.14.51 Cornelio Royer Climent wrote:
 
 Hi
 
  
 
 I want to create this table, but i can't, 
 
  
 
 Look this error.
 
  
 
  
 
 CREATE TABLE security_info2 (
 
 window character varying(64) NOT NULL
 
 );
 
 ERROR:  syntax error at or near window
 
 LINE 2: window character varying(64) NOT NULL

window is a reserved word in PostgreSQL since version 8.3, I believe.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

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


[GENERAL] Open Source references

2010-07-09 Thread Leif Jensen
   Hej Guys,

   I'm looking for design ideas (and implementation specifics, not just 
tutorials) to a transaction heavy multi-process, multi-threaded system using 
PostgreSQL. Are there some good references to existing open source systems for 
me to see how other people has solved that kind of problems ?

 Leif

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


[GENERAL] Process- or SessionID with ECPG

2010-07-01 Thread Leif Jensen
   Hi guys,

   I have an ECPG based program and I would very much like to get either the 
process id of the backend or the session id for debugging purposes. Is there a 
way to call PQbackendPID() directly from my program (I suppose that will 
require getting hold of the PGconn from within ECPG?) or some other way to 
interrogate the ECPG system ?

   My program is multi-threaded.

 Leif

-- 
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] Cognitive dissonance

2010-06-10 Thread Leif Biberg Kristensen
On Thursday 10. June 2010 17.24.00 Tom Lane wrote:
 Alvaro Herrera alvhe...@commandprompt.com writes:
  Excerpts from Peter Eisentraut's message of jue jun 10 02:50:14 -0400 
2010:
  As I said back then, doing this is straightforward, but we kind of need
  more than one user who asks for it before we make it part of a regular
  service, which comes with maintenance costs.
 
  Hey, count me as another interested person in a single-file plain-text
  doc output format.
 
 Well, there are two separate things here:
 
 * providing a Makefile target to build plain-text output.
 
 * shipping prebuilt plain text docs in standard distributions.
 
 I am for #1, not so much for #2, mainly on the grounds of size.  But
 given #1 it would be possible for packagers to make their own choices
 about whether to include plain-text docs.

Wouldn't it suffice to make it downloadable, like the pdf doc?

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] uppdate from postgersql 8.3.7 to 8.4.4

2010-05-25 Thread Leif Biberg Kristensen
On Tuesday 25. May 2010 12.15.14 Alban Hertroys wrote:
 I know it's totally unrelated, but when did it become popular to send (HTML) 
messages in a very small blue font? I find this rather hard to read and usually 
fall back to the plain text alternative (which is included, thankfully).

At least there's a plain text fallback. Messages in HTML only are totally 
unreadable in a plaintext MUA, and should IMO be bounced from the list.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] Fwd: Google Alert - postgresql 8.4

2010-05-17 Thread Leif Biberg Kristensen
On Monday 17. May 2010 17.13.05 Thom Brown wrote:
 
  Is this announcement premature?  I don't see where to download 8.4.4.
 
  -- Forwarded message --
  From: Google Alerts googlealerts-nore...@google.com
  Date: Mon, May 17, 2010 at 5:18 AM
  Subject: Google Alert - postgresql 8.4
  To: richard.broer...@gmail.com
 
 
  Google News Alert for: postgresql 8.4
 
  PostgreSQL developers fix vulnerabilities
  The H
  PostgreSQL versions 8.0 to 8.4 and 7.4 are affected by the update. 
Installation packages and source code are available online. 8.4.4, 8.3.11, 
8.2.17, ...
 
 Erk... yeah, that does appear to be premature.  The site doesn't
 reflect that announcement.

I'm on Gentoo Linux, and am compiling PostgreSQL 8.4.4 from portage (~x86) as 
I'm writing.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] Resetting serial type after delete from table

2010-05-08 Thread Leif Biberg Kristensen
On Saturday 8. May 2010 10.11.32 John Gage wrote:
 If I delete from table, which table contains a serial type field,  
 and then insert new rows into the table excluding the [serial] column  
 from the list of columns in the INSERT statement, the numbers in the  
 serial column resume where they left  off prior to the delete from   
 table: 639, 640, 641, 642 for example.
 
 This behavior is totally acceptable, but is it possible to have the  
 serial column reset itself to 1 following  delete from table (i.e.  
 following flushing all the rows from the table)?  The only way I can  
 think to do this is by altering the table by dropping the serial  
 column and then altering it again by adding a new serial column before  
 doing the insert.  That is only a couple of more lines of script, so I  
 don't do the work, but is there an easier way?

http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-
SERIAL

http://www.postgresql.org/docs/current/static/functions-sequence.html

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-04 Thread Leif Biberg Kristensen
On Tuesday 4. May 2010 08.20.56 Yeb Havinga wrote:
 Leif Biberg Kristensen wrote:
  Now I only need a list of the built-in functions. There doesn't seem to be 
a 
  pg_get functions()?

 Try psql -E postgres
 (-E shows all queries psql does on the catalog)
 
 then \df

Thanks, Yeb.

I'm starting to get second thoughts about including a full function list; 
there's a myriad of functions of which I guess nobody (except for Tom Lane of 
course) has a full overview.

It's probably better to provide a short list of the most common functions, and 
then let users add to it according to their own domain of usage.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-04 Thread Leif Biberg Kristensen
On Tuesday 4. May 2010 16.31.20 Tom Lane wrote:
 Leif Biberg Kristensen l...@solumslekt.org writes:
  Now I only need a list of the built-in functions.
 
 There's an awful lot of them, many of which aren't really intended to be
 called by users anyway.  Can't you just do if it looks syntactically
 like a function call, assume it is one?

As the FAQ points out, «GeSHi is not a lexical parser, unlike other 
highlighting solutions.» http://qbnz.com/highlighter/faq.php So, that's not 
really feasible the way GeSHi is built. That said, I've already compiled a 
list of most of the functions from the documentation. I only omitted system 
administration functions which may be postponed for a later version. I've 
submitted a preliminary draft of a Postgresql language file, and am waiting for 
a reply.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-04 Thread Leif Biberg Kristensen
On Tuesday 4. May 2010 16.31.20 Tom Lane wrote:
 Leif Biberg Kristensen l...@solumslekt.org writes:
  Now I only need a list of the built-in functions.
 
 There's an awful lot of them, many of which aren't really intended to be
 called by users anyway.  Can't you just do if it looks syntactically
 like a function call, assume it is one?

Another point: I'm uncertain how to define a «user» in this context. Someone 
who wants to highlight plpgsql code snippets is most likely to be some kind of 
developer.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-04 Thread Leif Biberg Kristensen
On Tuesday 4. May 2010 18.05.02 Tom Lane wrote:
 Leif Biberg Kristensen l...@solumslekt.org writes:
  Another point: I'm uncertain how to define a «user» in this context. 
Someone 
  who wants to highlight plpgsql code snippets is most likely to be some 
kind of 
  developer.
 
 Sure, but what I meant was not intended to be called directly from
 SQL.  Lots of those functions underlie operators, for example, and
 you're really expected to use the operator instead.  Stuff like
 btree support functions likewise not really intended to be called
 manually.

Yes, I think we are agreed. See also my earlier reply to Yeb Havinga, where I 
expressed some second thoughts about including a full function list. I think 
that the «public» functions included in the docs are sufficient for most 
purposes. There aren't an awful lot of them; I compiled a list by hand in a 
couple of hours.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

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


[GENERAL] GeSHi module for Postgresql?

2010-05-03 Thread Leif Biberg Kristensen
GeSHi http://qbnz.com/highlighter/images/geshi.png is a system for 
highlighting code. I've just installed the WP-Syntax plugin, which utilizes 
GeSHi, in my WP blog because I like code highlighting and am tired of doing it 
more or less by hand. However, I was disappointed when I didn't find PostgreSQL 
on the list of supported languages.

I wonder if any of you have at least started writing a PostgreSQL module, in 
which case I'll be happy to contribute. From a peek at the plsql module, it 
doesn't look like rocket science. If I have to write one from scratch, I 
wonder if there' s a compiled list somewhere of just the PostgreSQL keywords 
and function names. That would be of great help.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-03 Thread Leif Biberg Kristensen
On Monday 3. May 2010 22.49.21 Leif Biberg Kristensen wrote:
 GeSHi http://qbnz.com/highlighter/images/geshi.png is a system for 

Sorry about that link. It's of course http://qbnz.com/highlighter/index.php.

*blush*

-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-03 Thread Leif Biberg Kristensen
On Monday 3. May 2010 23.05.54 Richard Broersma wrote:
 On Mon, May 3, 2010 at 1:49 PM, Leif Biberg Kristensen
 l...@solumslekt.org wrote:
  I
  wonder if there' s a compiled list somewhere of just the PostgreSQL 
keywords
  and function names. That would be of great help.
 
 These are what I know of:

Richard, thank you.
 
 http://www.postgresql.org/docs/9.0/static/sql-keywords-appendix.html

That list is a table with descriptions etc, but I can extraxt the keywords 
with a little Perl magic.

 http://www.postgresql.org/docs/9.0/static/functions.html

That's not very useful. I see that I can get a list of the functions in public 
with a \df, put can I get a corresponding one for the system functions?

 http://www.postgresql.org/docs/9.0/static/reference.html

That list is probably superfluous, as I suspect those words are already in the 
keywords list.

But thanks anyway. I've also received a private message from a guy with an 
«under work» project, and along with your hints it's a good start.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-03 Thread Leif Biberg Kristensen
On Monday 3. May 2010 23.02.05 Christophe Chauvet wrote:

 hi
 
 i have begin this work, you can find it at
 
 http://svn.postgresqlfr.org/repos/tools/geshi/trunk/

Christophe, thank you very much!

I've started to look at it, and have already taken your postgresql.php for a 
spin. You can see the first test at my blog under the title «Regular expression 
fun in PostgreSQL». I don't want to post the direct link here.

As soon as I've got some real progress, I'll send you an updated file.

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

-- 
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] GeSHi module for Postgresql?

2010-05-03 Thread Leif Biberg Kristensen
On Monday 3. May 2010 23.50.55 Alvaro Herrera wrote:

 Why wouldn't you simply ask Postgres?

Because I didn't know how :D That's why we've got this wonderful list, isn't 
it?
 
 select * from pg_get_keywords();

Cool! Thank you! I really only need the 'word' column. I'll separate the data 
types from the rest of the keywords, though.

Now I only need a list of the built-in functions. There doesn't seem to be a 
pg_get functions()?

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/blog/

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


  1   2   3   >