date:20090506


Slansky Lukas wrote:


1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 
aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT


3. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

 

I was wondering when these rules are not OK for our environment. It 
seems that rules 1 and 2 sometimes pass packets and therefore these 
packets are rejected.


After a long period of inactivity, perhaps?

If you're relying on `-m state' or `-m ctstate' you should be using a 
TCP keepalive. Otherwise the connection tracking entry for the 
connection will be purged after a while - how long depends on your 
firewall configuration - and then packets will no longer be seen as part 
of an established connection.


--
Craig Ringer

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

[GENERAL] Transaction settings: nowait

2009-05-06 Thread durumdara


Hi!

In FireBird the transactions have these settings:

SET TRANSACTION
   [NAME/|hostvar|/]
   [READ WRITE | READ ONLY]
   [ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
 | READ COMMITTED [[NO] RECORD_VERSION] } ]
   [WAIT | NO WAIT]
   [LOCK TIMEOUT/|seconds|/]

And this is the important thing:

[WAIT | NO WAIT]
   [LOCK TIMEOUT/|seconds|/]

If set wait and timeout, the Firebird is waiting for the locked resource 
(record) for X seconds before it show deadlock error.


But when you set no wait, the deadlock error immediately shown by the 
server.


I wanna ask that if I want to avoid the full deadlocks.

For. example: I forget to make commit, or rollback on exception then all 
resources I used (updated) is locked.


If I use nowait, the clients immediately get the error message, and they 
are are not sitting deafly and blindly before the screen, waiting for 
what will happen.


So: have PGSQL same mechanism like nowait?

Thanks for your help:
dd

Re: [GENERAL] Transaction settings: nowait

2009-05-06 Thread durumdara

Hi!

2009.05.06. 11:54 keltezéssel, Richard Huxton írta:

durumdara wrote:

So: have PGSQL same mechanism like nowait?

When you take a lock:
http://www.postgresql.org/docs/8.3/interactive/sql-lock.html
http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

As I see these things are not help to me when two transactions are
trying to use same recource...

Yesterday I tried to test my PG Website. I withdrew a rollback
instruction from the code.
The transaction started, but the insert SQL was wrong (I miss some
required field).
So this transaction haven't been changed anything on the table, but the
transaction remaining in active state because my fail.

Then the pgadmin is flew away on a field readjustment in this table.
This was an deadlock...

I don't want to see any deadlocks... I don't want to lock the tables.

There is also the statement_timeout config setting which will
terminate any single statement that takes longer than a set time.
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT

As I read this config param, this is terminate the statements only.
As I think, this meaning that if I have a wrong join, or I do very
slow query, the server can cancel and terminate it.
But if I have a transaction that remaining in opened state, this could
be a source of the errors (deadlocks).

Thanks for your help:
dd

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

[GENERAL] tsearch2 memory problem

2009-05-06 Thread ml

Hi!

I realized that loading a dictionary with ~16 words consumes
additional ~40 MB of memory for each connection. It obviously doesn't
use a shared memory. Is it possible to decrease the memory consumption?
I found this thread

http://www.mail-archive.com/pgsql-general@postgresql.org/msg116924.html

but no solution. Is there any progress in this area?

Thanks.

David

-- 
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] Transaction settings: nowait

2009-05-06 Thread Albe Laurenz

durumdara wrote:
 In FireBird the transactions have these settings:
 
 
 SET TRANSACTION
[NAME hostvar]
[READ WRITE | READ ONLY]
[ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
  | READ COMMITTED [[NO] RECORD_VERSION] } ]
[WAIT | NO WAIT]
[LOCK TIMEOUT seconds]
 And this is the important thing:
 
 
 [WAIT | NO WAIT]
[LOCK TIMEOUT seconds]
 If set wait and timeout, the Firebird is waiting for the 
 locked resource (record) for X seconds before it show deadlock error.
 
 But when you set no wait, the deadlock error immediately 
 shown by the server.
 
 I wanna ask that if I want to avoid the full deadlocks. 
 
 For. example: I forget to make commit, or rollback on 
 exception then all resources I used (updated) is locked.
 
 If I use nowait, the clients immediately get the error 
 message, and they are are not sitting deafly and blindly 
 before the screen, waiting for what will happen.
 
 So: have PGSQL same mechanism like nowait?

I assume that you mean locks when you write deadlocks.

There is no exact equivalent, but you can set the variable
statement_timeout to something else than 0 in your session
(or in your transaction).
Then every statement longer than this many seconds will be
terminated.

Yours,
Laurenz Albe

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

[GENERAL] XML - PG ?

2009-05-06 Thread Gauthier, Dave

Is there a way to read an XML file into a postgres DB?  I'm thinking that it 
will create and relate whatever tables are necessary to reflect whatever's 
implied by the XML file structure.

Thanks for any pointers !

Re: [GENERAL] XML - PG ?

On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
 Is there a way to read an XML file into a postgres DB?  I’m thinking that it
 will create and relate whatever tables are necessary to reflect whatever’s
 implied by the XML file structure.

since xml is basically completely unstructured, you are not giving
enough information about what you'd like to do. that said, be sure and
check out the pg xml extensions...

merlin

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

Re: [GENERAL] PGSQL x iptables

On Wed, May 6, 2009 at 3:47 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:
 Slansky Lukas wrote:

 1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

 2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s
 aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT

 3. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited


 I was wondering when these rules are not OK for our environment. It seems
 that rules 1 and 2 sometimes pass packets and therefore these packets are
 rejected.

 After a long period of inactivity, perhaps?

 If you're relying on `-m state' or `-m ctstate' you should be using a TCP
 keepalive. Otherwise the connection tracking entry for the connection will
 be purged after a while - how long depends on your firewall configuration -
 and then packets will no longer be seen as part of an established
 connection.

