[GENERAL] Is this possible in a trigger?

[Fernando]

I want to keep a history of changes on a field in a table.  This will be 
the case in multiple tables.


Can I create a trigger that loops the OLD and NEW values and compares 
the values and if they are different creates a change string as follows:


e.g;

FOR EACH field IN NEW
   IF field.value <> OLD.field.name THEN
  changes := changes
   || field.name
   || ' was: '
   || OLD.field.value
   || ' now is: '
   || field.value
   || '\n\r';
   END IF
END FOR;

Your help is really appreciated.

Thank you.

Re: [GENERAL] Is this possible in a trigger?

[Fernando]

Thank you for your answer.  I guess I better create this history in the 
application's data class.


Klint Gore wrote:

Fernando wrote:
I want to keep a history of changes on a field in a table.  This will 
be the case in multiple tables.


Can I create a trigger that loops the OLD and NEW values and compares 
the values and if they are different creates a change string as follows:


e.g;

FOR EACH field IN NEW
IF field.value <> OLD.field.name THEN
   changes := changes
|| field.name
|| ' was: '
|| OLD.field.value
|| ' now is: '
|| field.value
|| '\n\r';
END IF
END FOR;

Your help is really appreciated.
You can't in plpgsql.  It doesn't have the equivalent of a walkable 
fields collection.  Its possible in some other procedure languages 
(I've seen it done in C).


Having said that, you might be able to create new and old temp tables 
and then use the system tables to walk the columns list executing sql 
to check for differences.


something like

  create temp table oldblah as select old.*;
  create temp table newblah as select new.*;
  for arecord in
   select columnname
   from pg_??columns??
   join pg_??tables?? on ??columns??.xxx = ??tables??.yyy
  where tablename = oldblah and pg_table_is_visible
  loop

   execute 'select old.' || arecord.columname || '::text , new. ' 
|| arecord.columname || '::text' ||

   ' from oldblah old, newblah new ' ||
   ' where oldblah.' || arecord.columnname || ' <> 
newblah.' ||arecord.columnnameinto oldval,newval;


  changes := changes || arecord.columnname || ' was ' || oldval || 
' now ' || newval;

  end loop;
  execute 'drop table oldblah';
  execute 'drop table newblah';

performance could be awful though.

klint.

[GENERAL] Conditional on Select List

[Fernando]

Is it possible to do this?

SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;

What I want is to return a boolean, but when I tried SELECT 
COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to 
boolean.


Is there such IF function or do I have to create my own.

Thank you.

Re: [GENERAL] Conditional on Select List

[Fernando]

Thanks this is exactly what I need it.

Fernando

Tom Lane wrote:

Fernando <[EMAIL PROTECTED]> writes:
  

Is it possible to do this?
SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;



SELECT COUNT(colname) > 0 AS colname FROM table;

If you really like to type, you could use a CASE expression.

regards, tom lane

  

Re: [GENERAL] PG -v- MySQL

[Fernando]

Have you tried Navicat?  The light version is free for the Mac (I think).

Andy Anderson wrote:

On May 13, 2008, at 11:42 AM, Merlin Moncure wrote:

Here are some other things we have v. mysql:

*) Much better shell


I tend to agree based on my limited experience.

However, being a GUI-oriented person I haven't noticed management 
tools comparable to phpMyAdmin (for web) and CocoaMySQL (for Mac). 
Perhaps someone can enlighten me?


(Yes, I've tried pgAdmin, but it's not quite ... right. I can't say 
why at the moment, I should probably take another look at it.)


-- Andy

Re: [GENERAL] Password safe web application with postgre

[Fernando]

You could try to have a function in your application that encrypts the 
connection string and store it in a session variable.  When you need it 
you decrypted from the session variables.  Session variables are stored 
as files on the server, therefore the risk is not as high.


Just a thought.

Fernando.

Bohdan Linda wrote:

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 


My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan 

  

Re: [GENERAL] [PHP] Some undefined function errors

[Fernando]

Are you calling store procedures that return cursors?  I had this 
problem with cursors because the transaction gets committed and the 
cursor closed after they return.  Mind you I had the problem on .NET 
using npgsql, so I might be way off.


Cheers

On 20/05/2010 10:05, Giancarlo Boaron wrote:

Hi all.

Recently, I wrote an email about the problem I was having with some Postgres functions that 
when those functions were called, I received the following error: "Call to undefined 
function".

After some answers, I decided to rebuild a brand new linux virtual machine with 
Apache + PHP + Postgres, but I still get this annoying error messege with some 
functions like pg_prepare() and pg_escape_string().

I compiled Postgres with --without-readline option.
I compiled PHP with --with-apxs2=/usr/local/apache2/bin/apxs and 
--with-pgsql=/usr/local/pgsql/

And the compilation process has no errors.

What am I doing wrong? Do I have to change something in php_config.h file? If 
so, what do I have to change?

Thank you.




   

Re: [GENERAL] [PHP] Some undefined function errors

[Fernando]

Sorry I miss read the question.

It does seem that php is not picking up the pg module and cannot find 
the functions.


I assume Postgresql is in fact installed at /usr/local/pgsql.  If you 
run phpinfo(); can you see that PG is installed?


On 20/05/2010 10:46, Giancarlo Boaron wrote:

**
I make the function call in a php file. I'm not using stored procedures.
If I create an empty php file and put only some of these functions, 
Apache reports de 'call to undefined function XXX'.


--- Em *qui, 20/5/10, Fernando //* escreveu:


    De: Fernando 
Assunto: Re: [GENERAL] [PHP] Some undefined function errors
Para: pgsql-general@postgresql.org
Data: Quinta-feira, 20 de Maio de 2010, 11:25

Are you calling store procedures that return cursors?  I had this
problem with cursors because the transaction gets committed and
the cursor closed after they return.  Mind you I had the problem
on .NET using npgsql, so I might be way off.

Cheers

On 20/05/2010 10:05, Giancarlo Boaron wrote:

Hi all.

Recently, I wrote an email about the problem I was having with some Postgres functions that 
when those functions were called, I received the following error: "Call to undefined 
function".

After some answers, I decided to rebuild a brand new linux virtual machine 
with Apache + PHP + Postgres, but I still get this annoying error messege with 
some functions like pg_prepare() and pg_escape_string().

I compiled Postgres with --without-readline option.
I compiled PHP with --with-apxs2=/usr/local/apache2/bin/apxs and 
--with-pgsql=/usr/local/pgsql/

And the compilation process has no errors.

What am I doing wrong? Do I have to change something in php_config.h file? 
If so, what do I have to change?

Thank you.




   

[GENERAL] dbi-link with Sybase

[fernando]

Hi there,

This may not be the best list for my question, but I'm sure someone there will 
he able to help me
;-)

I'm trying to use dbi-link under RHEL5.3. Using PostgreSQL and Perl rpms from 
distro, installed
Sybase ASE 1.5.5 developers edition and DBD::Sybase using cpan -i.

My test programs, using pubs2 example database, work fine. They connect as:

$dbh = DBI->connect("dbi:Sybase:server=RHEL53I386", "teste", "123testando");

So I have a working DBD::Sybase install. But then I try to initialize dbi-link 
using the statement:

SELECT make_accessor_functions(
'dbi:Sybase:server=RHEL53I386',
'teste',
'123testando',
'---
AutoCommit: 1
RaiseError: 1
',
NULL,
NULL,
NULL,
'pubs2'
);

I get the error (from postgresql logs)

OpenClient message: LAYER = (7) ORIGIN = (2) SEVERITY = (6) NUMBER = (6)
Message String: ct_con_alloc(): unable to get layer message string: unable to ge
t origin message string: error string not available
NOTICE:  ct_con_alloc failed at /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread
-multi/DBD/Sybase.pm line 92.

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"
SQL statement "SELECT dbi_link.create_accessor_methods(
'pubs2',
NULL,
NULL,
'dbi:Sybase:server=RHEL53I386',
'teste',
'123testando',
1
)
"
ERROR:  error from Perl function: error from Perl function: error from Perl func
tion: DBI connect('server=RHEL53I386','teste',...) failed: (no error string) at 
line 137 at line 36. at line 53.

I already tried forcing my database to en_US.UTF-8 and sourced SYBASE.sh from 
postgres bash_profile
script. Before that, the error was aboit missing dynamic libs.

I know this looks more a sybase thing than a postgresql thing but maybe someone 
can understand the
sybase error message and help me. After all I hope someone out there does use 
dbi-link


[]s, Fernando Lozano

-- 
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] Java Postgres drivers.

[fernando]

Dave,

It looks like the postgresql.jar archive is not in your CLASSPATH.


[]s, Fernando Lozano

> I'm not sure this is the right place to enquire...
> 
> I'm trying to connect to a postgres datanbase with Java.
> 
> import java.sql.*;
> 
> public static void main(String[] args) {
> // TODO code application logic here
> try{
> System.out.println("Starting...");
> Class.forName("org.postgresql.Driver");
> String url="jdbc:postgresql:inenergy";
> System.out.println("Got here...");
> 
> catch(Exception e){
> System.out.println("Error..."+e.getMessage());
> 
> }
> }
> 
> Just don't get to the 'Got here...' statement.
> 
> Can anyone see what I'm doing wrong?
> 
> -- 
> 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

[GENERAL] archives insert + Delphi

[FernAndo]

Hi all,

Necessary to inside insert archives (jpg, doc) of the bd using delphi +
DBExpress
How to make this?
Exists an example?

regards, fern











---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] process big

[FernAndo]

my freebsd 4,7 this creating processes big of postgresql.
help me with some information?

PostgreSQL 7.3.1


CPU states:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100%
idle
Mem: 221M Active, 1890M Inact, 272M Wired, 118M Cache, 199M Buf, 9256K Free
Swap: 1024M Total, 276K Used, 1024M Free

  PID USERNAME  PRI NICE  SIZERES STATETIME   WCPUCPU COMMAND
78121 postgres2   0   128M   125M sbwait   2:07  6.40%  6.40% postgres
78415 postgres2   0   128M 46572K sbwait   0:02  1.61%  1.61% postgres
76831 postgres2   0   130M   126M sbwait   0:32  0.00%  0.00% postgres
76833 postgres2   0   130M   126M sbwait   0:24  0.00%  0.00% postgres
78115 postgres2   0   128M   115M sbwait   0:23  0.00%  0.00% postgres
  140 postgres2   0   126M  1652K select   0:19  0.00%  0.00% postgres




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Lock questions

[Fernando]

Hello,
i've been reading the README file in the lmgr folder (in src/backend/storage/lmgr/) 
and i have a couple of questions about locks:
  - What's the difference between the Lightweight Locks (LWLocks) and the Regular 
locks (Heavyweight Locks)? For example, if i do a query and it needs a lock, would  it 
be a lightweight one or a regular one?

  - There are two lock methods, DEFAULT and USER. Where can I get more information 
about them? In the README file it says that "USER locks are non-blocking", how could 
this be?

Thank you very much!



http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español


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

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] Optimizer problem in 8.1.6

[Fernando Schapachnik]

Maybe this is already solved in more advanced releases, but just in 
case.

VIEW active_users:
SELECT * FROM users WHERE active AND ((field IS NULL) OR (NOT field));

Table users has index on text field login.

EXPLAIN SELECT * from active_users where login='xxx';
QUERY PLAN
--
 Index Scan using active_users on users u  (cost=0.00..5.97 rows=1 
width=131)
   Index Cond: ("login" = 'xxx'::text)
   Filter: (active AND ((field1 IS NULL) OR (NOT field1)))

So far, everything OK.

Now, combined (sorry for the convoluted query, it is build
automatically by an app).

EXPLAIN SELECT DISTINCT p.id
FROM partes_tecnicos p,
rel_usr_sector_parte_tecnico r, active_users u
WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND
u.login='xxx' AND r.id_sector=p.id_sector_actual AND 
p.id_cola_por_ambito=1)
OR p.id_cola_por_ambito=1)
AND p.id_situacion!=6;
 
-
 Unique  (cost=1016.84..22057814.97 rows=219 width=4)
   ->  Nested Loop  (cost=1016.84..19607287.64 rows=980210931 width=4)
 ->  Nested Loop  (cost=8.07..2060.25 rows=100959 width=4)
   ->  Index Scan using partes_tecnicos_pkey on 
partes_tecnicos p  (cost=0.00..33.00 rows=219 width=4)
 Filter: ((id_cola_por_ambito = 1) AND 
(id_situacion <> 6))
   ->  Materialize  (cost=8.07..12.68 rows=461 width=0)
 ->  Seq Scan on rel_usr_sector_parte_tecnico r  
(cost=0.00..7.61 rows=461 width=0)
 ->  Materialize  (cost=1008.77..1105.86 rows=9709 width=0)
   ->  Seq Scan on users u  (cost=0.00..999.06 
rows=9709 width=0)
 Filter: (active AND ((field1 IS NULL) OR 
(NOT field1)))

Notice the seq. scan on users.

It is solved using:

EXPLAIN SELECT DISTINCT p.id
FROM partes_tecnicos p, pt.rel_usr_sector_parte_tecnico r,
(SELECT id FROM active_users WHERE
login='xxx') u
WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id
AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1)
OR p.id_cola_por_ambito=1
) AND p.id_situacion!=6;


-
 Unique  (cost=18.65..2323.23 rows=219 width=4)
   ->  Nested Loop  (cost=18.65..2070.83 rows=100959 width=4)
 ->  Index Scan using partes_tecnicos_pkey on partes_tecnicos 
p  (cost=0.00..33.00 rows=219 width=4)
   Filter: ((id_cola_por_ambito = 1) AND (id_situacion <> 
6))
 ->  Materialize  (cost=18.65..23.26 rows=461 width=0)
   ->  Nested Loop  (cost=0.00..18.19 rows=461 width=0)
 ->  Index Scan using active_users on users u  
(cost=0.00..5.97 rows=1 width=0)
   Index Cond: ("login" = 'xxx'::text)
   Filter: (active AND ((field1 IS NULL) 
OR (NOT field1)))
 ->  Seq Scan on rel_usr_sector_parte_tecnico r  
(cost=0.00..7.61 rows=461 width=0)
(10 rows)


Thanks!

Fernando.

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

Re: [GENERAL] Optimizer problem in 8.1.6

[Fernando Schapachnik]

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <[EMAIL PROTECTED]> writes:
> > Now, combined (sorry for the convoluted query, it is build
> > automatically by an app).
> 
> > EXPLAIN SELECT DISTINCT p.id
> > FROM partes_tecnicos p,
> > rel_usr_sector_parte_tecnico r, active_users u
> > WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND
> > u.login='xxx' AND r.id_sector=p.id_sector_actual AND 
> > p.id_cola_por_ambito=1)
> > OR p.id_cola_por_ambito=1)
> > AND p.id_situacion!=6;
> 
> Is this query really what you want to do?  Because the OR overrides all
> the join conditions, meaning that rows having p.id_cola_por_ambito=1
> AND p.id_situacion!=6 must produce Cartesian products against every
> row in each of the other tables.
> 
> I think your SQL-building app is broken.

Yes, yes, we found this while working on improving the query. I just 
wanted to point out that the optimizer was doing a sequential scan 
in a situation it could unfould de active_users definition, add the 
login='xxx' clause, and use the index on the users table.

Thanks.

Fernando.

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

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

Re: [GENERAL] Optimizer problem in 8.1.6

[Fernando Schapachnik]

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <[EMAIL PROTECTED]> writes:
> > Now, combined (sorry for the convoluted query, it is build
> > automatically by an app).
> 
> > EXPLAIN SELECT DISTINCT p.id
> > FROM partes_tecnicos p,
> > rel_usr_sector_parte_tecnico r, active_users u
> > WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND
> > u.login='xxx' AND r.id_sector=p.id_sector_actual AND 
> > p.id_cola_por_ambito=1)
> > OR p.id_cola_por_ambito=1)
> > AND p.id_situacion!=6;
> 
> Is this query really what you want to do?  Because the OR overrides all
> the join conditions, meaning that rows having p.id_cola_por_ambito=1
> AND p.id_situacion!=6 must produce Cartesian products against every
> row in each of the other tables.

A rewritten query still exhibits the same behavior:

VACUUM verbose ANALYZE users;
[...]
INFO:  analyzing "users"
INFO:  "users": scanned 778 of 778 pages, containing 22320 live 
rows and 3 dead rows; 3000 rows in sample, 22320 estimated total rows

EXPLAIN ANALYZE SELECT DISTINCT p.id
FROM partes_tecnicos p
WHERE
p.id IN
(SELECT r.id_parte_tecnico FROM
rel_usr_sector_parte_tecnico r, active_users u
WHERE (r.id_usr=u.id AND u.login='xxx' AND 
r.id_sector=p.id_sector_actual AND
p.id_cola_por_ambito=1)
OR p.id_cola_por_ambito=1)
AND p.id_situacion!=6;

 Unique  (cost=0.00..19045387.60 rows=177 width=4) (actual 
time=0.331..997.593 rows=209 loops=1)
   ->  Index Scan using partes_tecnicos_pkey on partes_tecnicos p  
(cost=0.00..19045387.16 rows=177 width=4) (actual time=0.323..995.797 
rows=209 loops=1)
 Filter: ((id_situacion <> 6) AND (subplan))
 SubPlan
   ->  Result  (cost=8.07..90878.33 rows=4493367 width=4) 
(actual time=0.028..3.250 rows=178 loops=254)
 One-Time Filter: ($0 = 1)
 ->  Nested Loop  (cost=8.07..90878.33 rows=4493367 
width=4) (actual time=0.025..2.393 rows=216 loops=209)
   ->  Seq Scan on users u  (cost=0.00..1002.92 
rows=9747 width=0) (actual time=0.009..0.009 rows=1 loops=209)
 Filter: (active AND ((field1 IS 
NULL) OR (NOT field1)))
   ->  Materialize  (cost=8.07..12.68 rows=461 
width=4) (actual time=0.004..0.800 rows=216 loops=209)
 ->  Seq Scan on 
rel_usr_sector_parte_tecnico r  (cost=0.00..7.61 rows=461 width=4) 
(actual time=0.008..2.128 rows=488 loops=1)
 Total runtime: 998.552 ms
(12 rows)

Notice again the seq scan on users instead of using the index and the 
very off estimate.

Thanks.

Fernando.


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

Re: [GENERAL] Optimizer problem in 8.1.6

[Fernando Schapachnik]

En un mensaje anterior, Michael Glaesemann escribió:
> 
> On Jun 22, 2007, at 10:16 , Fernando Schapachnik wrote:
> 
> >EXPLAIN SELECT DISTINCT p.id
> 
> Can you provide EXPLAIN ANALYZE? I suspect that when you rewrote the  
> query it changed how the planner took into account the statistics. If  
> your statistics are off, perhaps this changes how the planner  
> rewrites the query.

Sure. The DB is VACUUM'ed daily, and the users database only received 
a few updates per day.

This is from the rewrote one:

  
-
 Unique  (cost=18.65..2838.38 rows=268 width=4) (actual 
time=0.265..1503.554 rows=209 loops=1)
   ->  Nested Loop  (cost=18.65..2529.51 rows=123548 width=4) (actual 
time=0.257..1127.666 rows=101992 loops=1)
 ->  Index Scan using partes_tecnicos_pkey on partes_tecnicos 
p  (cost=0.00..39.89 rows=268 width=4) (actual time=0.025..2.115 
rows=209 loops=1)
   Filter: ((id_cola_por_ambito = 1) AND (id_situacion <> 
6))
 ->  Materialize  (cost=18.65..23.26 rows=461 width=0) (actual 
time=0.005..1.817 rows=488 loops=209)
   ->  Nested Loop  (cost=0.00..18.19 rows=461 width=0) 
(actual time=0.209..5.670 rows=488 loops=1)
 ->  Index Scan using active_users on users u  
(cost=0.00..5.97 rows=1 width=0) (actual time=0.141..0.147 rows=1 
loops=1)
   Index Cond: ("login" = 
'xxx'::text)
   Filter: (active AND ((field1 IS NULL) 
OR (NOT field1)))
 ->  Seq Scan on rel_usr_sector_parte_tecnico r  
(cost=0.00..7.61 rows=461 width=0) (actual time=0.053..1.995 rows=488 
loops=1)
 Total runtime: 1504.500 ms
(11 rows)


The original one is taking a *lot* of time (more than an hour by now).

Thanks!

Fernando.

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

   http://archives.postgresql.org/

[GENERAL] Connection idle broken

[Fernando Xavier]

Hi, 

I have trouble with my  java application. Since i change the network 
configuration, the postgresql idle connections broken after 10 minutes. (i set 
authentication_timeout = 600 in postgresql.conf).

My network:

192.168.1.1 (postgresql server and gateway server)
192.168.0.1 (linksys wireless router)
192.168.0.x (clients)

How i make my idle connections alive for long time?

Any idea?
 
Regards,
 
Fernando


  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

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

Res: [GENERAL] Connection idle broken

[Fernando Xavier]

Right.. i'm sorry :-)

i solved this problem according Scott Marlowe suggestion. I changed the 
postgresql.conf

tcp_keepalives_idle = 300 
tcp_keepalives_interval = 60 
tcp_keepalives_count = 10  

The connection don't broke more...Actually, the router linksys isn't good for 
network complex.. 

Thanks for all replies! :-)
 
regards, 
Fernando

- Mensagem original 
De: Douglas McNaught <[EMAIL PROTECTED]>
Para: Fernando Xavier <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Enviadas: Terça-feira, 27 de Novembro de 2007 16:33:35
Assunto: Re: [GENERAL] Connection idle broken

On 11/27/07, Fernando Xavier <[EMAIL PROTECTED]> wrote:
>
>
> Hi, thanks for reply!
>
> No, my router don't have configurations for timeout connections..

Get a better router then. Something between your clients and the
database server is timing out those connections, and it's most likely
that box--NAT connections are timed out fairly aggressively by default
on consumer routers (you didn't say whether you were using NAT or not,
but it may be turned on by default).  Relying on anything labeled
"Linksys" for production work is a terrible idea.

Also, please keep your replies on the mailing list so others can
benefit from the discussion.

-Doug







  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

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

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