small addendum: i bet this is the problem.  You can configure server
keepalives in postgresql.conf.  I'd suggest two minutes
(tcp_keepalives_idle=120) .

merlin

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

Re: [GENERAL] Transaction settings: nowait

2009/5/6 Albe Laurenz laurenz.a...@wien.gv.at:
 durumdara wrote:
 In FireBird the transactions have these settings:


 SET TRANSACTION
    [NAME hostvar]
    [READ WRITE | READ ONLY]
    [ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
                          | READ COMMITTED [[NO] RECORD_VERSION] } ]
    [WAIT | NO WAIT]
    [LOCK TIMEOUT seconds]
 And this is the important thing:


 [WAIT | NO WAIT]
    [LOCK TIMEOUT seconds]
 If set wait and timeout, the Firebird is waiting for the
 locked resource (record) for X seconds before it show deadlock error.

 But when you set no wait, the deadlock error immediately
 shown by the server.

 I wanna ask that if I want to avoid the full deadlocks.

 For. example: I forget to make commit, or rollback on
 exception then all resources I used (updated) is locked.

 If I use nowait, the clients immediately get the error
 message, and they are are not sitting deafly and blindly
 before the screen, waiting for what will happen.

 So: have PGSQL same mechanism like nowait?

'select...for update' has a nowait clause, as does 'lock'.  also
advisory locks, using pg_try_advisory_lock.

another tactic is to send queries asynchronously and fire a cancel
based on client driven logic.

merlin

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

Re: [GENERAL] XML - PG ?

2009-05-06 Thread Christophe



On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:

Is there a way to read an XML file into a postgres DB?  I’m thinking  
that it will create and relate whatever tables are necessary to  
reflect whatever’s implied by the XML file structure.


There's no built-in functionality that does what you describe,  
although building such a thing would be very straight-forward. There  
are many application-specific decisions you'd need to make (what level  
of hierarchy in the XML file corresponds to a database/schema/table,  
for example, and how to handle nested fields and missing ones).

Re: [GENERAL] XML - PG ?

2009-05-06 Thread Gauthier, Dave

Maybe...

ALL
  EMPLOYEES
EMP EMP_NAME=JOE JOB=CARPENTER /
EMP EMP_NAME=FRANK JOB=PLUMBER/
EMP EMP_NAME=SUE JOB=CARPENTER/
  /EMPLOYEES
  JOBS
JOB JOB_NAME=CARPENTER SALARY=25.50 /
JOB JOB_NAME=PLUMBER SALARY=28.75 /
  /JOBS
/ALL

...equals...

create table employees (emp_name varchar[64], job varchar[64]);
create table jobs (job_name varchar[64], salary float);
insert into employees (emp_name,job) values ('JOE','CARPENTER');
insert into employees (emp_name,job) values ('FRANK','PLUMBER');
insert into employees (emp_name,job) values ('SUE','CARPENTER');
insert into jobs (job_name,salary) values (CARPENTER,25.50);
insert into jobs (job_name,salary) values (PLUMBER,28.75);

After that, it's up to the user to understand that employees.job = 
jobs.job_name and index appropriately.





-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com] 
Sent: Wednesday, May 06, 2009 11:23 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] XML - PG ?

On Wed, May 6, 2009 at 10:47 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
 Is there a way to read an XML file into a postgres DB?  I'm thinking that it
 will create and relate whatever tables are necessary to reflect whatever's
 implied by the XML file structure.

since xml is basically completely unstructured, you are not giving
enough information about what you'd like to do. that said, be sure and
check out the pg xml extensions...

merlin

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

Re: [GENERAL] XML - PG ?

2009-05-06 Thread Joao Ferreira gmail

hello,

as a perl addict I am... I recommend checking this out:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm

it's pretty flexible and allows you to specify to some extent just how
the database structure is infered from the XML...

check it out

Joao




On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
 
 On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
 
  Is there a way to read an XML file into a postgres DB?  I’m thinking
  that it will create and relate whatever tables are necessary to
  reflect whatever’s implied by the XML file structure.
 
 
 There's no built-in functionality that does what you describe,
 although building such a thing would be very straight-forward. There
 are many application-specific decisions you'd need to make (what level
 of hierarchy in the XML file corresponds to a database/schema/table,
 for example, and how to handle nested fields and missing ones).


-- 
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] XML - PG ?

2009-05-06 Thread Joao Ferreira gmail

On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote:
 hello,
 
 as a perl addict I am... I recommend checking this out:
 
 http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm
 
 it's pretty flexible and allows you to specify to some extent just how
 the database structure is infered from the XML...


... maybe start here to get a faster grasp:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm

j


 
 check it out
 
 Joao
 
 
 
 
 On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
  
  On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
  
   Is there a way to read an XML file into a postgres DB?  I’m thinking
   that it will create and relate whatever tables are necessary to
   reflect whatever’s implied by the XML file structure.
  
  
  There's no built-in functionality that does what you describe,
  although building such a thing would be very straight-forward. There
  are many application-specific decisions you'd need to make (what level
  of hierarchy in the XML file corresponds to a database/schema/table,
  for example, and how to handle nested fields and missing ones).
 
 


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

[GENERAL] prepared statements and DBD::Pg

2009-05-06 Thread JP Fletcher


Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when 
the first command in a prepared statement is 'CREATE TEMP TABLE'.


For instance, this works:

   my $prepare_sql =SQL;
   CREATE TEMP TABLE foo( id int, user_id int,);

   INSERT INTO foo(1, 1);

   INSERT INTO foo(2, 2);
   SQL

   my $sth = $dbh-prepare($prepare_sql);


This produces the error

ERROR:  cannot insert multiple commands into a prepared statement

my $prepare_sql =SQL;
  
   INSERT INTO foo(1, 1);


   INSERT INTO foo(2, 2);
   SQL

   my $sth = $dbh-prepare($prepare_sql);


Is this the expected behaviour?


--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfle...@ca.afilias.info



--
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] XML - PG ?

2009-05-06 Thread Thomas Kellerer


Gauthier, Dave, 06.05.2009 17:40:

Maybe...

ALL
  EMPLOYEES
EMP EMP_NAME=JOE JOB=CARPENTER /
EMP EMP_NAME=FRANK JOB=PLUMBER/
EMP EMP_NAME=SUE JOB=CARPENTER/
  /EMPLOYEES
  JOBS
JOB JOB_NAME=CARPENTER SALARY=25.50 /
JOB JOB_NAME=PLUMBER SALARY=28.75 /
  /JOBS
/ALL

...equals...

create table employees (emp_name varchar[64], job varchar[64]);
create table jobs (job_name varchar[64], salary float);
insert into employees (emp_name,job) values ('JOE','CARPENTER');
insert into employees (emp_name,job) values ('FRANK','PLUMBER');
insert into employees (emp_name,job) values ('SUE','CARPENTER');
insert into jobs (job_name,salary) values (CARPENTER,25.50);
insert into jobs (job_name,salary) values (PLUMBER,28.75);



You could use XSLT to tranform the XML into the approriate SQL Statements.

Thomas


--
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] XML - PG ?

2009-05-06 Thread Joao Ferreira

On Wed, 2009-05-06 at 16:53 +0100, Joao Ferreira gmail wrote:
 hello,
 
 as a perl addict I am... I recommend checking this out:
 
 http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag/Cookbook.pm
 
 it's pretty flexible and allows you to specify to some extent just how
 the database structure is infered from the XML...
 

maybe start here to get a faster grasp:

http://search.cpan.org/~cmungall/DBIx-DBStag/DBIx/DBStag.pm

j


 check it out
 
 Joao
 
 
 
 
 On Wed, 2009-05-06 at 11:31 -0400, Christophe wrote:
  
  On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote:
  
   Is there a way to read an XML file into a postgres DB?  I’m thinking
   that it will create and relate whatever tables are necessary to
   reflect whatever’s implied by the XML file structure.
  
  
  There's no built-in functionality that does what you describe,
  although building such a thing would be very straight-forward. There
  are many application-specific decisions you'd need to make (what level
  of hierarchy in the XML file corresponds to a database/schema/table,
  for example, and how to handle nested fields and missing ones).
 
 


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

[GENERAL] how to select temp table

2009-05-06 Thread liuzg4 liuzg4

ver 8.4
i create two table with same name  named 'testtable'

one is temp table



 i select * from testtable
then this table is a public  or a temp ???
drop table testtable

then is this table a public or a temp? i test ,it drop temp table fisrst!

so am i  must show  public or pg_temp_1  when i has two table with same name
!!!

Re: [GENERAL] PGSQL x iptables

2009-05-06 Thread Slansky Lukas

 Slansky Lukas wrote:

  1. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j
ACCEPT
  
  2. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 
  aaa.bbb.ccc.ddd --dport 5432 -j ACCEPT
  
  3. -A RH-Firewall-1-INPUT -j REJECT --reject-with
icmp-host-prohibited
  
   
  
  I was wondering when these rules are not OK for our environment. It 
  seems that rules 1 and 2 sometimes pass packets and therefore these 
  packets are rejected.

 Craig Ringer wrote:

 After a long period of inactivity, perhaps?