Re: [GENERAL] [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

[Fernando Hevia]

> Tom Lane [mailto:[EMAIL PROTECTED] wrote:
> 
> Since the OP has apparently already managed to get updated tzdata files 
> installed on his system, he could just copy them into 
> /usr/share/postgresql/timezone --- anything using zic should be a 
> compatible file format.
>
> The lack-of-ARST-on-input problem can be addressed by mucking with
> /usr/share/postgresql/timezonesets/Default, if you're using 8.2.
> In earlier versions the table is hardwired into datetime.c :-(
> 

Summing up:

After installing the updated tzdata files in the server I had to copy the
America/Argentina/* files to /usr/share/postgresql/timezone in order to get
postgres determine the correct local time.

With 8.2.x the ARST abbreviation was recognized after including the
following line in /usr/share/postgresql/8.2/timezonesets/Default

ARST   -14400 D  # Argentina Summer Time

postgres=# select '01:13:16.426 ARST Wed Jan 2 2008'::timestamp with time
zone;
timestamptz

 2008-01-02 01:13:16.426-02
(1 row)

I wonder if pg_timezone_names plays any role in the ARST issue. It does
contain the right data (appeared after copying tzdata into
/usr/share/postgresql/timezone and restarting server) but ARST wasn't
accepted till previous step was done.

postgres=# select * from pg_timezone_names where abbrev = 'ARST';
  name  | abbrev | utc_offset | is_dst
+++
 localtime  | ARST   | -02:00:00  | t
 America/Argentina/Rio_Gallegos | ARST   | -02:00:00  | t
 America/Argentina/Mendoza  | ARST   | -02:00:00  | t
 America/Argentina/La_Rioja | ARST   | -02:00:00  | t
 America/Argentina/Buenos_Aires | ARST   | -02:00:00  | t
 America/Argentina/Cordoba  | ARST   | -02:00:00  | t
 America/Argentina/Catamarca| ARST   | -02:00:00  | t
 America/Argentina/Ushuaia  | ARST   | -02:00:00  | t
 America/Argentina/Tucuman  | ARST   | -02:00:00  | t
 America/Argentina/Jujuy| ARST   | -02:00:00  | t
 America/Argentina/San_Juan | ARST   | -02:00:00  | t
(11 rows)


Thanks for all contributions.

Regards,
Fernando.




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

Re: [GENERAL] [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

[Fernando Hevia]

> Tom Lane [mailto:[EMAIL PROTECTED] wrote:
> 
> "Fernando Hevia" <[EMAIL PROTECTED]> writes:
> > With 8.2.x the ARST abbreviation was recognized after including the
> > following line in /usr/share/postgresql/8.2/timezonesets/Default
> 
> > ARST   -14400 D  # Argentina Summer Time
> 
> Um ... is that really offsetting in the correct direction?  What I put
> into CVS was
> 
> ARST-7200 D  # Argentina Summer Time
> 
> If that's wrong I need to know ...
> 

No, you are right: -7200 is the correct offset.

Regards,
Fernando.


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

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

[GENERAL] Format Float numbers

[Fernando Xavier]

Hi,

I want format a column in select result:

1.1 => 1.10

Any idea?

Thanks!

Fernando

Abraços,

Fernando


  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

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

[GENERAL] Re: [ANNOUNCE] Re: [pgsql-es-ayuda] Para participantes extranjeros en el Tercer PGDay Latinoamericano.

[Fernando Hevia]

En oportunidades anteriores donde me ha tocado trabajar con empresas cubanas
fue la empresa la que gestionó la visa y me la enviaron escaneada. No tuve
que ir a la embajada en ningún momento.

Saludos,
Fernando.

2011/1/11 ๏̯͡๏ Guido Barosio 

> Yunior,
>
>En Argentina el tramite de visado es personal y se realiza en la
> Embajada de Cuba, incluso tiene un costo fijo. Se realiza
> personalmente y no se aceptan gestores por el tramite. La visa es un
> sello en el pasaporte, previo al viaje.
>
>Como piensan resolver este aspecto? El visado seria por turismo o
> trabajo?
>
>Saludos cordiales,
>
> Guido Barosio
>
> 2011/1/10 Ing. Yunior Mesa Reyes :
> > IMPORTANTE!!!:
> >
> > Para participantes extranjeros en el Tercer PGDay Latinoamericano, a
> > desarrollarse en la Universidad de las Ciencias Informáticas, La Habana,
> en
> > febrero del 2011.
> >
> > Estimados, luego de realizar todas las coordinaciones previas le
> > relacionamos la información referente a su participación en nuestro
> evento:
> >
> > ** El evento estará acargo de la gestión de las visas.
> >
> > ** El pasaje deberá pagarlo el interesado.
> >
> > ** El hospedaje y la alimentación durante los días de desarrollo del
> evento
> > se garantizará por nuestra parte.
> >
> > **Necesitamos antes del miércoles 12 de enero del 2011 los siguientes
> datos
> > para la confección de las visas de trabajo.
> >
> > DATOS A ENVIAR A NUESTROS CORREOS DE CONTACTO
> >
> > ** Nombre completo.
> >
> > ** # de pasaporte y/o cédula de indetidad.
> >
> > ** Nacionalidad y país de residencia.
> >
> > ** Fecha de entrada y salida del país.
> >
> > (ES NECESARIO QUE TODOS LOS INTERESADOS EN PARTICIPAR EN NUESTRO EVENTO
> SE
> > INSCRIBAN EN LA PÁGINA DEL EVENTO COMO PARTICIPANTES)
> >
> > Vínculo directo:  http://postgresql.uci.cu/node/1
> >
> > Saludos,
> > Ing.Yunior Mesa Reyes
> > Postgre-SQL Empresarial. DATEC
> > Universidad de las Ciencias Informáticas.Ciudad de la Habana. Cuba.
> > «"Se tu el cambio que quieres ver en el mundo"..."El éxito es el fracaso
> > superado por la perseverancia"»
> >
>
> ---(end of broadcast)---
> -To unsubscribe from this list, send an email to:
>
>   pgsql-announce-unsubscr...@postgresql.org
>

[GENERAL] Removing Context messages

[Fernando Hevia]

Hi list,
 
I'm having trouble removing some context messages in psql and/or pgadmin.
I made a simple example to show this with 2 functions: f_outer which loops
through a recordset and calls f_inner for each record.
 
Context messages appear only when the f_inner function logs. (Would be nice
to know why these CONTEXT notices appear).
This is the output I'm getting:
 
pg=> select f_outer();
NOTICE:  f_outer: 3
NOTICE:  f_inner: 3 = [HEVIA]
CONTEXT:  SQL statement "SELECT  f_inner( $1 )"
PL/pgSQL function "f_outer" line 9 at perform
NOTICE:  f_outer: 6
NOTICE:  f_inner: 6 = [GUIDARA]
CONTEXT:  SQL statement "SELECT  f_inner( $1 )"
PL/pgSQL function "f_outer" line 9 at perform
NOTICE:  f_outer: 7
NOTICE:  f_inner: 7 = [MASTROIANI]
CONTEXT:  SQL statement "SELECT  f_inner( $1 )"
PL/pgSQL function "f_outer" line 9 at perform
 f_outer
-
 
(1 row)
 
I want to get rid of the CONTEXT messages.
I have tried in psql with "\set VERBOSITY terse" without success. No idea on
what to try on pgadmin though.
 
Thanks,
Fernando

--- Function declaration follows in case it helps ---
CREATE OR REPLACE FUNCTION f_inner(p_client numeric(10)) RETURNS void AS
$BODY$
DECLARE
  r_clients clientes%ROWTYPE;
BEGIN
  SELECT * INTO r_clients FROM clientes WHERE id_cliente = p_client;
  RAISE NOTICE 'f_inner: % = [%]', p_client, r_clients.apellido;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
 
CREATE OR REPLACE FUNCTION f_outer() RETURNS void AS
$BODY$
DECLARE
  r_clients clientes%ROWTYPE;
BEGIN
  FOR r_clients IN SELECT * FROM CLIENTES
  LOOP
RAISE NOTICE 'f_outer: %', r_clients.id_cliente;
PERFORM f_inner(r_clients.id_cliente);
  END LOOP;
END;
$BODY$ 
LANGUAGE 'plpgsql' VOLATILE;
 


-- 
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] Removing Context messages

[Fernando Hevia]

> -Mensaje original-
> De: Tom Lane [mailto:[EMAIL PROTECTED] 
> Enviado el: Lunes, 07 de Abril de 2008 01:37
> Para: Fernando Hevia
> CC: pgsql-general@postgresql.org
> Asunto: Re: [GENERAL] Removing Context messages 
> 
> "Fernando Hevia" <[EMAIL PROTECTED]> writes:
> > I want to get rid of the CONTEXT messages.
> > I have tried in psql with "\set VERBOSITY terse" without success.
> 
> Works for me ...
> 
>   regards, tom lane

Found it. Variables are case sensitive and \set command must *not* be ended
with semi-colon. (I blundered here)
The correct syntax is:

pg=# \set VERBOSITY 'terse'


Thanks.


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

[GENERAL] Is this a bug? (changing sequences in default value)

[Fernando Schapachnik]

Pg 8.1.11, I try to change sequences as default value of a table, then 
remove old sequence:

# \d table1
   Table "table1"
 Column |  Type   |   Modifiers
+-+---
 id | integer | not null default nextval('table1_id_seq'::regclass)
 nombre | text| not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (id)

# ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
ALTER TABLE

# \d table1
   Table "table1"
 Column |  Type   |   Modifiers
+-+---
 id | integer | not null default nextval('newseq_id_seq'::regclass)
 nombre | text| not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (id)

# drop SEQUENCE table1_id_seq ;
ERROR:  cannot drop sequence table1_id_seq because table 
table1 column id requires it
HINT:  You may drop table table1 column id instead.

Am I doing something wrong?

Thanks!

Fernando.

-- 
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] Is this a bug? (changing sequences in default value)

[Fernando Schapachnik]

En un mensaje anterior, Merlin Moncure escribió:
> On Thu, May 8, 2008 at 7:52 AM, Fernando Schapachnik
> <[EMAIL PROTECTED]> wrote:
> > Pg 8.1.11, I try to change sequences as default value of a table, then
> > remove old sequence:
> >
> > # \d table1
> >   Table "table1"
> >  Column |  Type   |   Modifiers
> > +-+---
> >  id | integer | not null default nextval('table1_id_seq'::regclass)
> >  nombre | text| not null
> > Indexes:
> >"table1_pkey" PRIMARY KEY, btree (id)
> >
> > # ALTER TABLE table1 alter column id set default nextval('newseq_id_seq');
> > ALTER TABLE
> >
> > # \d table1
> >   Table "table1"
> >  Column |  Type   |   Modifiers
> > +-+---
> >  id | integer | not null default nextval('newseq_id_seq'::regclass)
> >  nombre | text| not null
> > Indexes:
> >"table1_pkey" PRIMARY KEY, btree (id)
> >
> > # drop SEQUENCE table1_id_seq ;
> > ERROR:  cannot drop sequence table1_id_seq because table
> > table1 column id requires it
> > HINT:  You may drop table table1 column id instead.
> >
> > Am I doing something wrong?
> 
> yes and no  when you created the table initially you probably made it
> a 'serial' column which set up the ownership that prevents the drop
> operation.  that ownership did not go away when you altered the
> default to the new serial.
> 
> to fix this,
> alter sequence sequence table1_id_seq owned by none; -- now you can drop

Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation 
of this syntax I tried gives me error as, apparently, it should:

\h ALTER SEQUENCE
Command:     ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO 
MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

Thanks again!

Fernando.

-- 
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] Is this a bug? (changing sequences in default value)

[Fernando Schapachnik]

En un mensaje anterior, Merlin Moncure escribió:
[...]
> >> > Am I doing something wrong?
> >>
> >> yes and no  when you created the table initially you probably made it
> >> a 'serial' column which set up the ownership that prevents the drop
> >> operation.  that ownership did not go away when you altered the
> >> default to the new serial.
> >>
> >> to fix this,
> >> alter sequence sequence table1_id_seq owned by none; -- now you can drop
> >
> > Hi, Merlin. Thanks for the tip, but it doesn't work. Every variation
> > of this syntax I tried gives me error as, apparently, it should:
> >
> > \h ALTER SEQUENCE
> > Command: ALTER SEQUENCE
> > Description: change the definition of a sequence generator
> > Syntax:
> > ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
> >[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
> > MAXVALUE ]
> >[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
> 
> oop, you are using 8.1 :-).  This was added in a later version.  drop
> sequence ... cascade should probably work.  you can try it out in a
> transaction to be sure.

Thanks for your help, but cascade doesn't make a difference.

Fernando.

-- 
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] Is this a bug? (changing sequences in default value)

[Fernando Schapachnik]

En un mensaje anterior, Merlin Moncure escribió:
> On Tue, May 13, 2008 at 8:50 AM, Fernando Schapachnik
> <[EMAIL PROTECTED]> wrote:
> >  > > ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
> >  > >[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
> >  > > MAXVALUE ]
> >  > >[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
> >  >
> >  > oop, you are using 8.1 :-).  This was added in a later version.  drop
> >  > sequence ... cascade should probably work.  you can try it out in a
> >  > transaction to be sure.
> >
> >  Thanks for your help, but cascade doesn't make a difference.
> 
> What do you mean? PostgreSQL 8.1 has 'drop sequence cascade':
> http://www.postgresql.org/docs/8.1/interactive/sql-dropsequence.html
> 
> If this isn't working, can you paste the text of the error message?

Sorry I wasn't clear. I mean to say that the error message is the same 
with or without cascade:

sso=# drop SEQUENCE table1_id_seq cascade;
ERROR:  cannot drop sequence table1_id_seq because table 
ambitos column id requires it
HINT:  You may drop table table1 column id instead.

Thanks.

Fernando.

-- 
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] syntax error with execute

[Fernando Moreno]

I haven't use the RETURNING clause before, but the "INTO" option, at least
in SELECT sentences, must be outside of the string expression. This way:
EXECUTE 'some query' INTO variable;

Cheers.

2008/5/30 A B <[EMAIL PROTECTED]>:

> I have a query like this in a plpgsql function:
>
> EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
> ('||vals||') RETURNING currval('''||seqname||''') INTO newid'
>
> and I get the response:
>
> ERROR:  syntax error at or near "INTO"
> LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO newid
>
> And I do not understand this error. If I take the INSERT command and
> run it by hand, it works fine, but it doesn't work in the function
> when called by execute. Anybody has an idea on what is wrong and what
> to do about it?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] re-using cluster

[Fernando Dominguez]

Hello,

One of my hd failed recently, so I has to reinstall my  system, but my data
where on other hd that did
not fail.

So I want to use that data , I tried initd -D /storage/pgCluster but I get
a "directory not empty" message, of course I
want to use that cluster.

How could I use that data?

Thanks.

[GENERAL] Bugs revealed by static code analysis

[Fernando Correia]

As a PostgreSQL user, I'd like to bring to the attention of the community
and the developers that Andrey Karpov from PVS-Studio published an article
listing several potential bugs in PostgreSQL. These bugs were revealed by
code analysis with the PVS-Studio tool. He is also offering a license to
the team.

I think the report is worth checking:

Pre New Year Check of PostgreSQL
http://www.viva64.com/en/b/0227/

[GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Hello,

do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat
(or other web servers) can be already found installed or where they can be
installed from scratch? In possible, it would be better if the PostgreSQL be
directly accessible by my servlet, without any web service/PHP script in the
middle.

Thank you very much in advance.

Kind regards.

Fernando Pianegiani

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Hello,

thank you for your answer. Sorry for my cross posting.

Are you in any business about hosting platforms?? ;-)

Don't forget that the Internet is free, that youtube is free and that this
mailing list is free and that also MySQL is free. But, not all the services
that appear free are really for free. Behind them there is for sure a
business.

Fernando


> Extreme cross posting, you may need to elect one mailing list and post
> only to it.
>
> I think you may not get good offering for what you are looking for if
> you are only interested in FREE hosting having all those features
> you've mentioned. The hosting services do have to somehow get
> compensation for their overheads, this would mean either offering a
> FREE service for a limited period and/or requiring that they
> explicitly add as many of their advertisements as possible to your
> resulting web pages, not to mention they will try to place as many
> clients as possible on modest hardware, your ability to manage Tomcat,
> PostgreSQL will also be very limited in such environments, in general
> your clients will not be happy.
>
> Also the FREE services of this kind have by now been exploited by the
> many individuals who provide websites that are aimed at ripping people
> off.
>
> Allan.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


On Sat, Aug 6, 2011 at 10:36 AM, Allan Kamau  wrote:
On Sat, Aug 6, 2011 at 11:02 AM, Fernando Pianegiani
 wrote:
> Hello,
>
> do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat
> (or other web servers) can be already found installed or where they can be
> installed from scratch? In possible, it would be better if the PostgreSQL
be
> directly accessible by my servlet, without any web service/PHP script in
the
> middle.
>
> Thank you very much in advance.
>
> Kind regards.
>
> Fernando Pianegiani
>

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Exuse me, PostgreSQL is completely free, not MySQL.. :-D

On Sat, Aug 6, 2011 at 11:48 AM, Fernando Pianegiani <
fernando.pianegi...@gmail.com> wrote:

> Hello,
>
> thank you for your answer. Sorry for my cross posting.
>
> Are you in any business about hosting platforms?? ;-)
>
> Don't forget that the Internet is free, that youtube is free and that this
> mailing list is free and that also MySQL is free. But, not all the services
> that appear free are really for free. Behind them there is for sure a
> business.
>
> Fernando
>
>
>
>> Extreme cross posting, you may need to elect one mailing list and post
>> only to it.
>>
>> I think you may not get good offering for what you are looking for if
>> you are only interested in FREE hosting having all those features
>> you've mentioned. The hosting services do have to somehow get
>> compensation for their overheads, this would mean either offering a
>> FREE service for a limited period and/or requiring that they
>> explicitly add as many of their advertisements as possible to your
>> resulting web pages, not to mention they will try to place as many
>> clients as possible on modest hardware, your ability to manage Tomcat,
>> PostgreSQL will also be very limited in such environments, in general
>> your clients will not be happy.
>>
>> Also the FREE services of this kind have by now been exploited by the
>> many individuals who provide websites that are aimed at ripping people
>> off.
>>
>> Allan.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> On Sat, Aug 6, 2011 at 10:36 AM, Allan Kamau  wrote:
> On Sat, Aug 6, 2011 at 11:02 AM, Fernando Pianegiani
>  > wrote:
> > Hello,
> >
> > do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat
> > (or other web servers) can be already found installed or where they can
> be
> > installed from scratch? In possible, it would be better if the PostgreSQL
> be
> > directly accessible by my servlet, without any web service/PHP script in
> the
> > middle.
> >
> > Thank you very much in advance.
> >
> > Kind regards.
> >
> > Fernando Pianegiani
> >
>
>

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Thak you Antonio.

After open source for the software, we will wait for open resource for the
hardware (this is just a first example http://www.arduino.cc/, even if of
different nature).

I need to eat too, for this reason I cannot pay for an hosting platform
after that my funded research project ended.

Fernando

On Sat, Aug 6, 2011 at 3:28 PM, Antonio Goméz Soto <
antonio.gomez.s...@gmail.com> wrote:

> Well,
>
> I am from the hosting business, and can assure you, what you are looking
> for does not exist.
> This configuration requires specialists on the provider side, which are
> expensive. They
> need to eat too.
>
> And history teaches, that even if it would exist, you should not put
> anything meaningful on it,
> because they surely will go out of business soon.
>
> Antonio.
>
>
> Op 06-08-11 10:02, Fernando Pianegiani schreef:
>
>  Hello,
>>
>> do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat
>> (or other web servers) can be already found installed or where they can be
>> installed from scratch? In possible, it would be better if the PostgreSQL be
>> directly accessible by my
>> servlet, without any web service/PHP script in the middle.
>>
>> Thank you very much in advance.
>>
>> Kind regards.
>>
>> Fernando Pianegiani
>>
>
>

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Dear David, Nicklas,

I think that this is not the right place where to discuss about this topic,
but I have to try to give you an answer.

PostgreSQL is not only open source, like MySQL is, but also free. This means
that people who don't develop neither a line of code of PostgreSQL (or just
some few lines of it) can use it to do what they want, even money, without
having the obligation to provide back 1 cent to the PostgreSQL community.
That's incredible I know, but it is so. And why? I don't know exactly, but I
know that there are several reasons that are not very "transparent". I can
try to give my interpretation hopping that it is the real one or in any case
thinking that it should be the correct one. If people who develop PostgreSQL
make it free, people who don't develop PostgreSQL but who use it for their
research projects or business can make available the results of their
projects also to the developers of PostgreSQL, but overall to the worldwide
community. And so, in case for example a developer of PostgreSQL or any
other person should have a health problem, he can hope that his hospital
uses the results of a research project got also with the help of PostgreSQL
or of some other free technology.

I think the previous one is the philosophy that should be behind the words
"open source" and "free". In all the other cases there are in my opinion
interests that should be better clarified.

If we do something for free for other people, then they can do something for
other people using for free our results, and so on...the alternatives are
under our eyes, that is the jungle of the market and the worldwide crisis.
So David, don't worry for my activity of research, instead you should be
seriously preoccupied if you do business. Moreover, the fact that my project
ended could be in case an additional problem for both of us and not only if
we should have problems of health strictly inherent to the results of
research coming from a possible development of my old project. Obviously, I
hope no for all of us. :-)

David, Nicklas, if you make money by using PostgreSQL and you want to be
really honest (as you claim honesty from me and from my work activity),
please count that money and give the right percentage of it to the
PostgreSQL community, but considering also who spent more time, resources
like electric power, computer hardware, etc. than the others in developing
it. In this way if you develop PostgreSQL, then you too can get your right
percentage from you and from the other developers.

Dear Scott, Niklas, you are right, the components and the resources
necessary to manufacture the solutions developed within the arduino project
are not for free. Only the schematics, the gerbers, etc. are for free.

Finally, the world is full of companies that make available for free their
hardware/software/human resources. Probably also you use them every day for
free (e.g., the media in general) or you store your data for free in part of
those resources (facebook, youtube, just to do general examples), but those
resources are not really "for free", those companies find the way to gain a
lot of money with the fact that you access to their resources.

In any case, excuse me if I have hurted your's feelings. My intention was
not to ask for a free hosting platform (free stuff, etc.) to the PostgreSQL
community, but I simply asked if the community knows anybody who provides a
service of free hosting, supposing that in some way the provider of that
hosting service would have earned his right income from me in some way (e.g.
a banner installed on my PC or other similar business). Fortunately up to
now I have never asked for charity dear David and I hope to have not to do
it in the future.

Have a good dinner!

Fernando

On Sat, Aug 6, 2011 at 5:35 PM, David Johnston  wrote:

> Since this thread is already top-post...
>
> One of the reasons software can be "free" is because people are able to
> make money doing things like hosting and consulting.
>
> If you are looking for charity because you are poor, or what you want to do
> has little commercial value, you would be wise to propose what it is you
> want to actually accomplish and the specific resources you likely need.
>  Showing effort on your part will project professionalism as opposed to the
> free-loader personality that you show when you simply ask for free stuff.
>  Simply pointing out that you need to eat as the reason why you need free
> hosting makes you look foolish.  The fact that you cross-posted projects
> disrespect for the very communities providing the "free" stuff you want to
> use.
>
> I'm sorry your research GRANT expired but you should focus on either
> obtaining a new grant or how to earn a regular income.  If you are starting
> your own business the reality is that you need funds as opposed to free
> hos

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

It is not my intention to continue this discussion within this mailing list,
so please stop to reply or reply just to me. Otherwise I have to answer
again to the community and I don't want to do it. :-)

Dear Chris, thank you. I will answer to you in the following.

On Sat, Aug 6, 2011 at 11:03 PM, Chris Travers wrote:

> I hope I am not feeding a troll here.
>
> The economic model behind PostgreSQL is a very good one.  Here is my
> understanding of it.  Various people in the community cooperate but
> they also sell products (EnterpriseDB, Green Plum) based on the
> codebase along with their proprietary enhancements.  Others sell
> services including hosting and consulting.  In general anyone who does
> not share everything practical ends up paying for it later in terms of
> internal maintenance overhead.  Everyone benefits.
>

> The wonderful thing about open source software is that every one of us
> owns the means of production not in a collective or a government but
> individually.  I can take PostgreSQL and make a living off it.  You
> can take my program that runs on it (LedgerSMB) and make a living off
> it.  We can take these pieces of software and use them to provide
> services to others. The barrier to getting into business for yourself
> is very low.
>

It's impossible that everyone benefits proportionally to his own effort of
development. In this sense the model is not "right". But this is the rule of
the open source model and it is OK because the rule is accepted by all the
developers.
>
>
> In my view, if you don't want to pay for hosting and you need all
> these features, you probably don't really need hosting.  The only way
> you will get hosting is if you convince someone that taking you on
> benefits them more than the costs (either by paying them or making the
> case that it's a good business idea to take you on), or  the
> wonderful thing about free and unfettered access to the means of
> production --- you can set up your own system with all these
> technologies.  That's the free hosting solution that might work best
> for you.
>

Simply I don't know how the business works for the hosting platforms. Or
better, now I have understood it. :-) For this reason I asked my question. I
supposed that it was a business similar to the one existing for "File
Hosting".

Thank you for your support. :-)

Fernando


> Best Wishes,
> Chris Travers
>

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

On Sun, Aug 7, 2011 at 1:40 AM, David Johnston  wrote:

> On Aug 6, 2011, at 18:03, Stuart McGraw  wrote:
>
> My point, while coming across a little harsh apparently, is that emphasis
> on requiring a free service projects a certain personality.
>

If a good service is free, like PostgreSQL is, the emphasis of using it is
justified if you do good things by it. The same is valide for free hardware,
but as 'we' know for the hardware is not exactly the same. However, because
free file hosting exists and because my funded project ended and I cannot
continue to economically support it with other incomes, I tried to ask if
the use of a free hosting platform where to install free software (java,
PostgreSQL, etc.) could exist.

Even just adding "or low cost" would have helped.
>

Unfortunately, you cannot ask questions on my behalf. I usually ask
questions on the basis of my requirements and knowledge. Then, if you want,
you can answer with possible comments or not or star a discussion. And
finally, I would thank you in any case for your attention and your time.


> That said, it wasn't the original request the got me to respond but the
> part about needing to eat.


I have never talked about the needing to eat!! I just wrote that my project
ended, but fortunately the life of a researcher/engineer is not made just of
funded projects. In any case thanks for your concern. :-)


> I know "we" started it with the comment about why there are no free hosting
> providers and I am just as guilty for adding to it.
>
> In all, though, I didn't mean to say anyone IS a free-loader only that you
> can be perceived as one and such perceptions can suppress otherwise useful
> responses.  In the end everyone free-loads and is taken advantage of at the
> same time in many different areas; and any judgements should be made only
> when many facts are known (if ever).
>

Sorry if I criticized the "free" model as somebody has defined it and as
probably it is. I explained my ideas in my previous email and I don't want
to bore you repeating again my point of view.

>
> I apologize for my tone earlier but to be honest this is probably one of
> the calmest flame-wars I've ever seen :)
>

I apologize too. :-) Peace done!

>
> The bottom line is I would not expect to find any individual or company
> willing or able to offer such a service, to the general public, for free.
>  And it is a service you are requesting as opposed to a product like
> PostgreSQL.  A product is more likely to be improved by the people using it
> compared to a service, and those improvements are likely to make it back
> into the original.
>
> But, there are a number of companies that do what you need for a price.  If
> you feel what you are doing is important it should at least be worth your
> time to talk to these companies and see what arrangements can be made
> instead of dismissing them outright because they charge for their services.
>  You may find someone inclined to take on pro-bono work for a good cause;
> especially if your needs are modest.  In short, ask for everything and then
> perform the filtering yourself instead of asking others to filter for you -
> only you know what your actual situation is which makes anyone else's
> filtering only an uninformed guess.
>

As answered before, you cannot ask questions on my behalf. :-) I already
filtered any "no free" solutions for valid reasons. What you suggest would
have caused just a great loss of time for me and for the hosting providers.

>
> David J.
>
>
> > Hello Fernando,
> >
> > I was sorry to read the harsh responses your request got
> > here.  The thing that has always appealed to me about the
> > free software movement is the spirit of cooperation and
> > mutual help that many involved exhibit.
> >
> > You quite rightly point out the hypocrisy of those who
> > call someone a "freeloader" when they themselves use free
> > software in profit making ventures without sharing their
> > profits with the software's developers and contributors.
> >
> > Please be assured that not everyone here reacted negatively
> > to your post.  I wish you success in your search.
> >
> > --
> > 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] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

John, Craig,

how do you explain the services of file hosting? By those services millions
of persons free-load pictures, videos, text, GBs of data, etc.. I think that
what I asked is quite similar, that is the use of a piece of remote hardware
where to have free software installed. The difference in my opinion is in
the fact that I implicitly asked also for the use of a free operating
system, but not in the hardware or in its maintenance.

On Sun, Aug 7, 2011 at 3:56 AM, John R Pierce  wrote:
On 08/06/11 4:40 PM, David Johnston wrote:

> The bottom line is I would not expect to find any individual or company
> willing or able to offer such a service, to the general public, for free.
>  And it is a service you are requesting as opposed to a product like
> PostgreSQL.  A product is more likely to be improved by the people using it
> compared to a service, and those improvements are likely to make it back
> into the original.
>

indeed, especially a service like hosting that has significant ongoing hard
costs involved...  a colocated server requires power, air conditioning,
network traffic and transit fees, management, physical security, and the
cost of the hardware itself, which has typically a 3-5 year lifespan (in 3
years, newer hardware can do so much more work its often not cost effective
to keep the old hardware online).

On Sun, Aug 7, 2011 at 4:25 AM, Craig Ringer  wrote:

> On 7/08/2011 1:08 AM, Scott Ribe wrote:
>
>> After open source for the software, we will wait for open resource for the
>>> hardware (this is just a first example http://www.arduino.cc/, even if
>>> of different nature).
>>>
>> While the plans may be free, the actual hardware sure as hell won't be.
>>
>>  A bit OT, but
>
> Arduino is not so much a "will" as an "is". It's in wide-spread use and has
> even been adopted for the base of the new Android peripheral development
> system - the Android Open Accessory Development Kit.
>
> http://developer.android.com/**guide/topics/usb/adk.html
>
> I struggle to see any connection between Arduino and PostgreSQL, though.
> They're very different  kinds of free/open source, as software "is" its
> specification and can be distributed at no cost, but you can't just download
> a hardware device and use it.
>
> --
> Craig Ringer
>

Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

On Sun, Aug 7, 2011 at 11:22 AM, John R Pierce  wrote:

> On 08/07/11 1:46 AM, Fernando Pianegiani wrote:
>
>> how do you explain the services of file hosting? By those services
>> millions of persons free-load pictures, videos, text, GBs of data, etc.. I
>> think that what I asked is quite similar, that is the use of a piece of
>> remote hardware where to have free software installed. The difference in my
>> opinion is in the fact that I implicitly asked also for the use of a free
>> operating system, but not in the hardware or in its maintenance.
>>
>
> that stuff is usually advertising supported.   many of those 'free' file
> hosting systems charge to let people download at reasonable speeds, and make
> the download process painful for freeloaders.  or, like Google Picasa's
> image service, they charge if you use more than a couple gigabytes.how
> do you attach advertising to a user programmed tomcat server with a postgres
> database?


This is an interesting question for people who want to develop business in
the field of the cloud.

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

[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

On Sun, Aug 7, 2011 at 2:41 PM, Craig Ringer  wrote:

> On 6/08/2011 4:02 PM, Fernando Pianegiani wrote:
>
>> Hello,
>>
>> do you know any FREE hosting platforms where PostgreSQL, Java SDK,
>> Tomcat (or other web servers) can be already found installed or where
>> they can be installed from scratch?
>>
>
> About the only hope I know of is hub.org .
>
> http://archives.postgresql.**org/pgsql-announce/2010-01/**msg0.php<http://archives.postgresql.org/pgsql-announce/2010-01/msg0.php>
>
> They're offering one-year free VPS services at certain times of year.
>
>
> Thanks a lot!


> By the way, one of the reasons you're not finding much free hosting for
> PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted.
> Your additional requirement for Java and Tomcat means you're certain to be
> stuck with a virtual private server (VPS) or a BSD Jail based host. I'll be
> very surprised if you can find any offerings that are free (as opposed to
> "free trial") in that vein, but I wish you luck in your search.
>

I see...

>
> What you *might* be able to do is find sponsorship for hosting or find
> someone who'll grant you free hosting for your project because they think
> that particular project is worthwhile and important. That'll depend a great
> deal on what you're trying to host and what the likely load will be.
>

this is very difficult, but it is exactly what I am doing in environments
different from this one. Even if this risks to be considered (not so
positively) as a request of charity... :-)

>
> --
> Craig Ringer
>

Re: [GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

On Sun, Aug 7, 2011 at 4:22 PM, David Johnston  wrote:

>
> > this is very difficult, but it is exactly what I am doing in environments
> different from this one. Even if this risks to be considered (not so
> positively) as a request of charity... :-)
>
> At that point, unless you have confidentiality requirements, why not just
> tell everyone what it is you are working on and see if anyone responds
> favorably?  It woul normally be deemed off-topic but at this point one more
> non-Postgresql post isn't going to make a big difference on this thread.
>
> Fundraising for a cause is quite a bit different than asking for a personal
> gift and it sound like your request falls into the former category.
>

Dear David, thank you for your post. I have not posted exactly a
non-PostgreSQL post, in fact I asked for information about possible services
of free hosting platforms with PostgreSQL installed. I repeat that I didn't
ask for a hosting platform but for information about possible inherent free
services.

The item of research focuses on the remote detection of events of health
hazard, like in particular the cardiac atrial fibrillation, by wireless
sensors installed on the body of the patient and a phone that forwards the
data towards the hosting. If somebody can be interested I pray him to ask me
for more information writing just to my email address. Thanks a lot!

>
> David J.

[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?

[Fernando Pianegiani]

Josh,

sorry for multiple posting.

This is the description of this mailing list:
"General discussion area for users. Apart from compile, acceptance test, and
bug problems, most new users will probably only be interested in this
mailing list (unless they want to contribute to development or
documentation). All non-bug related questions regarding PostgreSQL's version
of SQL, and all installation related questions that do not involve bugs or
failed compiles, should be restricted to this area. Please note that many of
the developers monitor this area."

So, in my opinion asking if somebody knows a hosting service where
PostgreSQL can be used for free is not inappropriate. But if you consider it
inappropriate and you are in a position to cancel my posts you have to
cancel them without any hesitation asap.

Fernando

On Mon, Aug 8, 2011 at 12:45 AM, Joshua Berkus  wrote:

> Fernando,
>
> You just posted your question to multiple innappropriate mailing lists.
>  Please do not do that again.
>
> --Josh Berkus
>

Re: [pgadmin-support] [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !!

[Fernando Hevia]

You could also disable "Read and write Unicode UTF-8 files" in
Options->Preferences.
It will not write a BOM but you will not have UTF-8 either.


On Thu, Apr 22, 2010 at 07:29, John Gage  wrote:

> Additionally, if the Vim option "bomb" is set to "nobomb" it will strip the
> BOM.  This solves my "problem" by permitting editing files in both Vim and
> pgAdmin3 Query tool and then being able to run the files in psql.
>
> :set nobomb   [in Vim]
>
>
>
> On Apr 22, 2010, at 12:12 PM, Magnus Hagander wrote:
>
>  FYI, psql in PostgreSQL 9.0 will ignore UTF8 BOMs.
>>
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-supp...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>

[GENERAL] pg_ctl start check sum failed

[Fernando Dominguez]

Hello,

I   try to use an old cluster into a new system.

The new system comes with a newer version of postgres so I uninstalled it
and I installed the same version that I had in the older system --->8.1

I got impressed when I Installed the 8.1 with dpkg -i and it started to run
without starting the daemon...

Is it possible to know  what directory is the server using to store the
data?

--- main question

Once I have installed the server I try to start it using pg_control start -D
/oldCluster directory but I get FATAL checksum incorrect.

I want to use the old data, any ideas?

Many thanks

[GENERAL] log_statement not working on pl/pgsql functions

[Fernando Moreno]

Hi, I've changed the setting log_statement to mod, in order to log data
modifications, and it's working fine with sentences sent by the client
application (psql included), but insert/update/delete sentences executed
inside functions are not logged. Functions are called in a select query.

I've reloaded (even restarted) the server, the line with the setting is
uncommented and "show log_statement" returns "mod". I changed its value to
"all" for a while and it worked as expected, logging every single query. By
the way, I'm using Postgresql 8.3.1 on window xp.

Am I doing something wrong?

Re: [GENERAL] RAISE NOTICE format in pgAdmin

[Fernando Moreno]

2008/8/29 Bill Todd <[EMAIL PROTECTED]>

> If I have a series of RAISE NOTICE 'xxx' statements in a plpgsql function
> and I call the function from pgAdmin the notice messages are concatenated on
> a single line on the Messages tab. Is there any way to get each message to
> appear on a separate line?
>
> Is there a better way than using RAISE NOTICE to debug functions?
>
> Bill
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

As far as I know, that is a known bug in the current version of pgAdmin, but
you can check the log file (directly or through the server status option)
for a more readable feedback.

[GENERAL] offtopic, about subject prefix

[Fernando Moreno]

Hello, I'm new to this mailing list, and I have a couple of questions:

Is it really necessary to add the [GENERAL] prefix?

Are messages without this prefix likely to be ignored by automatic filters
or something like that?

Thanks in advance.

Re: [GENERAL] offtopic, about subject prefix

[Fernando Moreno]

2008/9/3 brian <[EMAIL PROTECTED]>

> Fernando Moreno wrote:
>
>> Hello, I'm new to this mailing list, and I have a couple of questions:
>>
>> Is it really necessary to add the [GENERAL] prefix?
>>
>
> The prefix is added by the mailing list software. It's there so that people
> subscribed to multiple pgsql-* lists can easily distinguish them. There's no
> need to include it in your messages.
>
> b
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Thanks for the answer!

Re: [GENERAL] case expression

[Fernando Moreno]

>
> BTW, should you have an "else" clause in there? - What happens when the
> comparison fails?
>

As Tom said, a null value would be returned.

Re: [GENERAL] My first revoke

[Fernando Moreno]

Hi, first of all, a new role doesn't have any privilege on any table (every
type of database object has different default privileges), so you only have
to grant select on the tables you want, and yes, one by one.

You can also grant or revoke privileges this way: grant select on
table1,table2,table3...tableN to my_role;

[GENERAL] db_user_namespace, md5 and changing passwords

[Fernando Moreno]

Hi there, I'm going to use the db_user_namespace parameter to get a strong
relationship between roles and databases, multiple databases -users
included- residing in the same server without conflicts is my objective too.


Right now I'm working on the backup process, which ideally would let me
mirror a database and all of its users, keeping their passwords.
[EMAIL PROTECTED] must not collide with [EMAIL PROTECTED], this is why I
need db_user_namespace enabled.

Just before executing pg_dump, I will create a table to store roles
information: name and options like login, encrypted password (from
pg_authid) and connection limit. When restoring, I'll add the
current_database() value to the stored role names, in order to create them
correctly. The problem is that md5sums in postgresql passwords are not
created from "password", but "passworduser", and "user" is not likely to be
the same because it depends directly on the database name; therefore,
authentication will always fail even when trying with the same password.

Is there a way to avoid this problem without having to reset all passwords
or storing them in plain text?

Thanks in advance.

Re: [GENERAL] db_user_namespace, md5 and changing passwords

[Fernando Moreno]

2008/10/7 Alvaro Herrera <[EMAIL PROTECTED]>

> Bruce Momjian escribió:
>
> > Well, I posted about this in August with no one replying:
> >
> >   http://archives.postgresql.org/pgsql-admin/2008-08/msg00068.php
> >
> > Basically, there is a mismatch between what libpq and the backend think
> > is the username, and that affects how MD5 uses the salt on the two sides
> > of the connection.
>
> I totally agree that this needs a redesign, but we must provide
> something to replace it with, not just rip it off.
>
> > The minimal solution would be to document this and print a proper
> > error message.
>
> Seems fair.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Thanks for the answers, I wasn't aware of the conflict between md5-auth and
db_user_namespace, but it seems highly related to my problem.

Could you suggest me another way to handle this? Managing users in the usual
way is likely to work fine most of the time, but not when an specific
database(+users) is backed up and restored in the same server, or when two
or more databases (corresponding to different applications) try to create
users with the same name.

Right now there's only one database in the server, but the backup module
should be ready to handle these situations in the future.

Cheers.

Re: [GENERAL] Using a variable as tablename ins plpgsql?

[Fernando Moreno]

2008/10/20 Glyn Astill <[EMAIL PROTECTED]>

> Hi people,
>
> Hopefully this is a quickie, I want to pass in a table name to a plpgsql
> function and then use that table name in my queries.
>
> Is EXECUTE the only way to do this?
>

As far as I know, yes. That's the only way to create queries using dynamic
table and column names.

Re: [GENERAL] triggers problems whit function

[Fernando Moreno]

2008/10/22 Ma. Cristina Peña C. <[EMAIL PROTECTED]>

>  I want to use a function in to a trigger
>
>
>
> This is my
>
> CREATE FUNCTION "subradio"(integer) RETURNS integer AS 'select cast(count
> (claveubica) as integer ) from asradios where ubicacion =0;' LANGUAGE 'sql';
>
>
>
> And my ttrigger is
>
> CREATE TRIGGER validaradios AFTER DELETE ON subestacion FOR EACH ROW
> EXECUTE PROCEDURE subradio(0);
>
>
>
> But I got an error
>
> ERROR:  CreateTrigger: function subradio() does not exist
>
>
>
> What can I do??
>
A trigger function must have a specific structure, it takes no arguments and
returns "trigger". Besides, trigger functions are supposed to do some
processing before or after insert, update or delete operations, so there's
no sense in returning a row count.

Take a look at the docs, specially chapter 35 and 38.9.

Cheers.

[GENERAL] backup and permissions

[Fernando Moreno]

Hi, I'm working on a little backup utility for a desktop application. It's
going to execute pg_dumpall (-r) and pg_dump, but first I have to deal with
the permissions needed to do that:

1. Users (pgsql roles) enabled to backup would be superusers all the time.
This sounds insecure.

2. Users will get superuser access through a security definer function just
before the backup, then they'll be nosuperuser again. An interrupted backup
process would be dangerous, but I could check whether or not this clause is
enabled, every time a user connects. Still risky.

3. Users will just be able to read every object in the database, and
pg_authid. I've done some tests and this seems enough.

I need some advice to choose the better/safer option, what would you do?

Thanks in advance.

Re: [GENERAL] backup and permissions

[Fernando Moreno]

Hello Scott, thanks for your answer. I've just noticed that my first message
lacked some important info.

First, this is an accounting software, and there's only one database. Almost
all of the options (buttons, generally ) are stored in a set of tables,
beside the database privileges needed to work properly. Permissions are
assigned from the application, and they're translated internally as a list
of grant/revoke commands on tables, sequences, functions and schemas. Every
application user is a pgsql role with login and nosuperuser options.

Right now there are about 20 users, 3 of them with admin permissions (still
regular users, but they can execute functions and modify data that others
can't). They can't create, alter or drop database objects.

Doing backups will be just an option more to enable/disable and it's not
likely to be a public one, just a few people will be allowed to do it. What
they do with the backup file is beyond my scope, of course, but I wouldn't
like to see a bunch of users having fun with the database server ;) . This
is why I'm thinking of a temporary superuser privilege, or even a temporary
read access to let a user execute pg_dump and pg_dumpall without being a
superuser. By the way, I don't like the idea of backing up the postgres
account, I might need to create a customized dump to include just the
regular roles and their md5-passwords.

Maybe, as said by a scottish girl: I think I'm paranoid...

Cheers.

[GENERAL] Table appears on listing but can't drop it

[Fernando Morgenstern]

Hello,

I'm running version 8.4.1 and  have a table that appears on listing ( when i 
run \l ) but i can't drop it. Example:

postgres=# \l
  List of databases
   Name|  Owner   | Encoding |  Collation  |Ctype|   Access 
privileges   
---+--+--+-+-+---
  skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
 : 
postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
 : 
postgres=CTc/postgres

postgres=# drop database skynet;
ERROR:  database "skynet" does not exist

I intentionally removed other databases name.

Also, i verified that i can run CREATE DATABASE skynet having two databases 
with the same name.

Any ideas of what causes this problem?

Regards,
---

Fernando Marcelo
www.consultorpc.com
ferna...@consultorpc.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] Table appears on listing but can't drop it

[Fernando Morgenstern]

Em 08/01/2010, às 14:48, Tom Lane escreveu:

> Adrian Klaver  writes:
>> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
>>> Name|  Owner   | Encoding |  Collation  |Ctype|   Access 
>>> privileges
>>> ---+--+--+-+-+---
>>> skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>>> : postgres=CTc/postgres
>>> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>>> : postgres=CTc/postgres
> 
>> You have a space at the beginning of the name. Try:
>> drop database " skynet";
> 
> I'm not sure about that, because the whole row seems to be offset in
> his email.  That could be just copy-and-paste sloppiness.  Still,
> some sort of non-printing character in the name seems to be indicated,
> else he'd not have been able to create another db with name "skynet".
> 
> Try something like
>   select '"' || datname || '"' from pg_database
> to get a clearer view of what's really in there.
> 
>   regards, tom lane


Hello,

Thanks for your quick answers. The extra space is indeed a copy-and-paste 
issue. Here it is the select that you suggested:

postgres=# select '"' || datname || '"' from pg_database;
  ?column?   
-
 "template1"
 "template0"
 "t1"
 "skynet"


Best Regards,
---

Fernando Marcelo
www.consultorpc.com
ferna...@consultorpc.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] Table appears on listing but can't drop it

[Fernando Morgenstern]

Em 08/01/2010, às 15:49, Adrian Klaver escreveu:

> On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
> 
>> Hello,
>> 
>> Thanks for your quick answers. The extra space is indeed a copy-and-paste 
>> issue. Here it is the select that you suggested:
>> 
>> postgres=# select '"' || datname || '"' from pg_database;
>>   ?column?
>> -
>>  "template1"
>>  "template0"
>>  "t1"
>>  "skynet"
>> 
>> 
>> Best Regards,
>> ---
>> 
>> Fernando Marcelo
>> www.consultorpc.com
>> ferna...@consultorpc.com
> 
> Can you connect to it?
> 
> -- 
> Adrian Klaver
> adrian.kla...@gmail.com

No, i get this:

$ psql skynet
psql: FATAL:  database "skynet" does not exist

I can create a database with the same name:

postgres=# create database skynet;
CREATE DATABASE

postgres=# select '"' || datname || '"' from pg_database;
  ?column?   
-
 "template1"
 "template0"
 "postgres"
 "t1"
 "skynet"
 "skynet"

And drop the newly created database:

postgres=# drop database skynet;
DROP DATABASE
postgres=# select '"' || datname || '"' from pg_database;
  ?column?   
-
 "template1"
 "template0"
 "postgres"
 "t1"
 "pgpool"
 "skynet"

Strange, isn't it?

Regards,
---

Fernando Marcelo
www.consultorpc.com
ferna...@consultorpc.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] Table appears on listing but can't drop it

[Fernando Morgenstern]

Em 08/01/2010, às 15:58, Adrian Klaver escreveu:

> 
> Actually what is strange is that your previous listing :
> postgres=# select '"' || datname || '"' from pg_database;
>   ?column?
> -
>  "template1"
>  "template0"
>  "t1"
>  "skynet"
> 
> is not the same as the one above:
> 
> postgres=# select '"' || datname || '"' from pg_database;
>  ?column?
> -
> "template1"
> "template0"
> "postgres"
> "t1"
> "pgpool"
> "skynet"
> 
> In particular the presence of postgres,t1 and pgpool.
> 
> Are you sure which cluster you are pointing at and whether the psql version 
> matches the server version?
> 
> -- 
> Adrian Klaver
> adrian.kla...@gmail.com

Hi,

The reason for pgpool is that we were using it, but decided to stop due to some 
problems. At this moment we have pgpool with one node only. Also, i am 
connecting directly to postgres in order to verify this problem.

And the difference between this and previous listing is because i am manually 
removing databases name as they contain client names that i don't want to share 
here.

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
ferna...@consultorpc.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] Table appears on listing but can't drop it

[Fernando Morgenstern]

Em 09/01/2010, às 19:40, hubert depesz lubaczewski escreveu:

> On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
>> postgres=# drop database skynet;
>> ERROR:  database "skynet" does not exist
> 
> do:
> 
> psql -l | hexump -C
> and examine output.
> 
> Best regards,
> 
> depesz
> 
> -- 
> Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
> jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007


Hi,

I have done:

# psql -U postgres -p 4000 -l | hexdump -C

And got the two databases: http://pastebin.ca/1746711

I couldn't find any difference here.

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
ferna...@consultorpc.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] Table appears on listing but can't drop it

[Fernando Morgenstern]

Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu:

>> Hi,
>> 
>> I have done:
>> 
>> # psql -U postgres -p 4000 -l | hexdump -C
>> 
>> And got the two databases: http://pastebin.ca/1746711
>> 
>> I couldn't find any difference here.
> 
> Could you add -qAt to psql options and rerun the command?
> 
> Best regards,
> 
> depesz
> 
> -- 
> Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
> jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Hello,

Same result: http://pastebin.ca/1746714

Regards,
---

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

[GENERAL] Moving database cluster

[Fernando Hevia]

An Ubuntu install creates a postgres cluster automatically on
/var/lib/postgresql/8.4/main
Whats the best procedure for moving this cluster to an other location?
Should I just rerun initdb? What happens then with the default cluster or
how could I delete it?

Thanks,
Fernando.


-- 
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] Moving database cluster

[Fernando Hevia]

 

> -Mensaje original-
> De: Guillaume Lelarge [mailto:guilla...@lelarge.info] 
> 
> Le 14/01/2010 21:40, Fernando Hevia a écrit :
> > 
> > An Ubuntu install creates a postgres cluster automatically on 
> > /var/lib/postgresql/8.4/main Whats the best procedure for 
> moving this 
> > cluster to an other location?
> > Should I just rerun initdb? What happens then with the 
> default cluster 
> > or how could I delete it?
> > 
> 
> If you don't have any data on it, the best way is to drop it 
> with pg_dropcluster, and create a new one with pg_createcluster.
> 
> 

Sound advice. Thanks!!


-- 
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] Moving database cluster

[Fernando Hevia]

 

> -Mensaje original-
> De: 
> 
> On Thursday 14 January 2010, Fernando Hevia elucidated thus:
> > An Ubuntu install creates a postgres cluster automatically on 
> > /var/lib/postgresql/8.4/main Whats the best procedure for 
> moving this 
> > cluster to an other location? Should I just rerun initdb? 
> What happens 
> > then with the default cluster or how could I delete it?
> 
> The easiest way is to shut down Pg, move the 'main' directory 
> somewhere else, and then point a symlink to the new location.
> 

Thanks for your reply. 
I had considered this first but then I wasn't sure if there would be any
performance penalty.
The current main directory sits on a 'slow' RAID 1 volume while the new one
will sit on a 'fast' 12 disk RAID 10 volume.
I guess the symlink shouldn't be troublesome but I don't know if some PG
process will continually be reading this link encumbering somehow the RAID 1
disks.

Regards,
Fernando.


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

[GENERAL] Changing FS when full

[Fernando Schapachnik]

Hi,

I have a big database on FS1, now almost full. Have space on FS2, 
where I created a tablespace and moved every table and index to it. 
Still, lots of space used on FS1. The problem is not pg_xlog, but 
base:

# du -hs base/105658651/* | fgrep G
1,0Gbase/105658651/106377323
1,0Gbase/105658651/106377323.1
1,0Gbase/105658651/106377323.2
1,0Gbase/105658651/106377323.3
1,0Gbase/105658651/106377323.4
1,0Gbase/105658651/125520217
1,0Gbase/105658651/127352052
1,0Gbase/105658651/127352052.1
1,0Gbase/105658651/127352052.2
1,0Gbase/105658651/127352052.3
1,0Gbase/105658651/127352052.4
1,0Gbase/105658651/127352052.5

Unfortunately no volume management is available, so I can't move 
disks from FS2 to FS1.

I could play soft links tricks, but I'm afraid of paying the 
FS-traversal penalty on each file access (is that right?).

So, any way of instructing PG (8.1 if that matters) to place those 
files elsewhere without an initdb?

Thanks!

Fernando.

-- 
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] Changing FS when full

[Fernando Schapachnik]

En un mensaje anterior, Greg Smith escribió:
> >So, any way of instructing PG (8.1 if that matters) to place those 
> >files elsewhere without an initdb?
> >  
> 
> You can create another table just like the original on a tablespace 
> using the new storage, drop the original, and then rename the new one to 
> the original name.  This is described as "another way to cluster data" 
> in the Notes section of 
> http://www.postgresql.org/docs/8.4/static/sql-cluster.html , and it has 
> a few warnings related to information that can be lost in this 
> situation.  Make sure you've moved all temporary files onto the new 
> filesystem first, observing the warning about that there too.

One question: is this different from ALTER TABLE ... SET TABLESPACE?

Thanks.

-- 
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 I turn on query logger?

[Fernando Moreno]

Check out the "log_statement" option in the postgresql.conf file
(there's an entire section (18.7) in the docs about logging).

-- 
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] Saber cuando se dispara el trigger (After: insert,update,delete)

[Fernando Moreno]

Hola, no olvides que esta es la lista de correo para usuarios de habla inglesa.

-- 
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] Array in nested query

[Fernando Moreno]

What error are you getting?

I tried your query and I had to add an explicit cast to smallint[] to
make it work. Like this:

... a.attnum = any ((select conkey FROM pg_catalog.pg_constraint WHERE
> oid = 3708025)::smallint[]);

It seems strange to me, I didn't expect the ANY clause to need that
cast. Or maybe I'm missing something.

Cheers.

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

[GENERAL] foxpro, odbc, data types and unnecessary convertions

[Fernando Moreno]

Hi all, I'm using visual foxpro 9 -not my decision- for a client
application. Statements are writen as the typical sql string and sent
through ODBC.

For numbers, I have to convert them first to string and then remove
the spaces, the code looks like this: sql_string = "some sql" +
alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and
str in a third function but it's still tricky. A shorter and
presumably better way to do the same is: sql_string = "some_column =
?foxpro_variable ". The problem with the last option is that, watching
the pgsql log, values are sent this way: '12345'::float(8), so for
every numeric value, no matter its type, I'm sending 12 characters
more and the server is doing convertions that I don't need.

Having a lot of foreign keys and other numeric data, I think this
behaviour is not so good for network (remote and poor connection) and
server performance. I'm almost decided to keep doing the trim/str
thing, but my question is: am I exaggerating? what would you do?

Thanks.

-- 
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] foxpro, odbc, data types and unnecessary convertions

[Fernando Moreno]

Thank you very much for your advice, I guess I'm wasting my time in
this 'problem'. I'm going to check that class, it seems pretty useful.
And by the way...yes, this is a born-dead app (at least on the client
side) and it's likely to be ported to .NET in the future, but like I
said before, it's not my call.

Cheers.

-- 
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] when to use "execute" in plpgsql?

[Fernando Moreno]

Hi, check this out:
http://archives.postgresql.org/pgsql-general/2008-05/msg00938.php

I would say that execute is the only way to achieve some things
related to schemas and temp tables.

-- 
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] Log en Postgresql 8.1.11

[Fernando Moreno]

El día 2 de marzo de 2009 18:14, Angelo Astorga
 escribió:
> Trabajo con postgresql 7.3.4 y el log se guarda en el dir
> .../data/serverlog, ahora que utilizo postgresql 8.1.11 no encuentro donde
> guarda el log, alguna ayuda de como generar archivo log de postgresql...
>
> aastorga

Esta es la lista en inglés, que no te sorprenda que tus mensajes sean ignorados.

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

[GENERAL] pg_toast_temp_xx AND pg_temp_xx SCHEMAS

[Fernando Hevia]

Hi guys,
 
In one of my databases (8.3.6) I have around 25 schemas with names like
these. There seem to appear a couple of new ones every now and then.
 
The only reference I found was this:
--- 
http://www.postgresql.org/docs/8.3/static/release-8-3.html

* Place temporary tables' TOAST tables in special schemas named
pg_toast_temp_nnn (Tom) 

This allows low-level code to recognize these tables as temporary, which
enables various optimizations such as not WAL-logging changes and using
local rather than shared buffers for access. This also fixes a bug wherein
backends unexpectedly held open file references to temporary TOAST tables. 
---

Could anyone point me out some documentation about what these schemas mean?
Should I be worried? Anything I should do about it?
 
Thanks,
Fernando


-- 
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] pg_toast_temp_xx AND pg_temp_xx SCHEMAS

[Fernando Hevia]

 

> -Mensaje original-
> De: Tom Lane [mailto:t...@sss.pgh.pa.us] 
> 
> ... and there's one for each concurrently 
> executing backend if it creates any temp tables.
> 

That explains the growth I noticed as backends have been incremented
recently.
Thanks!!


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

[GENERAL] SSL error: decryption failed or bad record mac (pg as Samba backend)

[Fernando Schapachnik]

Hi,

I'm trying to use an SSL-enabled (OpenSSL 0.9.7d) Postgres 7.3.9 as database 
backend to Samba 3.0.11. On startup Samba opens a connection, and passes it to 
every fork()ed process. On some scenarios (consistenly, when somebody tries to 
log into a workstation after reboot), Samba spits:

SELECT ... (details ommited)
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

And the server log says:
[24129]  LOG:  SSL error: decryption failed or bad record mac
[24129]  LOG:  pq_recvbuf: recv() failed: Connection reset by peer

There is no problem when not using SSL. The Samba code doesn't have any 
SSL-specifics, leaving it to libpq. Any ideas?

Thanks in advance.

Regards.

Fernando.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Vaccum analyze.

[Fernando Lujan]

Hi folks,
I wanna know from you, how often must I run vaccum analyze on my db?
Once per day, twice... One per hour and so on...
I didn't find a especific document about this question.
Thanks in advance.
Fernando Lujan
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[GENERAL] libpq usage in Samba

[Fernando Schapachnik]

I'm trying to figure out why Samba is failing at high loads if using Postgres as
a backend (7.3.9 in my setup). On startup in makes one connection only against
the database which is shared among all the Samba processes. In turn, each issue
a PQexec() over the same connection (a SELECT, actually). Is that a safe use? I
would think it is not, but not really sure. I mean, does libpq multiplex queries
and handle concurrency correctly in a such a scenario?

Thanks.


Fernando.

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

Re: [GENERAL] *bsd port that installs the contribs?

[Fernando Schapachnik]

En un mensaje anterior, Matt Van Mater escribió:
> I there a way to specify that I want the contribs directory (and its
> children) compiled and installed during the build process in the
> various BSD ports systems?  Of course I understand that what works on
> one BSD may not work on the others, but I don't see any FLAVORs or
> make options that would suggest there is a way to do this (I have
> looked in Open|Free ).

cd /usr/ports/databases/postgresql-contrib
make && make install && make clean

Regards.

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

[GENERAL] Pg_autovaccum.

[Fernando Lujan]

Hi everyone,

Which is the best configuration to pg_autovaccum?

Are there benchmarks showing the improvements, after and before the
service had started?

Thanks in advance.

-- 
Fernando Lujan

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

[GENERAL] Generating random values.

[Fernando Lujan]

Hi folks,

I have a table wich contains my users... I want to insert to each user
a random password, so I need a random function. Is there such function
in Postgres? I just found the RANDOM which generates values between
0.0 and 1.0.

Any help or suggestion will be appreciated. :)

Fernando Lujan

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

Re: [despammed] [GENERAL] Generating random values.

[Fernando Lujan]

On 8/17/05, A. Kretschmer <[EMAIL PROTECTED]> wrote:
 
> select substring(md5(random()) from 5 for 15);

Thanks everybody, this solution will fullfill my needs... ;)

Sincerely,

Fernando Lujan

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

Re: [GENERAL] Generating random values.

[Fernando Lujan]

On 8/18/05, Mike Nolan <[EMAIL PROTECTED]> wrote:

> As I indicated in my original response, there is no best answer to the
> issue of password choices, though there are probably a few 'worst'
> answers.  :-)
> 
> Once someone has established a password scheme, either randomly generated
> or user selected, it should not be that difficult to write routines to
> generate acceptable passwords or to enforce standards for user-generated
> passwords.

Good point Mike. In my case, for instance, the users will have the
opportunity to chance their password. There's no problems with
passwords which a user could remember. At least, the user will not
trouble you with a password reset requirement. :D

Thanks for all replies and suggestions.

Fernando Lujan

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

[GENERAL] Restoring just a table or row from a backup copy.

[Fernando Lujan]

Is there a way to do that?

Thanks in advance.

Fernando Lujan

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

[GENERAL] Help with inventory control

[Fernando Grijalba]

I need the following to work with PostgreSQL.  I was thinking of
reading uncommitted transactions, but just found out they do not work
on PG.  I need to have user 1 take an item form inventory for an
order and before user 1 finishes the transaction user 2 will query the
inventory.  I need user 2 to see the quantity on-hand reduced by
the amount user 1 took.

Because read uncommitted is not supported is there a way to do this?

Thank you for all your cooperation.

Fernando

Re: [GENERAL] Help with inventory control

[Fernando Grijalba]

Thank you for your response.

I want to avoid the following situation.

User1 starts order and takes the last two units.  User2 starts
order 1 minute after and checks inventory. He sees 2 units left and
adds them to the his order.  User1 commits his order.  Now
User2 cannot finish his order because the products are not available
anymore.

This is the problem I want to avoid.  Therefore if User1 takes the
product but does not finish the order I want the inventory to still
show that the product is sold out to other users.

Any suggestions on how to implemnt that?

Thank you,

Fernando

Re: [GENERAL] Help with inventory control

[Fernando Grijalba]

Thank you for your input Doug, but that did not work. User2 was still getting the qty unchanged.

Any more ideas?

I was thinking of creating a table that will hold the product code and
the qty taken by an order.  check this table evey minute and if a
product has been there for more that 10 minutes and return it to
inventory if that is the case.  After I save the order I would
remove the prodcut for this order from this table.  This way I can
just update the qty in one transaction when the user gets the product
for the order and then save the order in a different transaction. 
This will allow me to reduce the quantity at the time the order
obtained the product and increase it when the order is cancelled or if
the user forgets or his/her computer crashes.

Is this a good idea?  Your input is appreciated.

Fernando

Re: [GENERAL] Help with inventory control - THANK YOU!!!

[Fernando Grijalba]

Thank you Mike for your answer.  That is what I had in mind.

You guys have helped me alot.  Thank you so much for your cooperation.

Fernando

Re: [GENERAL] Help with inventory control - Thank You!

[Fernando Grijalba]

Thank you very much Mike.  I will do just that.

FernandoOn 9/30/05, Mike Nolan <[EMAIL PROTECTED]> wrote:
> User1 starts order and takes the last two units. User2 starts order 1 minut=> e> after and checks inventory. He sees 2 units left and adds them to the his> order. User1 commits his order. Now User2 cannot finish his order because
> the products are not available anymore.>> This is the problem I want to avoid. Therefore if User1 takes the product> but does not finish the order I want the inventory to still show that the
> product is sold out to other users.>> Any suggestions on how to implemnt that?One common way to deal with it is to have a separate 'hold quantity'field (or table) for items in pending orders.  You can commit to that
field or table as each line item is entered, revised or deleted duringorder entry.  When the order is finalized, you simultaneously releasethe hold and take the item out of inventory.The primary problem with this method is abandoned orders, because you
want to release that inventory so someone else can order it.That's more of an issue if you are writing an application for yourcustomers than if it's being used by a sales staff who will know tocancel an abandoned order.  (However, you probably still need a 'cancel
pending transaction' capability to deal with things like system crashes.)I once designed a web-based transaction system which kept a timestamp oneach 'on hold' line item.  It assumed that if the order wasn't completed
within an hour the order had been abandoned, and at that point it releasedthe hold on any items.  (Actually it just checked the timestamp whenadding up the 'on hold' quantity during an inventory check and ignored
any timestamp that was more than an hour old.)There was also a one hour inactivity timeout on the web form, as I recall.You should be able to do most of this with trigger functions.--Mike Nolan

[GENERAL] Infinite loop in transformExpr()

[Fernando Schapachnik]

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

Backtrace:

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

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

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

Should I file a bug report?

Thanks!

Fernando.

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

   http://archives.postgresql.org/

Re: [GENERAL] Infinite loop in transformExpr()

[Fernando Schapachnik]

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <[EMAIL PROTECTED]> writes:
> > I've stumbled upon what seems to be a core-dumping infinite recursion 
> > in transformExpr(), on 8.1.6.
> 
> A test case would help.

The culprit query looks like:
SELECT ...
FROM
(SELECT ... FROM 3 tables
WHERE join condition AND
int_key IN (enumeration of aprox. 16000 values here)
GROUP BY ...) 
LEFT OUTER JOIN join condition GROUP BY ...;

(I can provide a more specific version if needed, but look below.)

A couple of strange things.

The query is executed in production via pgperl in a (FreeBSD 5.x)
server where:

# limit stacksize
stacksize65536 kbytes
# psql -U pgsql template1 -c 'SHOW max_stack_depth'
 max_stack_depth
-
 2048

Running the query in this scenario (reasonably) gives:
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth".

So I'm unsure why it explodes in production.

On a testing environment, however, setting max_stack_depth to 16000, 
it efectively dumps core. The strange thing is, that while trying to 
trim down the query, now I'm stuck with:

Fatal error 'Cannot allocate red zone for initial thread' at line 343 
in file /usr/src/lib/libpthread/thread/thr_init.c (errno = 12)

(ie, the server works, I just can't get the original error again, not 
even after restart or full/freeze vacuum).

The backtrace now gives:

(gdb) bt
#0  0x284eb37b in kill () from /lib/libc.so.5
#1  0x284e0422 in raise () from /lib/libc.so.5
#2  0x28552c1b in abort () from /lib/libc.so.5
#3  0x290b6a7c in pthread_testcancel () from /usr/lib/libpthread.so.1
#4  0x290b3067 in pthread_setconcurrency () from 
/usr/lib/libpthread.so.1
#5  0x290b2e87 in pthread_setconcurrency () from 
/usr/lib/libpthread.so.1
#6  0x290b627a in pthread_testcancel () from /usr/lib/libpthread.so.1
#7  0x290b740a in __error () from /usr/lib/libpthread.so.1
#8  0x2909e7ae in ?? () from /usr/lib/libpthread.so.1
#9  0x282a5845 in find_symdef () from /libexec/ld-elf.so.1
#10 0x282a61aa in dlopen () from /libexec/ld-elf.so.1
#11 0x08164d38 in BSD44_derived_dlopen ()
#12 0x081f9550 in load_external_function ()
#13 0x081fa06c in fmgr_info_cxt ()
#14 0x081f9f46 in fmgr_info_cxt ()
#15 0x081f9d62 in fmgr_info_cxt ()
#16 0x0811a0dc in init_fcache ()
#17 0x0811a742 in ExecMakeTableFunctionResult ()
#18 0x08125e67 in ExecReScanNestLoop ()
#19 0x0811db75 in ExecScan ()
#20 0x08125ee3 in ExecFunctionScan ()
#21 0x08119061 in ExecProcNode ()
#22 0x08126b8f in ExecSort ()
#23 0x081190c0 in ExecProcNode ()
#24 0x081251f3 in ExecMergeJoin ()
#25 0x08119087 in ExecProcNode ()
#26 0x08126b8f in ExecSort ()
#27 0x081190c0 in ExecProcNode ()
#28 0x08120f5c in ExecAgg ()
#29 0x08120ed5 in ExecAgg ()
#30 0x081190e6 in ExecProcNode ()
#31 0x08117b10 in ExecEndPlan ()
#32 0x08116fb0 in ExecutorRun ()
#33 0x08191f9d in PortalRun ()
#34 0x08191ccc in PortalRun ()
#35 0x0818e259 in pg_plan_queries ()
#36 0x08190dad in PostgresMain ()
#37 0x0816e41c in ClosePostmasterPorts ()
#38 0x0816dc13 in ClosePostmasterPorts ()
#39 0x0816bf07 in PostmasterMain ()
#40 0x0816b875 in PostmasterMain ()
#41 0x08133a0f in main ()


Thanks.

Fernando.

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

Re: [GENERAL] Infinite loop in transformExpr()

[Fernando Schapachnik]

En un mensaje anterior, Tom Lane escribió:
> PG versions before 8.2 don't handle very long IN lists particularly
> well.  This query will take a fair amount of stack space to parse, not
> to mention an unreasonably long time to plan.  (You should consider
> putting the 16000 values in a temp table and doing a join, instead.)

Thanks for the tip!

[...]

> Most likely, the production machine has a kernel-enforced stack limit
> setting that is less than what "max_stack_depth" claims.  Up till recently
> (8.2 I think), we didn't make any effort to verify that "max_stack_depth"
> was set to a sane value.  If it's too high you will get crashes rather
> than "stack depth limit exceeded", because overrunning the kernel limit
> is typically treated as a SIGSEGV.

[...]

> Hm.  It would appear that you are loading some custom code that sucks
> pthread support into the backend.  This is generally a bad idea in any

Not really. Only PLSQL and dblink. Anyway, my understanding is that 
this should be already fixed in 8.2 and is not worth looking deeply, 
right?

Thanks for your help.


Fernando.

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

Re: [GENERAL] [webmaster] Question

[Fernando Fernández]

Ok :))

I am developing a Windows application, under NT 4 version.
I am connecting to database throught ODBC (I had installed postgreSQL
drivers)
My database version... mmm... I am not sure, but I think 7.4.2 version
I am programing in Visual FoxPro 8.0, but this information is not important
I think.

In sql i can do something like this::
begin transaction
Update  where clave= @clave
IF @@error= 0  and @@rowcount= 0
Insert ... ( @clave,  )
commit transaction

I don't know wether "@@rowcount" exists in postgreSQL
Thanks you in general and in special to this "someone" that will be happy to
give me some help.

>From Palma de Mallorca
Fernando



- Original Message -
From: "Robert Treat" <[EMAIL PROTECTED]>
To: "Fernando Fernández" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, November 12, 2004 4:11 PM
Subject: Re: [webmaster] Question


On Fri, 2004-11-12 at 07:34, Fernando Fernández wrote:
> Hi :))
> I am Fernando, from Mallorca - Spain
>
> I am using PostgreSQL for my databases. Everything is going all right,
> but now i need in my application to know how many records has updated an
> update command.
>
> I know I can do something like:
> SELECT COUNT(*) AS xCount FROM ...
> IF xCount = 0
> INSERT ...
> ELSE
> UPDATE ...
> ENDIF
>
> but I would like do something like:
> UPDATE ...
> IF records_updated = 0
> INSERT
> ENDIF
>
> is it possible?

Yes, this is certainly possible, but there's no way we can explain it to
you without knowing some details of your work environment. Please send
an email to either [EMAIL PROTECTED] or
[EMAIL PROTECTED] that includes some details of your
programming environment (pl language, php, C, whatever; database
version; any driver layers involved) and someone will be happy to give
you some more detailed help.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



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

Re: [GENERAL] Problem with Select output

[Fernando Schapachnik]

sleect function(ssd_a) from ...

where function is one of the built-in text function or one of your own written 
in some of the supported (by your version) procedure languages.

Regards.

En un mensaje anterior, srini vasan escribió:
> --- srini vasan <[EMAIL PROTECTED]> wrote:
> 
> > Hi
> >I am facing some issues with select query. The
> > values of the columns in one table contains "\n" as
> > a
> > part of the value. So when I execute the select
> > query
> > on this table, I am getting the following output.
> > 
> > # select * from stdhlr_subscriber_profile ;
> > subscriber_id | ssd_a
> > ---+--
> > 47032 | test
> > value
> > (1 row)
> > 
> > But the value of ssd_a is "test\nvalue". I want the
> > select output to print the value of ssd_a as
> > "test\nvalue". 

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

Re: [GENERAL] Problem Dropping a Database with users connected to it

[Fernando Schapachnik]

Just kill the processes. You can grep for postgres AND idle. It doesn't prevent 
new connections, but doesn't look like an issue in your scenario. If you need 
that, you can restart with a copy of pg_hba.conf that only allows localhost, do 
your drop & recreate, and then restart again.


Regards.

En un mensaje anterior, Eric Dorland escribió:
> Hi,
> 
> I'm basically trying to do what the subject says, through various means
> with no success. The basic situation is that every night we recreate our
> development database with a complete copy of our live data. The problem
> is some of the developers (well me especially) leave open connections to
> the DB at night, so the database drop fails. Now that's ok, but I need
> some sort of alternative... I thought of:
> 
> * Disconnecting all other users before dropping the db, but that doesn't
> seem possible (I could start and stop the db, but that doesn't stop any
> clients from just reconnecting right away).

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

   http://archives.postgresql.org

Re: [GENERAL] An out of memory error when doing a vacuum full

[Fernando Schapachnik]

Is your system using full RAM? Ie, what does limits -a show?


Regards.

Fernando.

En un mensaje anterior, Sean Shanny escribió:
> To all,
> 
> The facts:
> 
> PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI 
> drives in hardware RAID 0 configuration.  Database size with indexes is 
> currently 122GB.  DB size before we completed the vacuum full was 150GB.

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

Re: [GENERAL] An out of memory error when doing a vacuum full

[Fernando Schapachnik]

Take a look at datasize: your processes are allowed a maximum of 512 Mb RAM.
Read the handbook to find out how to reconfigure your kernel and the limits
(and/or ulimit) man page to tweak the values for individual processes.

Good luck!

Fernando.

En un mensaje anterior, Sean Shanny escribió:
> limits -a
> Resource limits (current):
>  cputime  infinity secs
>  filesize infinity kb
>  datasize   524288 kb
>  stacksize   65536 kb
>  coredumpsize infinity kb
>  memoryuseinfinity kb
>  memorylocked infinity kb
>  maxprocesses 5547
>  openfiles   11095
>  sbsize   infinity bytes
>  vmemoryuse   infinity kb

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])