Is 15 seconds long period? I don't think so.

 If you're relying on `-m state' or `-m ctstate' you should be using a 
 TCP keepalive. Otherwise the connection tracking entry for the 

I'll try to lower TCP keepa live times and make some tests.

 connection will be purged after a while - how long depends on your 
 firewall configuration - and then packets will no longer be seen as
part 
 of an established connection.

Deleting -m state --state NEW seems to be solution but I'm trying to
figure out origin of the problem.

To John: I know it's related to iptables but this state seems to be only
on PG connections :-)

L.

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

[GENERAL] Yahoo Everest MPP - open source release?

2009-05-06 Thread digital_illuminati


There was some talk of Yahoo possibly open sourcing the source for the 
PostgreSQL MPP column store they built. Does anyone know if this is actually 
slated to happen, when, etc?

I'm looking for an open source solution for doing efficient queries on 300 
million+ row tables, and it sounds like Everest would be perfect.

Rob


-- 
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] Yahoo Everest MPP - open source release?

2009-05-06 Thread David Fetter

On Wed, May 06, 2009 at 09:28:37AM -0700, digital_illumin...@yahoo.com wrote:
 
 There was some talk of Yahoo possibly open sourcing the source for
 the PostgreSQL MPP column store they built.

Actually, Yahoo! acquired a company that had built this store.

 Does anyone know if this is actually slated to happen, when, etc?

Somebody at Yahoo! might.

 I'm looking for an open source solution for doing efficient queries
 on 300 million+ row tables, and it sounds like Everest would be
 perfect.

Community PostgreSQL handles situations like this just fine.  If you
want public help, you have come to the right place on this mailing
list.  If you want help on other terms--say, non-disclosure
agreement--there are plenty of companies including mine,
http://www.pgexperts.com/, that will be happy to work with you.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] prepared statements and DBD::Pg

2009-05-06 Thread Keary Suska


On May 6, 2009, at 9:39 AM, JP Fletcher wrote:


Hi,

I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1)  
when the first command in a prepared statement is 'CREATE TEMP TABLE'.


For instance, this works:

  my $prepare_sql =SQL;
  CREATE TEMP TABLE foo( id int, user_id int,);

  INSERT INTO foo(1, 1);

  INSERT INTO foo(2, 2);
  SQL

  my $sth = $dbh-prepare($prepare_sql);


This produces the error

ERROR:  cannot insert multiple commands into a prepared statement

my $prepare_sql =SQL;
INSERT INTO foo(1, 1);

  INSERT INTO foo(2, 2);
  SQL

  my $sth = $dbh-prepare($prepare_sql);


Is this the expected behaviour?




You should follow up on the DBD::Pg list, but I would guess that the  
module is doing a surface check to determine whether the statement is  
a candidate for being prepared. I bet that whenever the first  
statement is not a select, insert, delete, or update that you will not  
get an error. What is happening ion these cases is that no preparation  
is happening at all.


HTH,

Keary Suska
Esoteritech, Inc.
Demystifying technology for your home or business


--
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] Yahoo Everest MPP - open source release?

2009-05-06 Thread Joshua D. Drake

On Wed, 2009-05-06 at 09:38 -0700, David Fetter wrote:

  I'm looking for an open source solution for doing efficient queries
  on 300 million+ row tables, and it sounds like Everest would be
  perfect.
 
 Community PostgreSQL handles situations like this just fine. 

Yes it does. 300 million rows really isn't much. Of course if they are
very wide rows there could be an issue but that seems more a design
issue.

  If you
 want public help, you have come to the right place on this mailing
 list.  If you want help on other terms--say, non-disclosure
 agreement--there are plenty of companies including mine,
 http://www.pgexperts.com/, that will be happy to work with you.
 

Or others:

OmniTI, http://www.omniti.com/ 
Keving Kempter LLC: http://www.kevinkempterllc.com/
Command Prompt: http://www.commandprompt.com/
EnterpriseDB: http://www.enterprisedb.com/

Sincerely,

Joshua D. Drake





-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
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] prepared statements and DBD::Pg

2009-05-06 Thread Andrej

2009/5/7 JP Fletcher jpfle...@ca.afilias.info:
 Hi,

 I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
 first command in a prepared statement is 'CREATE TEMP TABLE'.

 For instance, this works:

   my $prepare_sql =SQL;
   CREATE TEMP TABLE foo( id int, user_id int,);

   INSERT INTO foo(1, 1);

   INSERT INTO foo(2, 2);
   SQL

   my $sth = $dbh-prepare($prepare_sql);


 This produces the error

 ERROR:  cannot insert multiple commands into a prepared statement

Blessed be CPAN and the manuals for DBD
http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare


WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be prepared by the
Postgres server. Statements that were legal before may no longer work.
See below for details.

The prepare method prepares a statement for later execution.
PostgreSQL supports prepared statements, which enables DBD::Pg to only
send the query once, and simply send the arguments for every
subsequent call to execute. DBD::Pg can use these server-side
prepared statements, or it can just send the entire query to the
server each time. The best way is automatically chosen for each query.
This will be sufficient for most users: keep reading for a more
detailed explanation and some optional flags.

Queries that do not begin with the word SELECT, INSERT, UPDATE,
or DELETE are never sent as server-side prepared statements.


Cheers,
Andrej

-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

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

[GENERAL] Postgres partially hang after inactivity

2009-05-06 Thread Marco Maccaferri


Hi.

I have a weird problem with PostgreSQL on a Windows machine. This is a 
test installation used to beta test a webapplication running with 
Tomcat, so most of the times it sits idle. Yesterday and today I had to 
update some table definitions, so issued the 'alter table...' command 
and the server hanged. The webapplication runs well so the queries are 
working but seems that somehow the modifications are locked. I'm not 
sure if writing works (haven't tested it, will do the next time).


I'm new to postgres so I don't know exactly what to do to track down 
this problem, the log files doesn't seems to report any issue. Any 
suggestion about where to look to understand what's wrong ?


As additional informations, the webapplication uses hibernate to access 
the database, I'm not sure but it is possible that the sessions are not 
always closed cleanly or closed at all. Postgres version is 8.3.7.


Regards,
Marco.


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

Re: [GENERAL] how to select temp table

2009-05-06 Thread Robert Gravsjö


On 2009-05-06 14.34, liuzg4 liuzg4 wrote:

ver 8.4
i create two table with same name  named 'testtable'

one is temp table



  i select * from testtable
then this table is a public  or a temp ???


Temp. To access public use select * from public.testtable.

Temporary tables exist in a special schema, so a schema name cannot be 
given when creating a temporary table.


See http://www.postgresql.org/docs/8.4/static/sql-createtable.html for 
details.


/r


drop table testtable

then is this table a public or a temp? i test ,it drop temp table fisrst!

so am i  must show  public or pg_temp_1  when i has two table with same name
!!!




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

Re: [GENERAL] how to select temp table

2009-05-06 Thread Alvaro Herrera

Robert Gravsjö escribió:
 On 2009-05-06 14.34, liuzg4 liuzg4 wrote:
 ver 8.4
 i create two table with same name  named 'testtable'

 one is temp table

   i select * from testtable
 then this table is a public  or a temp ???

 Temp. To access public use select * from public.testtable.

 Temporary tables exist in a special schema, so a schema name cannot be  
 given when creating a temporary table.

Note that you can refer to the temp table like this:

select * from pg_temp.testtable;

Also, you can make the non-temp table first in the search path by
putting pg_temp later than the public schema (or wherever you have
created the function):


select * from testtable;-- refers to temp
set search_path to 'public', 'pg_temp';
select * from testtable;-- refers to non-temp

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

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

Re: [GENERAL] Postgres partially hang after inactivity

On Wed, May 6, 2009 at 12:48 PM, Marco Maccaferri ma...@maccasoft.com wrote:
 Hi.

 I have a weird problem with PostgreSQL on a Windows machine. This is a test
 installation used to beta test a webapplication running with Tomcat, so most
 of the times it sits idle. Yesterday and today I had to update some table
 definitions, so issued the 'alter table...' command and the server hanged.
 The webapplication runs well so the queries are working but seems that
 somehow the modifications are locked. I'm not sure if writing works (haven't
 tested it, will do the next time).

probably, you had a transaction sitting around that is not closed (to
see: select * from pg_stat_activity).

the alter table is waiting for that transaction to finish, but itself
blocks all _new_ transactions (alter table acquires a strong lock on
the table).   Cancel the alter table query or the query that is
blocking your alter table.  Again, check pg_stat_activity and
ungranted locks (select * from pg_locks where granted = false).

merlin

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

[GENERAL] Is thre a way?

2009-05-06 Thread Anderson dos Santos Donda

Hi o/

I have a table with 2000 names and emails.. and I did UPDATE list SET email
= '' without the WHERE and now all rows is null in column email..


I have a backup made with pg_dump.. is there a way to restore only the email
data?


Thanks!!

Re: [GENERAL] Is thre a way?

2009-05-06 Thread Dann Corbit

Look at the cheat-sheet:

http://www.postgresonline.com/journal/index.php?/archives/63-PostgreSQL-
8.3-Pg_dump-Pg_Restore-Cheatsheet-Overview.html

 

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Anderson dos
Santos Donda
Sent: Wednesday, May 06, 2009 1:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Is thre a way?

 

Hi o/

I have a table with 2000 names and emails.. and I did UPDATE list SET
email = '' without the WHERE and now all rows is null in column email..


I have a backup made with pg_dump.. is there a way to restore only the
email data?


Thanks!!

Re: [GENERAL] Is thre a way?

2009-05-06 Thread Raymond O'Donnell

On 06/05/2009 21:28, Anderson dos Santos Donda wrote:

 I have a table with 2000 names and emails.. and I did UPDATE list SET
 email = '' without the WHERE and now all rows is null in column
 email..

pedant mode
They're not null, they're just filled with empty strings :-)
/pedant mode

 I have a backup made with pg_dump.. is there a way to restore only 
 the email data?

If the backup is in PLAIN (text) format, you can copy-and-paste the
relevant bits into a new script, and run that through psql.

If it's in one of the binary formats, I think pg_restore lets you choose
which objects you want to restore.

Either way, you ought to be OK. You may find it easiest to drop the
table first, restore it from the backup, and then restore any foreign
key dependencies.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

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

[GENERAL] WARNING: silent data corruption possible from PL/ruby 0.5.0 (after Ruby upgrade)

2009-05-06 Thread Peter Much


Dear all,

since upgrading Ruby from 1.8.6.287 to 1.8.7.72, and recompiling 
PL/ruby 0.5.0, functions written in PL/ruby may ignore their 
given parameters and instead compute with undefined values,
providing nonsense results.

The test-suite provided with PL/ruby will show the problem.

Upgrading to PL/ruby 0.5.3 does not solve the problem, but that
version seems to detect the problem and raise an error.

I have figured out how the problem appears, but do not understand
why it appears or where exactly the cause is.
The problem seems related to the way PL/ruby uses a Ruby function
named rb_block_call. That function will only be used when detected
during compilation of PL/ruby. While this was not the case with 
Ruby 1.8.6, it is with Ruby 1.8.7.
So, a workaround to get the applications operative is simply to 
suppress the detection of that function.

It is unclear if the actual problem is in Ruby or in PL/ruby. It it
also unclear what this function rb_block_call is good for or why
it did appear with Ruby 1.8.7.

The author of PL/ruby has been contacted, but did not answer.

Further details can be found in that bug report:
http://www.freebsd.org/cgi/query-pr.cgi?pr=ports/134300


rgds,
PMc

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

[GENERAL] Question on pg_xlog and pg_control placement

2009-05-06 Thread Richard Sickler

Hi,

I'm installing a new 8.3.7 system and have a question on placement locations
for pg_xlog and pg_control.

I've read in the documentation pertaining to pg_xlog, that it may be ...of
advantage if the log is located on another disk than the main database
files.

As PostGRES is installed on one physical disk, and my database is on a
second, dedicated physical disk, I thought the above seemed like good
advice.  In that way if the disk that the WAL is located on breaks, I can
install a replacement disk, restore my last backup, and use the WAL to get
back to where I was.  Great.  Ah, then I read that the pg_control file
contains the last checkpoint, and the checkpoint is used in the REDO
operation.

Should the pg_control file also be moved to the non-database disk?  Any
other file moves recommended?  Thank you.

Drive A  would contain POSTGRES binaries,  pg_xlog, and pg_control
Drive B  would contain base, global, pg_clog, etc.


Rich S.

[GENERAL] date ranges in where

2009-05-06 Thread Miguel Miranda

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i want
toknow what users logged in for last time between 2009-05-01 and 2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse it
doesnt include who logged in 2009-05-02 15:30:00, etc...

Re: [GENERAL] date ranges in where

2009-05-06 Thread Raymond O'Donnell

On 06/05/2009 22:12, Miguel Miranda wrote:
 Hi, what is the recommended way to select a range of dates?
 
 Lets say a have a table with a lastlogin (timestamp) column and i want
 toknow what users logged in for last time between 2009-05-01 and 2009-05-02?
 
 I know that a simple
 
 where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse it
 doesnt include who logged in 2009-05-02 15:30:00, etc...

Why not just include the time in the comparisons?

  between '2009-05-01 00:00'::timestamp
and '2009-05-02 23:59:59'::timestamp

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] date ranges in where



On May 6, 2009, at 2:12 PM, Miguel Miranda wrote:


Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i  
want toknow what users logged in for last time between 2009-05-01  
and 2009-05-02?


I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work  
beacuse it doesnt include who logged in 2009-05-02 15:30:00, etc...



WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

or, if the values have are some unknown X and Y dates then you can do  
this:


WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] date ranges in where

2009-05-06 Thread Adrian Klaver

On Wednesday 06 May 2009 2:12:47 pm Miguel Miranda wrote:
 Hi, what is the recommended way to select a range of dates?

 Lets say a have a table with a lastlogin (timestamp) column and i want
 toknow what users logged in for last time between 2009-05-01 and
 2009-05-02?

 I know that a simple

 where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse
 it doesnt include who logged in 2009-05-02 15:30:00, etc...

lastlogin between '2009-05-01' AND '2009-05-03'

-- 
Adrian Klaver
akla...@comcast.net

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

Re: [GENERAL] XML - PG ?

2009-05-06 Thread Eric Schwarzenbach

Gauthier, Dave wrote:

 Is there a way to read an XML file into a postgres DB?  I’m thinking
 that it will create and relate whatever tables are necessary to
 reflect whatever’s implied by the XML file structure.

  

 Thanks for any pointers !

That's a pretty common problem, and not one that needs to have a
postgresql-specific solution. There are definitely solutions out there,
but it may take some Googling to find a good one for your needs. One
option might be data binding tools (Castor and Liquid XML come to mind)
with which you can definitely go from XML to SQL, though many of them
may require going through an intermediate object model.

A simple approach (simple depending what technologies you're using and
are already familiar with) might be to use XSLT to transform your XML
either directly into SQL or into some other format easily loaded to a
database (such as the XML format used by DbUnit).

Eric

-- 
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] date ranges in where



On May 6, 2009, at 2:17 PM, Erik Jones wrote:



On May 6, 2009, at 2:12 PM, Miguel Miranda wrote:


Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i  
want toknow what users logged in for last time between 2009-05-01  
and 2009-05-02?


I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work  
beacuse it doesnt include who logged in 2009-05-02 15:30:00, etc...



WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

or, if the values have are some unknown X and Y dates then you can  
do this:


WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


Woops, that last one should've been:

WHERE lastlogin = 'X' AND lastlogin  'Y' + '1 day'::interval

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] date ranges in where



On May 6, 2009, at 2:19 PM, Adrian Klaver wrote:


On Wednesday 06 May 2009 2:12:47 pm Miguel Miranda wrote:

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i  
want

toknow what users logged in for last time between 2009-05-01 and
2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work  
beacuse

it doesnt include who logged in 2009-05-02 15:30:00, etc...


lastlogin between '2009-05-01' AND '2009-05-03'


Technically, BETWEEN is inclusive of the two values given so that  
would also match '2009-05-03 00:00:00'.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] XML - PG ?

2009-05-06 Thread John R Pierce


Gauthier, Dave wrote:


Is there a way to read an XML file into a postgres DB? I’m thinking 
that it will create and relate whatever tables are necessary to 
reflect whatever’s implied by the XML file structure.


Thanks for any pointers !



As others have said, the fundamental problem is that generalized XML is 
freeform and inherently unstructured. Every piece of data can have a 
different structure. IMHO, XML is fundamentally flawed by mixing the 
metadata with the data.





--
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] date ranges in where

2009-05-06 Thread Miguel Miranda

Well, i tried all your sugestions, and i found some funny issues, i use the
query to count exactly in a day by day basis, and running the query with

WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

OR

WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


it includes the 0 hours of day 3:

05-02-2009 12:00:00 AM

The exact result are from running:

between '2009-05-01 00:00'::timestamp
   and '2009-05-02 23:59:59'::timestamp

but i select the ranges from a web form using a textbox, and right now i
dont have a java calendar at hand, i tried this with good result:

WHERE lastlogin::date BETWEEN  '2009-05-01' AND '2009-05-02'

But now the query uses seq scan and not the index in lastlogin column.

Is there another way?


On Wed, May 6, 2009 at 3:17 PM, Erik Jones ejo...@engineyard.com wrote:


 On May 6, 2009, at 2:12 PM, Miguel Miranda wrote:

  Hi, what is the recommended way to select a range of dates?

 Lets say a have a table with a lastlogin (timestamp) column and i want
 toknow what users logged in for last time between 2009-05-01 and 2009-05-02?

 I know that a simple

 where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse
 it doesnt include who logged in 2009-05-02 15:30:00, etc...



 WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

 or, if the values have are some unknown X and Y dates then you can do this:

 WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval

 Erik Jones, Database Administrator
 Engine Yard
 Support, Scalability, Reliability
 866.518.9273 x 260
 Location: US/Pacific
 IRC: mage2k

Re: [GENERAL] date ranges in where



On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:

Well, i tried all your sugestions, and i found some funny issues, i  
use the query to count exactly in a day by day basis, and running  
the query with


WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

OR

WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


it includes the 0 hours of day 3:

05-02-2009 12:00:00 AM


No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01  
and 2009-05-02.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






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

[GENERAL] PgUS 501c3 Public Charity

2009-05-06 Thread Joshua D. Drake

Hello,

Yeah its not general technical discussion but this little bit of news
warrants more widely read attention. PgUS (http://www.postgresql.us/)
received its 501c3 public charity status today. You can view the 
determination letter here:

https://www.postgresql.us/determination_letter

Thanks to everyone who helped!

Sincerely,

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
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] date ranges in where

2009-05-06 Thread Miguel Miranda

sorry,  i edited the date, the correct one is

05-03-2009 12:00:00 AM

If i group by day, it count 1 user for day 2009-05-03 in the output, so it
adds 1 to the total count of the range

regards

On Wed, May 6, 2009 at 3:51 PM, Erik Jones ejo...@engineyard.com wrote:


 On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:

  Well, i tried all your sugestions, and i found some funny issues, i use
 the query to count exactly in a day by day basis, and running the query with

 WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

 OR

 WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


 it includes the 0 hours of day 3:

 05-02-2009 12:00:00 AM


 No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01 and
 2009-05-02.


 Erik Jones, Database Administrator
 Engine Yard
 Support, Scalability, Reliability
 866.518.9273 x 260
 Location: US/Pacific
 IRC: mage2k

Re: [GENERAL] date ranges in where

2009-05-06 Thread Adrian Klaver

On Wednesday 06 May 2009 2:51:08 pm Erik Jones wrote:
 On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:
  Well, i tried all your sugestions, and i found some funny issues, i
  use the query to count exactly in a day by day basis, and running
  the query with
 
  WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'
 
  OR
 
  WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval
 
 
  it includes the 0 hours of day 3:
 
  05-02-2009 12:00:00 AM

 No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01
 and 2009-05-02.

The problem being that midnight is both the end of one day and the start of 
another.


 Erik Jones, Database Administrator
 Engine Yard
 Support, Scalability, Reliability
 866.518.9273 x 260
 Location: US/Pacific
 IRC: mage2k



-- 
Adrian Klaver
akla...@comcast.net

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

Re: [GENERAL] XML - PG ?

2009-05-06 Thread Ries van Twisk



On May 6, 2009, at 4:16 PM, Eric Schwarzenbach wrote:


Gauthier, Dave wrote:


Is there a way to read an XML file into a postgres DB?  I’m thinking
that it will create and relate whatever tables are necessary to
reflect whatever’s implied by the XML file structure.



Thanks for any pointers !


That's a pretty common problem, and not one that needs to have a
postgresql-specific solution. There are definitely solutions out  
there,

but it may take some Googling to find a good one for your needs. One
option might be data binding tools (Castor and Liquid XML come to  
mind)

with which you can definitely go from XML to SQL, though many of them
may require going through an intermediate object model.

A simple approach (simple depending what technologies you're using and
are already familiar with) might be to use XSLT to transform your XML
either directly into SQL or into some other format easily loaded to a
database (such as the XML format used by DbUnit).

Eric



Call me a GUI boy, but I use JasperETL :)

Ries





--
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] date ranges in where



On May 6, 2009, at 2:55 PM, Miguel Miranda wrote:



On Wed, May 6, 2009 at 3:51 PM, Erik Jones ejo...@engineyard.com  
wrote:


On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:

Well, i tried all your sugestions, and i found some funny issues, i  
use the query to count exactly in a day by day basis, and running  
the query with


WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

OR

WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


it includes the 0 hours of day 3:

05-02-2009 12:00:00 AM

No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01  
and 2009-05-02.


Migeul's reply:


sorry,  i edited the date, the correct one is

05-03-2009 12:00:00 AM

If i group by day, it count 1 user for day 2009-05-03 in the output,  
so it adds 1 to the total count of the range


OK, you're going to have to show me an example where that holds:

pagila=# create table test (a date);
CREATE TABLE
Time: 121.029 ms

pagila=# insert into test values ('2009-05-01'), ('2009-04-30  
23:59:59'), ('2009-05-02 13:15:00'), ('2009-05-03 00:00:00');

INSERT 0 4
Time: 1.201 ms

pagila=# select count(*) from test where a = '2009-05-01' and a   
'2009-05-03';

 count
---
 2
(1 row)

Time: 0.690 ms

pagila=# select * from test where a = '2009-05-01' and a   
'2009-05-03';

  a
-
 2009-05-01 00:00:00
 2009-05-02 13:15:00
(2 rows)

Time: 0.386 ms


P.S. Please don't top post mid-conversation, it makes it very  
difficult to reply in a way that is readable with the proper context.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] date ranges in where



On May 6, 2009, at 2:59 PM, Adrian Klaver wrote:


On Wednesday 06 May 2009 2:51:08 pm Erik Jones wrote:

On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:

Well, i tried all your sugestions, and i found some funny issues, i
use the query to count exactly in a day by day basis, and running
the query with

WHERE lastlogin = '2009-05-01' AND lastlogin  '2009-05-03'

OR

WHERE lastlogin = 'X' AND lastlogin = 'Y' + '1 day'::interval


it includes the 0 hours of day 3:

05-02-2009 12:00:00 AM


No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01
and 2009-05-02.


The problem being that midnight is both the end of one day and the  
start of

another.


Not from perspective of the database which has no concept of  
midnight.  My point above was simply that 2009-05-02 12:00:00 AM is  
the start of the 2nd, not the 3rd.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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] PgUS 501c3 Public Charity

2009-05-06 Thread Andrew Gould

On Wed, May 6, 2009 at 4:53 PM, Joshua D. Drake j...@commandprompt.comwrote:

 Hello,

 Yeah its not general technical discussion but this little bit of news
 warrants more widely read attention. PgUS (http://www.postgresql.us/)
 received its 501c3 public charity status today. You can view the
 determination letter here:

 https://www.postgresql.us/determination_letter

 Thanks to everyone who helped!

 Sincerely,

 Joshua D. Drake

 --
 PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


Yay!  That's great news, Joshua!

Andrew

Re: [GENERAL] PGSQL x iptables


Slansky Lukas wrote:

Craig Ringer wrote:

After a long period of inactivity, perhaps?


Is 15 seconds long period? I don't think so.


No. If you see a connection that was working 15 and active seconds ago 
suddenly die, it's not due to time-based state table expiry.


Do you see anything in `dmesg'?

Have you used wireshark to trace activity on the interface and analyzed 
the dumps? You can often learn a lot about what's actually happening 
that way.



To John: I know it's related to iptables but this state seems to be only
on PG connections :-)


Very odd.

--
Craig Ringer

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

Re: [GENERAL] Transaction settings: nowait


durumdara wrote:

If set wait and timeout, the Firebird is waiting for the locked resource 
(record) for X seconds before it show deadlock error.


But when you set no wait, the deadlock error immediately shown by the 
server.


Waiting on a lock doesn't mean there's a deadlock. A deadlock only 
occurs if two transactions are each waiting on resources the other 
transaction holds.


PostgreSQL *DOES* let you control how long it waits before it checks to 
see if transactions waiting on locks might be deadlocked with another 
waiting transaction. See:

http://www.postgresql.org/docs/current/static/runtime-config-locks.html

However, it sounds like what you want is the ability to tell PostgreSQL 
that you don't want your queries to ever wait on a lock at all, even if 
it's just that you're waiting for another transaction to finish work.


If that's what you mean: I'm not aware of any way in PostgreSQL to set 
lock wait timeouts at a transaction-wide level, or to set Pg to report 
an error if it would have to wait for a lock.


Others seem to have wanted it enough to write patches for it, but I 
don't think they got merged. See, for example:

http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php

(Please read the discussion on that thread to get an idea of some of the 
problems with the approach you appear to be requesting - if I'm reading 
your post correctly.).


Anyway, it *IS* possible, as others have noted, to use
SELECT ... FOR { UPDATE | SHARE } NOWAIT
and/or
LOCK TABLE ... NOWAIT
before issuing work. However, this generally means you need to know what 
tuples your queries will touch, including tuples accessed by triggers 
and the like, before you issue your query.


It's also really clumsy, since you often WANT queries to wait on locks 
at least briefly, otherwise you'll get intermittent errors from queries 
that're operating normally just because another query that happened to 
run concurrently briefly locked something the first query wanted.


I must say that I personally can't really see the use of a 
transaction-wide lock wait timeout. Sure, most applications have to be 
prepared to re-issue queries anyway due to transient errors of one sort 
or another, and it's not unreasonable to want to be able to detect the 
difference between a query blocking on a lock, and a query that's just 
taking a long time to do work ... but I don't think that aborting the 
transaction/statement with an error is the right way to do that.


First: In PostgreSQL an error from a transaction results in rollback of 
the whole transaction. This means that if you wanted to re-try after a 
lock wait timeout, you'd need to re-issue the whole transaction, or 
you'd need to use savepoints before each statement to give you 
statement-level retry. Both are clumsy and inefficient. It could also 
easily result in situations where the same group of transactions keep on 
re-trying and fighting over the same locks over and over; you'd waste a 
lot of CPU and I/O repeating work pointlessly, when if you just let the 
transaction wait on the lock everything would go just fine.


In other words, it'd be ugly. I'm also not really sure how much 
difference it makes in practice WHY a statement is taking a long time, 
only that it is. Who cares whether you're waiting on a lock held by 
another transaction, or whether another transaction is using so much 
memory and disk I/O bandwidth that your query is taking forever?


If you really did care about lock waits specifically, it might almost be 
nicer to be able to have the server send asynchronous NOTICE-level 
messages informing the application that the query is blocked on a lock - 
eg NOTICE: lock_wait_timeout exceeded waiting for SHARE lock on oid 
44123. That way the client app would know what was happening, but the 
query wouldn't get interrupted unless the app intentionally issued a 
query cancel request.


Of course, that requires more sophisticated app programming and database 
interface driver use than just issuing an error on lock wait (you have 
to process messages, and you have to be prepared to do a query cancel 
request via another connection, probably from another thread), but it's 
a lot cleaner and nicer.


Personally in these situations I just make sure my apps are 
multi-threaded with all database work done in a worker thread and 
controlled by posting events back and forth. That way my UI can keep the 
user informed and keep on updating while the query runs. The UI can also 
make decisions about what to do with excessively long-running queries. 
In the app I'm presently working on, for example, I pop up an infinite 
progress indicator (busy-bar) after a short delay, and I permit the user 
to cancel a long-running transaction if they don't want to keep waiting 
(after all, I have to be able to handle transaction aborts for other 
reasons anyway, so why not?).


In theory, if I was worried about excessive lock wait times, I could 
even use the

Re: [GENERAL] date ranges in where